Since I have not learned my lesson from doing a daily blog post for a month from the previous several times that I have done it in the past, I am going to start a new blog series for the month of January 2016. This one will discuss each the queries in the January 2016 version of my SQL Server Diagnostic Information Queries for SQL Server 2016 in some detail. I will cover two or three queries a day, to make it through the set in 31 days.
Even if you are using an older version of SQL Server (which is very likely, since SQL Server 2016 is still in a CTP status), this series should still be quite relevant, since the vast majority of the queries in the set have similar or identical versions that work in older versions of SQL Server. The latest version of these queries for SQL Server 2005 through SQL Server 2016 is available here.
Query #1 is Version Info. This is designed to retrieve the name of your server/instance, along with the complete version, edition, and update level of your SQL Server instance. It also retrieves some information about your operating system. Query #1 is shown in Figure 1.
1: -- SQL and OS Version information for current instance (Query 1) (Version Info)
2: SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];
3:
4: -- SQL Server 2016 RTM Branch Builds
5: -- Build Description Release Date
6: -- 13.0.200.172 CTP 2.0 5/26/2015
7: -- 13.0.300.44 CTP 2.1 6/14/2015
8: -- 13.0.407.1 CTP 2.2 7/28/2015
9: -- 13.0.500.53 CTP 2.3 9/4/2015
10: -- 13.0.600.65 CTP 2.4 9/30/2015
11: -- 13.0.700.242 CTP 3.0 10/29/2015
12: -- 13.0.900.73 CTP 3.2 12/12/2015
13:
14:
15:
16: -- Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads
17: -- http://support.microsoft.com/kb/2964518/EN-US
18:
19: -- How to determine the version, edition and update level of SQL Server and its components
20: -- https://support.microsoft.com/en-us/kb/321185
Figure 1: Query #1 – Version Info
The reason why this is such a useful query is because it quickly gives you so much relevant information about your current SQL Server instance. When you are looking at an instance of SQL Server, one of the first things you want to know is the major version and edition of SQL Server that you are dealing with. For example, it makes quite a bit of difference if you are working with SQL Server 2005 Standard Edition vs. SQL Server 2014 Enterprise Edition in terms of what features are available, what common problems might be, and even whether it is still under any kind of product support from Microsoft.
This query also gives you the exact build number that is installed, which is extremely useful information. Knowing the exact build number lets you determine how old the build is, and whether it may be having issues that were corrected in later builds of SQL Server. It also gives you some indirect information about how old the instance might be and how well maintained it might be. If a particular build is old enough, it might be part of what Microsoft considers an “unsupported service pack”, which means that Microsoft Customer Service and Support (CSS) will only give you limited trouble-shooting support until you upgrade to a newer build that is still fully supported.
More importantly, I firmly believe that you are much less likely to run into problems with your SQL Server instances if you make a diligent effort to try to stay as current as possible with SQL Server Service Packs and Cumulative Updates. This especially true with SQL Server 2012 and SQL Server 2014 (which are the only versions still in mainstream support), since Microsoft typically fixes anywhere from 30 to 60 issues (some of them quite significant) in each SQL Server Cumulative Update.
Of course, many people are still hesitant to install any type of updates, because of the the time and difficulty involved. There is always a small risk of “breaking” your applications when you install a SQL Server update. It takes time to develop and execute a good testing, deployment and rollback plan, and installing SQL Server updates will cause some some downtime, even with a good high availability (HA) solution in place.
Despite this, I think giving in to fear and inertia is a mistake. Doing the work to come up with these testing plans, and then actually deploying SQL Server updates (along with Microsoft Updates, driver and firmware updates, etc.) on a regular basis is good for you and for your organization. It makes you a better DBA and develops your skillset. Taking some planned downtime to maintain your infrastructure (including SQL Server) helps prevent unplanned downtime.
Query #2 is Core Counts. This is designed to get the socket, physical core and logical core counts from the SQL Server Error log. Query #2 is shown in Figure 2.
1: -- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
2: -- This query might take a few seconds if you have not recycled your error log recently
3: EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
4:
5: -- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
6: -- It can also help you confirm your SQL Server licensing model
7: -- Be on the lookout for this message "using 20 logical processors based on SQL Server licensing"
8: -- which means grandfathered Server/CAL licensing
9: -- This query will return no results if your error log has been recycled since the instance was last started
Figure 2: Query #2 – Core Counts
This query tries to read the first SQL Server Error log to find the message about sockets and core counts to get this very useful information. This only works with SQL Server 2012 and newer. It also only works if the SQL Server Error log has not been recycled so many times that the original error log is no longer available. It would be much better if this information was exposed in a new column in an existing DMV. This query is useful for both virtualized instances and non-virtualized instances.
This query helps you determine whether Intel Hyper-Threading (HT) is enabled, and exactly how many physical sockets, physical cores, and logical cores are visible to SQL Server. It is also very useful for detecting problems caused by SQL Server Standard Edition licensing limits. For example, lets assume that “Shon the Server Guy” has purchased a shiny new Dell PowerEdge R730 two-socket server with two, Intel Xeon E5-2680 v3 12-core processors and 768GB of RAM, where you are planning on installing SQL Server 2014 Standard Edition.
Unfortunately, Shon was not aware of the ridiculously low licensing limits for SQL Server 2014 Standard Edition (which are 16 physical cores and 128GB of RAM per instance) when he bought this new server. When you install SQL Server 2014 Standard Edition on this machine, it will only use 16 physical cores and 128GB of RAM for the Database Engine. By default, it will use all twelve physical cores on the first processor, and then only use four physical cores the second processor. You can fix this imbalance by using an ALTER SERVER CONFIGURATION command as I discuss in this blog post:
Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes
Luckily, Microsoft has announced that they are raising the core count and RAM amount license limits to an unspecified higher amount for SQL Server 2016 Standard Edition. This is good news, even though I think it would still be better to eliminate those limits entirely, as was the case with SQL Server 2008 Standard Edition. Unfortunately, I don’t think that will ever happen since Windows Server 2016 is going to start using core-based licensing.
These three Pluralsight Courses go into more detail about how to run these queries and interpret the results
Please let me know what you think of this series. Thanks!
One thought on “SQL Server Diagnostic Information Queries Detailed, Day 1”
excellent series for an accidental DBA!!!!!