SQL Server Configuration Settings Query
By: Alejandro Cobar | Updated: 2019-07-10 | Comments (5) | Related: More > Monitoring
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.
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.
|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 3rd 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.|
- 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.
Last Updated: 2019-07-10
About the author
View all my tips