Automated SQL Inventory Pt. 1 – Instance Information

Posted on by RJ

This will be the first part of a long series defining my custom SQL Server inventory system.  I started my inventory as an exercise to learn my new environment after starting a new job, but it has grown to be very useful for reporting, using as a source for PowerShell to target all my instances, and for basic monitoring.  There are many components to it, so I figured breaking them down piece by piece would be easier to digest.  In this part, I will go through the script I use to pull information about the instance.

The requirements for my environment made it necessary for my scripts to support SQL Server 2000 and up, so I had to make things dynamic so that the right code for the version level would be executed.  I did this mainly by setting large VARCHAR variables to hold my T-SQL, checking the version of the instance I am connected to, then executing only the T-SQL that applies to that version.

For the instance, this is the information I decided to pull initially:

  • Server name
  • Instance name
  • SQL version
  • SQL major build (RTM, service pack)
  • SQL build number
  • SQL edition (Standard, Enterprise, Express, etc)
  • SQL authentication (Windows, mixed mode)
  • Max degrees of parallelism
  • Cost threshold for parallelism
  • Number of cores
  • Memory allocated for SQL
  • Total memory on the server
  • Latest SQL startup time
  • Time stamp (useful for making sure the information is up to date)

This list will probably be expanded, but for my environment this data is what I am most interested in.

The first handful (server name, instance name, version, major build, build number, edition, and authentication) can be pulled using the built-in SERVERPROPERTY() metadata function.  This works on all the versions I need to be able to check, so I don’t have to split my code up.

SELECT 
	CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY('MachineName')))    AS [ServerName],
	CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY('InstanceName')))   AS [InstanceName],
	CONVERT(VARCHAR(50),(SELECT 
		CASE (SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 4))
			WHEN '13.0' THEN 'SQL Server 2016'
			WHEN '12.0' THEN 'SQL Server 2014'
			WHEN '11.0' THEN 'SQL Server 2012'
			WHEN '10.5' THEN 'SQL Server 2008 R2'
			WHEN '10.0' THEN 'SQL Server 2008'
			WHEN '9.00' THEN 'SQL Server 2005'
			WHEN '8.00' THEN 'SQL Server 2000'
			ELSE 'Unknown Version'
		END
		)
	)                                                                AS [Version],
	CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY('ProductLevel')))   AS [Build],
	CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY('ProductVersion'))) AS [BuildNumber],
	CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY('Edition')))        AS [Edition],
	CONVERT(VARCHAR(50),(SELECT 
		CASE (SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')) 
			WHEN 1 THEN 'Windows' 
			WHEN 0 THEN 'Mixed Mode' 
		END
		)
	)                                                                AS [Authentication],
	GETDATE()                                                        AS [Timestamp]

This is pretty straight-forward.  The only ones that require more than a simple CONVERT() are the version name and the authentication mode.  For version name, I needed to compare the first few digits of the build number to a bunch of CASEs to get the version name.  There may be another way to get this, but this method is easy enough.  For the authentication mode, I just converted from a bit to something more readable.  This is also where I create the time stamp using GETDATE()

Moving on, the next hurdle was getting the number of cores and the total amount of RAM for the system.  I may move this to WMI using PowerShell, but initially I wanted to be able to collect all the information using T-SQL.  For SQL Server 2000, this required a little more work:

IF OBJECT_ID('tempdb..##OSstats') IS NOT NULL
	DROP TABLE ##OSstats
CREATE TABLE ##OSstats ([Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000)) 
INSERT INTO  ##OSstats EXEC xp_msver
SELECT [Internal_Value] AS [Cores]         FROM ##OSstats WHERE [name] = 'ProcessorCount'
SELECT [Internal_Value] AS [TotalMemoryMB] FROM ##OSstats WHERE [name] = 'PhysicalMemory'

I needed to use the xp_msver extended stored procedure to pull this, as there is no dm_os_sys_info DMV in SQL 2000.  I inserted the results of that stored procedure into a temp table, then pull the values I am interested in.

For SQL Server 2005, I use this approach:

SELECT [cpu_count]                          AS [Cores]         FROM [master].[sys].[dm_os_sys_info]				
SELECT [physical_memory_in_bytes]/1024/1024 AS [TotalMemoryMB] FROM [master].[sys].[dm_os_sys_info]

I was able to pull both the memory and number of cores from the dm_os_sys_info DMV.

For SQL Server 2008+, I use this:

SELECT [cpu_count]                     AS [Cores]         FROM [master].[sys].[dm_os_sys_info]
SELECT [total_physical_memory_kb]/1024 AS [TotalMemoryMB] FROM [master].[sys].[dm_os_sys_memory]

For these versions, I still am using the dm_os_sys_info DMV for the number of cores, but I’m pulling the memory information from the newer dm_os_sys_memory DMV.

Finally, I pull some of the configurations (max memory, max degrees of parallelism, and cost threshold for parallelism).  The method for pulling these is the same for SQL Server 2000 and SQL Server 2005, but differs for newer versions.  The difference is the older versions store their configuration values in  [master].[dbo].[sysconfigures], whereas newer versions use [master].[sys].[configurations].  Here is the old version:

SELECT [value] AS [MemoryAllocatedMB] FROM [master].[dbo].[sysconfigures] WHERE [config] = '1544'
SELECT [value] AS [MaxDOP]            FROM [master].[dbo].[sysconfigures] WHERE [config] = '1539'
SELECT [value] AS [CTFP]              FROM [master].[dbo].[sysconfigures] WHERE [config] = '1538'

Here is the new version:

SELECT [value] AS [MemoryAllocatedMB] FROM [master].[sys].[configurations] WHERE [configuration_id] = '1544'
SELECT [value] AS [MaxDOP]            FROM [master].[sys].[configurations] WHERE [configuration_id] = '1539'
SELECT [value] AS [CTFP]              FROM [master].[sys].[configurations] WHERE [configuration_id] = '1538'

It should be noted that in some cases the older version will work with newer versions of SQL Server, but since it is only there for backwards compatibility I choose to use the newer methods where possible.

That only leaves the logic as to when to run which queries, as well as the glue to return all the values in one results set.  The query in its entirety can be found here: GitHub

Stay tuned for part 2 – SQL databases

Leave a Reply

Your email address will not be published. Required fields are marked *