Toolbox - Just the Basics

, 2018-03-29

There are many query sets out there to collect varying groups of configuration data - the ones I most frequently use come from Glenn Berry of SQLskills (blog/@GlennAlanBerry) but I have found that there is more data than his basic set that I like to pull, and I wanted a single query that I could run across multiple versions of SQL Server.  Glenn's basic setup is version-specific - one script for 2005, a different for 2008, etc. - and I wanted to be able to just copy-paste my script in and press Execute.

I ended up starting with some base DMV info and ran it trial-and-error against different versions until I found the fields and formats that are different.  The biggest branch is between SQL 2005-2008R2 and 2012+; there were lots of changes to the dynamic management views in SQL 2012 - so many that I ended up creating two separate branches of code with a version check at the top to determine which branch the execution goes down.

There are multiple ways to determine the SQL Server version, but the one I found that I liked the best in this case is the SERVERPROPERTY('ProductVersion'):

SET @SQLVersion = LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')),4)PRINT @SQLVersion

This returned a nice four character value that was easy to use in the code (it's converted to a char so that I can use the LEFT function to trim it):

9.00
10.0
10.5
11.0
12.0
13.0

--

The biggest change between the versions is a new DMV added in SQL 2008R2 SP1, sys.dm_os_windows_info.  I very much like having a DMV with some basic Windows information:

SELECT *
FROM sys.dm_os_windows_info 

windows_release windows_service_pack_level windows_sku os_language_version
6.3 7 1033


...but what I don't like is that it was added in SP1 - SQL 2008R2 RTM doesn't have this view.  What that meant to my code was that I reference this view in the 2012+ branch but not in the 2005-2008R2 branch since I would have to get more specific (to the SP level) to determine whether I could use it in 2008R2.

http://s2.quickmeme.com/img/cc/cc63896fead20f99f3381a5c389587c48263f258d755be752624b60b62769bab.jpg

--

To return RAM info, I ended up using a temp table (not my favorite but it worked here) - there is a deferred name resolution issue with returning the data due to changes in SQL 2012+.  Even with the IF...ELSE code branch I was using to split my code, the changes in this particular query broke without using an up-front temp table to hold the RAM info (which I then added into the data return query).

--

So...the query:

/*
Physical RAM data comes from the sys.dm_os_sys_info DMV 

The catch is that in SQL 2012 the field changed from being measured in bytes to KB and the field name changed. 

I am collecting the data in a Temp Table because it was the only way I could find to handle deferred name resolution - by having the
DMV inline in a regular query the name resolution errors out even inside an IF ELSE statement.

The temp table is then selected from in the actual data return query below.

IF...ELSE...EXEC Code slightly modified from:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/442ce31a-9f08-4fa2-90ce-aaba23e05fac/gathering-physical-memory-for-sql-2005sql-2014?forum=transactsql
*/

IF OBJECT_ID('tempdb..#RAM') IS NOT NULL
DROP TABLE #RAM

CREATE TABLE #RAM
(
InstanceName SYSNAME,
Physical_Mem_MB INT
)

IF LEFT(CAST(SERVERPROPERTY('ResourceVersion') as VARCHAR(20)),2) in ('11','12','13','14')
EXEC ('INSERT INTO #RAM SELECT @@SERVERNAME, (physical_memory_KB/1024) as Physical_Mem_MB FROM sys.dm_os_sys_info')
ELSE
EXEC ('INSERT INTO #RAM SELECT @@SERVERNAME, (physical_memory_in_bytes/1024/1024) as Physical_Mem_MB FROM sys.dm_os_sys_info')

--

/*
The actual data return query for the Instance settings
There is a major branch for SQL 2005-2008R2 and a separate branch for 2012+, again because of differing field names and new DMVs between versions
Much of the data is selected as inline queries to prevent the need to tack on the Instance Name and then create a huge multi-way join
*/

DECLARE @SQLVersion as NVARCHAR(4)

SET @SQLVersion = LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')),4)

/*
PRINT @SQLVersion
*/

IF @SQLVersion in ('9.00', '10.0', '10.5')
SELECT SERVERPROPERTY('ServerName') as [InstanceName]
, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [ComputerNamePhysicalNetBIOS]
, SERVERPROPERTY('ProductVersion') as [SQLProductVersion]
, @SQLVersion as [SQLMajorVersion]
, CASE @SQLVersion
WHEN '9.00' THEN 'SQL Server 2005'
WHEN '10.0' THEN 'SQL Server 2008'
WHEN '10.5' THEN 'SQL Server 2008 R2'
END as [SQLVersionBuild]
, SERVERPROPERTY('ProductLevel') as [SQLServicePack]
, SERVERPROPERTY('Edition') as [SQLEdition]
, (
SELECT RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 4)
) as [WindowsVersionNumber]
, (
SELECT CASE LTRIM(RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 4))
/* LTRIM needed to trim leading space in three character version numbers such as 5.0 */
/* No DMV for this in SQL 2005, 2008, or 2008R2 RTM */
WHEN '5.0' THEN 'Windows 2000'
WHEN '5.1' THEN 'Windows XP'
WHEN '5.2' THEN 'Windows Server 2003/2003 R2'
WHEN '6.0' THEN 'Windows Server 2008/Windows Vista'
WHEN '6.1' THEN 'Windows Server 2008 R2/Windows 7'
WHEN '6.2' THEN 'Windows Server 2012/Windows 8'
WHEN '6.3' THEN 'Windows Server 2012 R2'
WHEN '10.0' THEN 'Windows Server 2016'
ELSE 'Windows vNext'
END
) as [WindowsVersionBuild]
, (
SELECT [Physical_Mem_MB]
FROM #RAM
) as [PhysicalMemMB]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%min server memory%'
) as [MinServerMemoryMB]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%max server memory%'
) as [MaxServerMemoryMB]
, (
SELECT [cpu_count]
FROM sys.dm_os_sys_info
) as [LogicalCPUCount]
, (
SELECT [hyperthread_ratio]
FROM sys.dm_os_sys_info
) as [HyperthreadRatio]
, (
SELECT [cpu_count]/[hyperthread_ratio]
FROM sys.dm_os_sys_info
) as [PhysicalCPUCount]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%max degree of parallelism%'
) as [MAXDOP]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%cost threshold for parallelism%'
) as [CTOP]

