You’ve Probably Had This Request Before…
So yesterday afternoon I received a management request to provide some statistics on our SQL Servers, probably to justify our positions for the next budget year. No problem, I thought, because I know how many total servers, how many clusters, how many physical boxes and how many are virtual. I also know which ones are production, acceptance, and development and I know how much RAM, how many processors and cores, how many drives and how big they all are. I also know how many licenses we own and which ones are Enterprise versus Standard, which ones are 2008R2 and which ones are 2005.
I also have a script (thanks to Pinal Dave) to tell me pretty much everything I need to know when I run it on each of my servers. However, that was not the information they requested. Wait for it, wait for it.
They wanted to know how many records or rows we maintained on how many tables. In all of my years working with SQL Server, Progress RDBMS, MS Access, and MySQL (in order of awesomeness), I do not think I have ever had anyone ask for that level of information. The quick stat that everyone wants to know is usually how many servers and how many terabytes. In our environment, they also want to know how many dot net applications are we supporting on those database servers.
Much to my dismay, my frantic google session showed many people who had a script that was essentially similar to Pinal’s script (although most were not nearly as fabulous). So I did what every DBA should do, I sent up a distress call to the #SQLHelp Twitter tag! Lo and behold who should come to my rescue, why SirSQL ((blog|twitter), who else would you expect? Although, I was not a damsel in distress so maybe that was a poor analogy, but hey it made me giggle, so why not?
Here is his solution, which is really quite elegant. I am thinking of automating it on one of my servers so that I can have this handy in case they ask for the information again. It is inevitable that they will ask for it again when you give it to them once, unless of course I spent hours writing the script myself. Thanks again, SirSQL!