SQL Server Diagnostic Information Queries Detailed, Day 1

By: Glenn Berry
Posted on: January 1, 2016 9:45 am

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

SQL Server 2014 DMV Diagnostic Queries – Part 1

SQL Server 2014 DMV Diagnostic Queries – Part 2

SQL Server 2014 DMV Diagnostic Queries – Part 3

 

Please let me know what you think of this series. Thanks!

 

 

Posted in: DMV Diagnostic Queries Detailed, SQL Server Configuration
1 Comment

Setting Your Page Verify Database Option to CHECKSUM

By: Glenn Berry
Posted on: October 16, 2014 8:15 am

One thing I still run into quite often are SQL Server 2005 and newer databases that have their Page Verify database option set to TORN_PAGE or NONE. The most common reason for this is that an older database that was originally created in SQL Server 2000 or older was upgraded to SQL Server 2005 or newer, and the Page Verify Option was left at the older and less effective TORN_PAGE value. I also run into instances where people have changed the Page Verify database option to NONE, thinking that this would have a dramatic beneficial effect on performance (which is not true).

From BOL: “When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.”

Paul Randal talked about some of the myths around page verify here. Kendra Little wrote a good post that demonstrates how CHECKSUM reacts to corruption here.

In my opinion, all of your databases should be using CHECKSUM for their Page Verify database option. You can easily query sys.databases to find out the status of the Page Verify database option for all of your databases with this query:

-- Get value of page verify option for all databases
SELECT name, page_verify_option_desc
FROM sys.databases;

 

If you have just a few databases, it is pretty easy to run code like this for each one, to change this option:

-- T-SQL to change Page Verify option to CHECKSUM for a single database
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
GO

 

If you have a large number of databases that need to be changed, you can write a query to generate the ALTER DATABASE statements for you, like this:

-- Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM
SELECT N'ALTER DATABASE [' + db.name + N'] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;'
FROM sys.databases AS db 
WHERE db.page_verify_option_desc <> N'CHECKSUM';

 

After you run this query, you can copy the rows from your results grid in SSMS to a new query window, and then run your ALTER DATABASE statements when you are ready, without having to write all of the T-SQL code yourself.

Keep in mind that just changing the setting to CHECKSUM does not instantly add CHECKSUMs to your existing data pages in the database. In order for this to happen, you have to read each page into memory, make some sort of change and then write it back out to the storage subsystem. This can happen from normal INSERT/UPDATE/DELETE activity over time, or from rebuilding your indexes.

Here are some useful links on this subject:

Checksum in SQL2005

How to tell if the IO subsystem is causing corruptions?

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

Checksum and tempdb

Performance impact of enabling page checksum and default trace

Posted in: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server Configuration, Storage
9 Comments

Learn About SQL Server Hardware This October in Chicago

By: Glenn Berry
Posted on: June 4, 2014 10:47 am

On October 9-10, 2014, I will be teaching IEHW: Immersion Event on SQL Server Hardware in Chicago, IL. This is a great opportunity for you to learn how to properly select and configure your server hardware and storage subsystem to get the best performance and scalability for the lowest SQL Server 2012/2014 licensing costs. You will also learn how to properly configure your hardware, storage subsystem, operating system, and SQL Server itself for the best performance and scalability for your workload. Attendees of this class will learn how to analyze, select, and size their database server hardware and storage subsystems for different types of SQL Server workloads in order to get the best performance and scalability while minimizing their SQL Server 2012/2014 license costs.

Since SQL Server 2014 is available, and Windows Server 2012 R2 has been available for over nine months, and we have new server models with the 2nd generation Intel Xeon E5 and E7 processor families, I think that it is going to make a lot of sense for many organizations to do a complete data platform refresh sometime during 2014/2015. By making wise hardware selection choices, you can easily save so much money on your SQL Server 2012/2014 licensing costs, that your actual server hardware is free. This is not an exaggeration! 

This two-day SQL Server hardware training class explains the core fundamentals and deeper details of database server hardware and storage subsystems for SQL Server database professionals. Many database professionals are completely unfamiliar with the details and nuances of modern server hardware and storage subsystems, while many server and storage administrators are completely unfamiliar with the specific workload demands of a SQL Server database server. Taking this course will allow you to bridge that gap.

This class also covers how to properly configure and benchmark your database server hardware and storage subsystems, along with how to properly install and configure the operating system and SQL Server for the best performance and reliability. The class will show you how to diagnose and troubleshoot hardware and storage related performance issues, and will include coverage of how virtualization interacts with your database server hardware and storage subsystem. Note: the primary audience for this class is SQL Server database professionals, not general system/server admins who are already familiar with server/storage hardware.

I think it is very important for database administrators to know as much as possible about the critical details of their server hardware and storage subsystem, rather than trusting their fate to “Shon the server guy”, who may or may not know that much about modern server hardware (maybe he is a networking specialist). Even if Shon is very knowledgeable about hardware, he may not understand the different demands that SQL Server will create with different types of workloads. I want you to be able to successfully make the case for selecting the best hardware and storage subsystem components for your workload and budget.  You can read more about the registration details here.

Special Pricing Options and Referrals
  • Past attendee price: If you’ve attended an Immersion Event in the past, you can register any time for 75% of the full price ($1,099). Please contact us for instructions.
  • Refer someone: If you know someone who would benefit from this class, refer them to us and when they register, we’ll give you a $50 Amazon gift card. They or you just need to let us know you’re referring them, and when they register, we’ll match them to your referral and send you the gift card.
Posted in: Hardware, IEHW, SQL Server Configuration, Teaching
1 Comment