ELSE /* SQL 2012+ */

SELECT SERVERPROPERTY('ServerName') as [InstanceName]
, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [ComputerNamePhysicalNetBIOS]
, SERVERPROPERTY('ProductVersion') as [SQLProductVersion]
, @SQLVersion as [SQLMajorVersion]
, CASE @SQLVersion
WHEN '11.0' THEN 'SQL Server 2012'
WHEN '12.0' THEN 'SQL Server 2014'
WHEN '13.0' THEN 'SQL Server 2016'
ELSE 'SQL Server vNext'
END as [SQLVersionBuild]
, SERVERPROPERTY('ProductLevel') as [SQLServicePack]
, SERVERPROPERTY('Edition') as [SQLEdition]
, (
SELECT [windows_release]
FROM sys.dm_os_windows_info
) as [WindowsVersionNumber]
, (
SELECT CASE windows_release
WHEN '5.0' THEN 'Windows 2000'
WHEN '5.1' THEN 'Windows XP'
WHEN '5.2' THEN 'Windows Server 2003/2003 R2'
WHEN '6.0' THEN 'Windows Server 2008/Windows Vista'
WHEN '6.1' THEN 'Windows Server 2008 R2/Windows 7'
WHEN '6.2' THEN 'Windows Server 2012/Windows 8'
WHEN '6.3' THEN 'Windows Server 2012 R2'
WHEN '10.0' THEN 'Windows Server 2016'
ELSE 'Windows vNext'
END
FROM sys.dm_os_windows_info /* New DMV in SQL 2008 R2 SP1*/
) as [WindowsVersionBuild]
, (
SELECT [Physical_Mem_MB]
FROM #RAM
) as [PhysicalMemMB]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%min server memory%'
) as [MinServerMemoryMB]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%max server memory%'
) as [MaxServerMemoryMB]
, (
SELECT [cpu_count]
FROM sys.dm_os_sys_info
) as [LogicalCPUCount]
, (
SELECT [hyperthread_ratio]
FROM sys.dm_os_sys_info
) as [HyperthreadRatio]
, (
SELECT [cpu_count]/[hyperthread_ratio]
FROM sys.dm_os_sys_info
) as [PhysicalCPUCount]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%max degree of parallelism%'
) as [MAXDOP]
, (
SELECT [value_in_use]
FROM sys.configurations
WHERE name LIKE '%cost threshold for parallelism%'
) as [CTOP]

/*
Clean-Up
*/

IF OBJECT_ID('tempdb..#RAM') IS NOT NULL
DROP TABLE [#RAM]

--

The resultset looks like this:

InstanceName ComputerName
PhysicalNetBIOS
SQLProductVersion SQLMajorVersion
Instance1\I1 Server55 12.0.5000.0 12
SQLVersionBuild SQLServicePack SQLEdition
SQL Server 2014 SP2 Standard Edition (64-bit)

Windows
Version
Number
Windows
Version
Build
Physical
MemMB
MinServer
MemoryMB
MaxServer
MemoryMB
6.3 Windows Server 2012 R2 81918 64 66560

LogicalCPUCount HyperthreadRatio PhysicalCPUCount MAXDOP CTOP
12 1 12 1 5


--

I like this a lot as it is a quick way to pull basic SQL, Windows, RAM, and CPU from any SQL 2005+, which is something I need all the time when I touch a new server.

Hope this helps!



Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads