Ian Kirk - SQL DBA

As I had mentioned earlier, we needed to make sure we understood the internal details of all our SQL servers, so to accomplish that, I set out to collect the data via WMI calls wrapped up in a little C# app.  In an effort to spare someone a bit of time, I’ll outline the particular classes and objects I’m referencing.

From the Win32_ComputerSystem class, I pull:

• Server Manufacturer = “Manufacturer”
• Server Model = “Model”
• Server Name = “Name”
• Number Of Processors = “NumberOfProcessors”
• System Type = “SystemType”
• Physical Memory = “TotalPhysicalMemory”

So to flesh these out a bit - Manufacturer, Model and Name are pretty self explanatory.  With these you can sort out that you’ve got a HP ProLiant DL585 G2 named SANDBOX01.  Now, though, if you’ve got some odd problem with one DL585’s power supply, you can quickly sort out which other boxes you need to look into in case they’ve got a similar problem.

Number of Processors, in Windows 2000 and 2003, doesn’t give you any opportunity to sort out things like HyperThreading or mutli-cores - you get what you can see through task manager.  Does this mean Windows 2008 does give you this information?  I think so, through a second object, but I don’t have any Windows 2008 to test with!

System Type is going to tell you you’ve got an X86 or x64 system.

Physical Memory will be the actual amount of physical memory you’ve got in the server, reported in bytes.  Of course, divide twice by 1024 and you’ve got gigabytes.  I had a bit of fun getting to this particular class / object - I tried using the Win32_OperatingSystem class, but its memory objects weren’t giving me the try amount of memory, leading me to chase a few geese that didn’t exist.

To round things out, I make a call to the Win32_Processor class and pull a few more objects:

• Processor Manufacturer = “Manufacturer”
• Processor Name = “Name”
• Processor Speed = “MaxClockSpeed”

The Manufacturer and Name objects return slightly redundant information.  This is, you’re quite likely going to realize a Xeon processor comes from Intel or that an AMD Opteron is, well, from AMD! Processor Speed will report back for each processor in megahertz, but since each processor is the same, I don’t think its a big deal.

All of this data gets pumped into a table in our metadata repository for us to toy with and report on.  This week I have flipped it around a few different ways and sent it on to the boss for his magic - making the standards get met.

Maybe this is the kind of information somebody else in the shop should be or could be collecting, but we’re the kind of crew that doesn’t prefer to sit and wait for that to come along.  The business is growing at a fast pace and documentation standards have been slow to keep up, so while lots of this information could have been captured on the front end, it wasn’t.  Now that we’ve got questions headed our direction, we figured it was a good time to find those answers.

I wouldn’t have been able to do the majority of this work without having a fine tool like the WMI Code Creator which will allow you to dig through all the WMI classes and objects, as well as create VB.Net, VB Script and C# code to collect the data for you.  This tool, combined with the MSDN Library I was able to get some good information to start testing with.

Oh - and I can’t forget thanking my coworker (who shall remain nameless) for all the C# development help!  I’ve had plenty of annoying questions, and they’ve handled them all with a smile.  If anyone wants to see the C# code, let me know and I can wrap it up for you… after I’ve made it prettier.