SQL Server Configuration Settings Query

By: Alejandro Cobar | Updated: 2019-07-10 | Comments (5) | Related: > Monitoring

Problem

Knowing the configuration of your SQL Server instance is key to determine if it is patched, how much memory has been allocated for SQL Server, parallelism settings and more. For each of these settings you need to run either different queries or look in different places in SQL Server Management Studio SSMS, so I put together a script that gathers important settings for SQL Server all in one place.

Solution

In this tip I'm going to present a query that provides several important values about SQL Server that can give you a rough overview of the server configuration. It might seem trivial, but I will try my best to explain why I think each returned value has a high importance.

T-SQL Script to Return SQL Server Configuration Information

CREATE TABLE #CPUValues(
[index]        SMALLINT,
[description]  VARCHAR(128),
[server_cores] SMALLINT,
[value]        VARCHAR(5) 
)
 
CREATE TABLE #MemoryValues(
[index]         SMALLINT,
[description]   VARCHAR(128),
[server_memory] DECIMAL(10,2),
[value]         VARCHAR(64) 
)
 
INSERT INTO #CPUValues
EXEC xp_msver 'ProcessorCount'
 
INSERT INTO #MemoryValues 
EXEC xp_msver 'PhysicalMemory'
 
SELECT 
   SERVERPROPERTY('SERVERNAME') AS 'instance',
   v.sql_version,
   (SELECT SUBSTRING(CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')),0,CHARINDEX('Edition',CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')))) + 'Edition') AS sql_edition,
   SERVERPROPERTY('ProductLevel') AS 'service_pack_level',
   SERVERPROPERTY('ProductVersion') AS 'build_number',
   (SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID) AS [port],
   (SELECT [value] FROM sys.configurations WHERE name like '%min server memory%') AS min_server_memory,
   (SELECT [value] FROM sys.configurations WHERE name like '%max server memory%') AS max_server_memory,
   (SELECT ROUND(CONVERT(DECIMAL(10,2),server_memory/1024.0),1) FROM #MemoryValues) AS server_memory,
   server_cores, 
   (SELECT COUNT(*) AS 'sql_cores' FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS sql_cores,
   (SELECT [value] FROM sys.configurations WHERE name like '%degree of parallelism%') AS max_dop,
   (SELECT [value] FROM sys.configurations WHERE name like '%cost threshold for parallelism%') AS cost_threshold_for_parallelism 
FROM #CPUValues
LEFT JOIN (
      SELECT
      CASE 
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '8%'    THEN 'SQL Server 2000'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '9%'    THEN 'SQL Server 2005'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.0%' THEN 'SQL Server 2008'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.5%' THEN 'SQL Server 2008 R2'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '11%'   THEN 'SQL Server 2012'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '12%'   THEN 'SQL Server 2014'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '13%'   THEN 'SQL Server 2016'     
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '14%'   THEN 'SQL Server 2017'
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '15%'   THEN 'SQL Server 2019' 
         ELSE 'UNKNOWN'
      END AS sql_version
     ) AS v ON 1 = 1
 
DROP TABLE #CPUValues
DROP TABLE #MemoryValues

After you execute the code, you will end up with a result set that looks like the following. Below are two examples.

Now I’m going to explain the purpose for each field (even if some of them are quite obvious) and why I posted the result for these 2 instances.

Field	Description
instance	The name of the instance targeted (an obvious one).
sql_version	The SQL Server version currently installed. As obvious as it is, it helps you to know if you are dealing with a SQL Server version that is out of support, will be out of support in the very near future (as it is the case right now for SQL Server 2008 and SQL Server 2008 R2), or simply if you’re covered for the years to come. *Perhaps your management isn’t even aware of this and you can come to the rescue.
sql_edition	Again, an obvious one. However, what if you are required to implement a DR solution for a set of servers under your support? Imagine that after performing the respective analysis, you conclude that the easiest way is to implement Database Mirroring. However, after implementing the solution you are told that the applications are suddenly slow… and it’s because you set mirroring under the high-safety mode (which introduces latency on systems with heavy workloads), using SQL Server Standard Edition (which only supports high-safety mode). Therefore, you are forced to break your solution and find another one, requiring extra effort from your end which could’ve been avoided by considering this field in your analysis.
service_pack_level	This one lets you know if the instance has simply been patched or not. If the value returned is RTM, then you can be sure that it has never been patched and you probably should do something about it. *This applies for SQL Server versions up to 2016. Starting from SQL Server 2017, Microsoft does not release Service Packs anymore, but Cumulative Updates instead. This is why you might have your SQL Server 2017 instance patched to the latest available release, but it will still say RTM.
build_number	This one lets you know how far behind, or how up-to-date, your SQL Server instance is in terms of patches (Service Pack, Cumulative Updates or hotfixes) released by Microsoft. You can also guide yourself using this resource because it contains a lot of detailed information on each build number released by Microsoft (even with links to download the patch directly from Microsoft itself).
port	Another obvious one. This just simply shows you the port number being used by the SQL Server instance.
min_server_memory	This instance parameter controls the minimum amount of physical memory that SQL Server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory set in the min server memory parameter but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the min server memory setting, SQL Server won’t release any memory below that amount, so you have to be very careful if this value has been changed or if you need to modify it for a particular reason.
max_server_memory	This instance parameter controls the maximum amount of physical memory that the SQL Server service will be allowed to use. So as with the min_server_memory, you must be very careful if this value has been changed or if you need to modify it for a particular reason. Make sure that the decision to modify this value isn't taken lightly.
server_memory	An obvious one. This value simply tells you the amount of usable* physical memory in the server, which in conjunction with the min/max server memory parameters, can tell if you are in trouble or not. *By usable, I’m referring to the real total amount of physical memory that can be used. Remember that the OS always reserves a certain amount of memory that cannot be used at all; for instance, my computer has 8GB of memory and only 7.90GB are usable.
server_cores	This value tells you the amount of CPU cores available in the server.
sql_cores	This is an interesting and very helpful. This value tells you the amount of CPU cores that the SQL Server instance is using. Why is this such a big deal? As you probably already know, there are several differences among the different editions of SQL Server; one of them is the amount of CPU cores that the SQL Server instance can and will use. Imagine that the newly inherited (and dedicated) SQL Server instances, under your support, are all SQL Server Standard Edition running on servers with 32 CPU cores. This is a perfect example of a potential poor choice, made by someone without the respective knowledge, because SQL Server Standard Edition supports up to 4 processor sockets or 24 CPU cores (whichever is lower); therefore, in this case you would be wasting CPU cores. But you can save the day by identifying this and informing your management. *In my example, TestInstance2 is a SQL Server Express instance that is only using 1 CPU core out of the 8 that my computer has, which serves to prove the point I just mentioned.
max_dop	I’ll leave you with an article from MSSQLTips, written by Matteo Lorini, that covers the MAXDOP setting with more detail that I can possibly provide in this section. However, it is important to remark that this value can tell you if you are dealing with a SQL Server instance that supports a 3^rd party application that doesn’t work well with parallelism and that requires the parameter is set to 1 at all times.
cost_threshold_for_parallelism	Here’s an article from Brent Ozar that explains the nature of this value, and why you probably shouldn’t leave the default value (5) that comes out of the box for your SQL Server instance. Here’s also a very good article from a good buddy of mine, Pablo Echeverria, that covers parallelism in SQL Server. Overall, a good starting point for this value is 50, and then you should tune up/down depending on your specific case.

Next Steps

This T-SQL code is even more useful if it is executed against all the SQL Server instances under your support, so you can have a general overview of these parameters/values across all the infrastructure under your care.
You can build your own mechanism to automatically traverse all the SQL Server instances, obtain the results from the T-SQL code, and store them in a central management database for your analysis. You can even go further and automate this solution through a job to keep a record in history and be able to determine if any value changed for any instance.
You can grab the T-SQL code I presented and add/remove values that fit your daily use case, I simply presented the ones that make a lot of sense to me.

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-07-10

Comments For This Article

Tuesday, August 6, 2019 - 12:18:14 PM - Greg Robidoux

Hi Kenney, that was my mistake. Thanks for reposting the comment.

-Greg

Tuesday, August 6, 2019 - 11:22:10 AM - Kenney Hill

I apologize as my previous comment does not have the first paragraph that it was supposed to. It has the paragraph from a post to a different article.

It was supposed to mention that my version doesn't use XPs, which can be a issue in some environments. Also, this script may not be as backwards compatible as it used to be due to additions I've made, like including information on the services, which probably limits parts of this script to 2014 and later. If someone wants to post if parts of this doesn't work on earlier versions that would be great.

Tuesday, August 6, 2019 - 9:53:07 AM - Kenney Hill

An interesting idea. I will likely add some of your properties to the script that I cobbled together years back and occasionally add new bits and pieces to. My version puts everything in one row so that I can run it against multiple servers at once, I use Registered Servers in SSMS, so that I can see all of their info in a spreadsheet like format. Here is my script. I apologize for it's haphazardness, remember it is cobbled together, and it's been cutomized to what I want to see, like in the case of # of DBs. In theory this should work back to SQL 7 but I have not tested that, I am confident that it works back to at least 2005 though.

----------------------------------------------------------------
set nocount off;
 
DECLARE @ver nvarchar(128);
 
declare @edition varchar(128);
declare @prodlvl varchar(128);
 
 
set @ver = cast(serverproperty('ProductVersion') AS nvarchar(128));
set @edition = cast(SERVERPROPERTY('edition') as varchar(128));
set @prodlvl = cast(SERVERPROPERTY('productlevel') as varchar(128));
 
declare @subver nvarchar(128);
 
set @subver = SUBSTRING(@ver, CHARINDEX('.', @ver) + 1, 1);
set @ver = left(@ver, CHARINDEX('.', @ver) - 1);
 
declare @rgedition INTEGER;
declare @rgversion VARCHAR(16);
declare @rgserialnumber VARCHAR(30);
 
 
if exists (select top 1 1 from master.dbo.sysobjects where xtype = 'X' and name = 'sqbutility')
exec master..sqbutility 21, @rgedition OUTPUT, @rgversion OUTPUT, @rgserialnumber OUTPUT;
 
declare @physicalmemory DECIMAL(7,1);
declare @sqltext NVARCHAR(4000);
 
if cast(@ver as int) >= 11 --for SQL 2012 or higher?
set @sqltext = 'select @pm = cast(round(physical_memory_kb / 1024 / 1024.0 , 1)as decimal(7, 1)) from sys.dm_os_sys_info';
if cast(@ver as int) < 11 --for SQL before 2012
set @sqltext = 'select @pm = cast(round(physical_memory_in_bytes / 1024 / 1024 / 1024.0 , 1)as decimal(7, 1)) from sys.dm_os_sys_info';
 
exec sp_executesql @sqltext, N'@pm DECIMAL(7,1) OUTPUT', @pm = @physicalmemory OUTPUT;
 
declare @bulocation varchar(256);
 
--if object_id('master.dbo.SQLBackupOptions') is not null
--begin
-- select top 1
-- @bulocation = REPLACE( case when RIGHT(BackupPath, 1) = '\' then LEFT(BackupPath, LEN(BackupPath) - 1) else BackupPath end,
-- '\' + CAST(SERVERPROPERTY('servername') as varchar(128)), '')
-- from
-- [master].[dbo].[SQLBackupOptions];
--end
 
DECLARE
@MaxDoP Varchar(2),
@CostThresholdForParallelism Varchar(20);
 
SELECT
@MaxDoP = CAST(value_in_use AS Varchar(2))
FROM
sys.configurations
WHERE
name = 'max degree of parallelism';
 
SELECT
@CostThresholdForParallelism = CAST(value_in_use AS Varchar(20))
FROM
sys.configurations
WHERE
name = 'cost threshold for parallelism';
 
WITH
ServiceInfo AS
(
SELECT
@@SERVERNAME AS ServerName,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.is_clustered END) IsClustered,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.cluster_nodename END) ClusterNodeName,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.service_account END) SQLServerServiceAccount,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.startup_type_desc END) SQLServerStartupType,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.status_desc END) SQLServerStatus,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server Agent' THEN ServerServices.service_account END) SQLAgentServiceAccount,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server Agent' THEN ServerServices.startup_type_desc END) SQLAgentStartupType,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server Agent' THEN ServerServices.status_desc END) SQLAgentStatus,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Full-text Filter Daemon Launcher' THEN ServerServices.service_account END) SQLFullTextServiceAccount,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Full-text Filter Daemon Launcher' THEN ServerServices.startup_type_desc END) SQLFullTextStartupType,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Full-text Filter Daemon Launcher' THEN ServerServices.status_desc END) SQLFullTextStatus--,
FROM
sys.dm_server_services ServerServices
)
 
SELECT
SERVERPROPERTY('machinename') as "Server",
ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') as "Instance", --select SERVERPROPERTY('ServerName')
(select local_net_address from sys.dm_exec_connections where session_id = @@SPID) as "IP",
(select local_tcp_port from sys.dm_exec_connections where session_id = @@SPID) as "Port",
cast(serverproperty('ProductVersion') AS nvarchar(128)) as "Version Number",
CASE @ver
WHEN '7'THEN '7'
WHEN '8'THEN '2000'
WHEN '9'THEN '2005'
WHEN '10'THEN '2008'
WHEN '11'THEN '2012'
WHEN '12'THEN '2014'
WHEN '13'THEN '2016'
WHEN '14'THEN '2017'
ELSE CAST(((CAST(@ver AS INT) - 14) * 2 + 2017) AS NVARCHAR(128)) + '?' --'UNKNOWN' - this formula assumes a new release every 2 years
END +
CASE @subver
WHEN '5'THEN 'R2'
ELSE ''
END as "Version",
left(@edition, charindex(' ', @edition) - 1) as "Edition",
REPLACE(REPLACE(@prodlvl, 'SP', ''), 'RTM', '0') as "SP", --@prodlvl AS SP,
--case when right(@edition, 8) = '(64-bit)' then 64 else 32 end as "Bits",
(select count(*) from sys.sysdatabases where dbid > 4 /*and name not in ('SQLPERF', 'SQLCARE')*/) as "# of DBs",
(select cpu_count /*/ hyperthread_ratio*/ from sys.dm_os_sys_info) as "CPUs",
--(select hyperthread_ratio from sys.dm_os_sys_info) as "Hyperthread Ratio",
@physicalmemory as "Mem(GB)",
(SELECT cast(round(cast(value as int) / 1024.0, 1) as decimal(12, 1))
FROM sys.configurations
WHERE name like 'max server memory%') as "SQL Mem(GB)",
RIGHT(@@version, LEN(@@version)- 3 - charindex (' ON ', UPPER(@@VERSION))) as "Windows Version",
@MaxDoP AS MaxDoP,
@CostThresholdForParallelism AS CostThreshold,
servinfo.IsClustered,
servinfo.ClusterNodeName,
servinfo.SQLServerServiceAccount,
servinfo.SQLServerStartupType,
servinfo.SQLServerStatus,
servinfo.SQLAgentServiceAccount,
servinfo.SQLAgentStartupType,
servinfo.SQLAgentStatus,
servinfo.SQLFullTextServiceAccount,
servinfo.SQLFullTextStartupType,
servinfo.SQLFullTextStatus,
--@bulocation as "Backup Location",
case when (select top 1 1 from master.dbo.sysobjects where xtype = 'X' and name = 'sqlbackup') = 1 then 'Red-Gate'
else 'Native' end as "Backup Type",
case @rgedition
when 0 THEN 'Trial: Expired'
when 1 THEN 'Trial'
when 2 THEN 'Standard'
when 3 THEN 'Professional'
when 6 THEN 'Lite'
else '' end as "Red Gate Edition",
isnull(@rgversion, '') as "Red Gate Version",
isnull(@rgserialnumber, '') as "Red Gate License"
FROM
ServiceInfo servinfo;

Sunday, July 21, 2019 - 4:53:27 PM - Bhuvanesh	Back To Top (81826)
This is really helpful, Thank you so much.

Wednesday, July 10, 2019 - 9:14:17 AM - Joe Gavin	Back To Top (81723)
This looks very handy.