Getting to Know You(r SQL Server) — Part 1

At last count I manage 124 SQL Servers worldwide and it seems that no two of them are configured alike. SQL version and edition, default folders, number of cores, RAM — there’s a lot to keep track of. To make things easier, I’ve gathered a number of useful queries and extended stored procedure calls into one routine that returns a single result set containing the information I find most useful. A good place to start is the SERVERPROPERTY function. This built-in scalar valued function returns any of almost three dozen properties describing your server. Introductions are always a great place to start, so let’s ask the server its name:

Wait, what? Three ways to get the server’s name? Well, yes. If you’re connected to the default instance of a standalone server, these will all return the same value. If your server is clustered then MachineName will be the network name of the cluster and ComputerNamePhysicalNetBIOS will be the name of the node SQL Server is running on. ServerName is always the network name of the SQL Server itself. For a default instance, that’s the same as MachineName; a named instance will return something like MachineName\InstanceName. Now that the introductions are complete, let’s dig a little deeper. Let’s find out what version of SQL Server we’re connected to and whether it’s Standard, Enterprise, Developer, etc. We’ll also ask if the instance is clustered, in case something looks odd with those three names we retrieved earlier:

IsClustered is a bit value: 1 for yes and 0 for no. ProductVersion returns the full build number, including the patch level, such as 11.0.3373.0 for SQL Server 2012 SP1 CU5. And Edition gives you the spelled-out product edition including whether it’s 64-bit. How, you may ask, do you know that “11.0.3373.0” means “SQL Server 2012 SP1 CU5”? Why, by visiting the Microsoft SQL Server Version List, of course! Not only does the site list every build since SQL Server 7.0, it includes links to the relevant KB article where you can download the patch. It’s my go-to choice when I want to see if a server is current and bring it up to date if it’s not. A couple of things I’d like to know that aren’t available from SERVERPROPERTY are the total RAM and number of processors on the machine. For that we need to turn to the xp_msver extended stored procedure. It can return one item or every item, but it always returns a four-column result set. I want to fit it into two columns, so we’ll pull a little sleight of hand:

The magic numbers 16 and 19 are simply the values of [Index] where ProcessorCount and PhysicalMemory are found. I could have just as easily searched [Name] for those string values but where’s the fun in that? You may want to know if the server has recently been restarted. Sure, you could click through SSMS all the way down to the current SQL Server log and check the time of the first entry, but there’s a less gooey way. The sys.dm_os_sys_info dynamic management view returns a single row with upwards of two dozen columns of fascinating system data. We’ll contain ourselves to just two for now:

The sqlserver_start_time column is a nice DATETIME value but ms_ticks isn’t so straightforward—it counts milliseconds (!?) since the OS was booted, so we have to do some gymnastics to turn it into something useful. We haven’t even gotten to the good stuff yet. Next time we’ll go over extracting the default locations for MDFs, LDFs, error log, agent log, and backups from the registry.

Leave a Reply

Your email address will not be published.