SQL Server Diagnostic Information Queries Detailed, Day 4

Continuing the series, Query #7 is SQL Server Services Info. It retrieves this information from the sys.dm_server_services DMV. Query 7 is shown in Figure 1.

   1: -- SQL Server Services information (Query 7) (SQL Server Services Info)

   2: SELECT servicename, process_id, startup_type_desc, status_desc, 

   3: last_startup_time, service_account, is_clustered, cluster_nodename, [filename]

   4: FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

   5:  

   6: -- Tells you the account being used for the SQL Server Service and the SQL Agent Service

   7: -- Shows the process_id, when they were last started, and their current status

   8: -- Shows whether you are running on a failover cluster instance

Figure 1: Query #7 SQL Server Services Info

This query tells you the last time that the SQL Server Service started, which is very important information when it comes to interpreting the results of many of the other queries in this set. It also tells you the Service account, and the startup type for the Database Engine and SQL Server Agent, along with their startup types and process ID. It also tells you whether you have a SQL Server failover cluster instance (FCI).

 

Query #8 is SQL Server Agent Jobs. This query returns information about SQL Server Agent jobs from the sysjobs and syscategories tables in the msdb system database. This query gives you a good high-level view of all of the SQL Server Agent jobs on your instance. Query #8 is shown in Figure 2.

   1: -- Get SQL Server Agent jobs and Category information (Query 8) (SQL Server Agent Jobs)

   2: SELECT sj.name AS [JobName], sj.[description] AS [JobDescription], SUSER_SNAME(sj.owner_sid) AS [JobOwner],

   3: sj.date_created, sj.[enabled], sj.notify_email_operator_id, sj.notify_level_email, sc.name AS [CategoryName],

   4: js.next_run_date, js.next_run_time

   5: FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)

   6: INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)

   7: ON sj.category_id = sc.category_id

   8: LEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)

   9: ON sj.job_id = js.job_id

  10: ORDER BY sj.name OPTION (RECOMPILE);

  11:  

  12: -- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured

  13: -- Look for Agent jobs that are not owned by sa

  14: -- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)

  15: -- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)

  16: --

  17: -- MSDN sysjobs documentation

  18: -- http://msdn.microsoft.com/en-us/library/ms189817.aspx

Figure 2: Query #8 SQL Server Agent Jobs

I like to focus on the JobOwner column, which should be sa rather than an individual login, whether the job is enabled or not, and whether there is a notify_email_operator specified.

I also like to see whether people are using jobs from the evil SQL Server Maintenance Plan Wizard that is built into SSMS. The SSMS Maintenance Plan Wizard (prior to SQL Server 2016) makes it far too easy for people to do dumb things, because they don’t know any better. For example, you can have SQL Server rebuild all the indexes in a database (whether they need it or not) and then update statistics (which is not necessary when you rebuild an index in SQL Server). 

I think most instances will be in much better shape if they use Ola Hallengren’s SQL Server Maintenance Solution for their backups and routine SQL Server maintenance.

SQL Server Diagnostic Information Queries Detailed, Day 3

For Day 3 of this series, we are up to Query #5, which is Global Trace Flags. This query simply runs a DBCC command to determine what global trace flags are currently in effect for your current instance of SQL Server. Query #5 is shown in Figure 1.

   1: -- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)

   2: DBCC TRACESTATUS (-1);

   3:  

   4: -- If no global trace flags are enabled, no results will be returned.

   5: -- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.

   6:  

   7: -- Common trace flags that should be enabled in most cases

   8: -- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log

   9: -- The behavior of TF 1118 and 2371 are enabled in SQL Server 2016 by default

  10:  

  11:  

  12: -- SQL Server query optimizer hotfix trace flag 4199 servicing model

  13: -- https://support.microsoft.com/en-us/kb/974006

Figure 1: Query #5 Global Trace Flags

Global trace flags affect the performance and behavior of the entire instance of SQL Server. It is always a good idea to know what global trace flags are in effect on your SQL Server instance, since this can help explain unexpected behavior that you may be seeing. It also gives you an idea about whether whoever setup and/or has been maintaining this instance of SQL Server really knows what they are doing or not. If I see an instance of SQL Server with no global trace flags enabled, that is usually a pretty good indicator that you will find lots of other issues with the instance.

There are certain trace flags that I think should be enabled pretty much in all cases, regardless of your workload. These include:

  • TF 1118  – This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion. As Paul Randal says, “Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on”. In fact, for SQL Server 2016, Microsoft has now included the behavior from TF 1118 in the product by default, so you don’t need to enable it for SQL Server 2016.

 

  • TF 2371 – This trace flag changes the threshold for automatic statistics updates from the old default of needing 20% of the rows in a table to have been updated to trigger a statistics update to a dynamic % that decreases as the table row count increases. This means that you will get more frequent automatic statistics updates on larger tables. Even with this change, it is still a good idea to use SQL Server Agent jobs to periodically trigger manual statistics updates on your most volatile tables. Personally, I think there is no downside to having this enabled. Someone at Microsoft must agree, since for SQL Server 2016, they have now included the behavior from TF 2371 in the product by default, so you don’t need to enable it for SQL Server 2016.

 

  • TF 3226 – This trace flag prevents SQL Server from writing information to the SQL Server Error log after every successful database backup (which includes Full, Differential and Log backups). It will still log failed log backups, which is what you really care about anyway. Records of all database backups are still stored in the msdb system database after enabling this trace flag. Personally, I think there is no downside to having this enabled, and it makes it much easier to find more relevant information in the SQL Server Error log when this trace flag is enabled.

These should be enabled as global trace flags, and as startup trace flags, using SQL Server Configuration Manager, so that they will go into effect whenever SQL Server is restarted.

Query #6 is Process Memory. This query retrieves information from the sys.dm_os_process_memory dynamic management view (DMV) about the memory usage by the SQL Server process. Query #6 is shown in Figure 2.

   1: -- SQL Server Process Address space info  (Query 6) (Process Memory)

   2: -- (shows whether locked pages is enabled, among other things)

   3: SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],

   4:        large_page_allocations_kb, locked_page_allocations_kb, page_fault_count, 

   5:        memory_utilization_percentage, available_commit_limit_kb, 

   6:        process_physical_memory_low, process_virtual_memory_low

   7: FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);

   8:  

   9: -- You want to see 0 for process_physical_memory_low

  10: -- You want to see 0 for process_virtual_memory_low

  11: -- This indicates that you are not under internal memory pressure

Figure 2: Query #6 Process Memory

There are four columns that I usually focus on from the results of this query. The first is SQL Server Memory Usage (MB), which shows how much memory the SQL Server process is actually using (which you should believe rather than what Windows Task Manager says).

Next, I look at locked_page_allocations_kb to see if locked pages in memory (LPIM) is enabled. If the value is above zero, then LPIM is enabled and in effect on the instance. My recommendation is to enable LPIM (as long as you have max server memory set to an appropriate value so that the operating system is never under memory pressure).

Finally, I look at the process _physical memory_low and process_virtual_memory_low columns to see if either one of these flags is set to 1, (which means that their respective memory is low). To be honest, I very rarely ever see either one of these columns come back with a value of 1, since SQL Server typically responds pretty quickly if either of these flags is set by the process.

SQL Server Diagnostic Information Queries Detailed, Day 2

Query #3 is Server Properties. This query simply makes multiple calls to the T-SQL SERVERPROPERTY metadata function to retrieve quite a bit of useful information about the current instance of SQL Server that you are connected to. Query #3 is shown in Figure 1.

   1: -- Get selected server properties (Query 3) (Server Properties)

   2: SELECT SERVERPROPERTY('MachineName') AS [MachineName], 

   3: SERVERPROPERTY('ServerName') AS [ServerName],  

   4: SERVERPROPERTY('InstanceName') AS [Instance], 

   5: SERVERPROPERTY('IsClustered') AS [IsClustered], 

   6: SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 

   7: SERVERPROPERTY('Edition') AS [Edition], 

   8: SERVERPROPERTY('ProductLevel') AS [ProductLevel],                -- What servicing branch (RTM/SP/CU)

   9: SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],    -- Within a servicing branch, what CU# is applied

  10: SERVERPROPERTY('ProductVersion') AS [ProductVersion],

  11: SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion], 

  12: SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion], 

  13: SERVERPROPERTY('ProductBuild') AS [ProductBuild], 

  14: SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],              -- Is this a GDR or OD hotfix (NULL if on a CU build)

  15: SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build

  16: SERVERPROPERTY('ProcessID') AS [ProcessID],

  17: SERVERPROPERTY('Collation') AS [Collation], 

  18: SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 

  19: SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],

  20: SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],

  21: SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], 

  22: SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],

  23: SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],

  24: SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled],    -- New for SQL Server 2016

  25: SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],

  26: SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],

  27: SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];

  28:  

  29: -- This gives you a lot of useful information about your instance of SQL Server,

  30: -- such as the ProcessID for SQL Server and your collation

  31: -- Note: Some columns will be NULL on older SQL Server builds

Figure 1: Query #3 Server Properties

This metadata function was recently enhanced for SQL Server 2012 and SQL Server 2014 by adding quite a bit of information about the product level, product update level, build numbers, etc. Aaron Bertrand went into much more detail about this change here. One challenge with this T-SQL metadata function is that many, but not all of the possible valid values for input values are documented in Books Online (BOL). One example is SERVERPROPERTY(‘InstanceDefaultDataPath’) and SERVERPROPERTY(‘InstanceDefaultLogPath’), which I just recently discovered.

Query #4 is Configuration Values. This query retrieves information from the sys.configurations catalog view. It will return a different number of rows depending on which version of SQL Server you are using. For example, SQL Server 2014 SP1 CU4 returns 70 rows, while SQL Server 2016 CTP 3.2 returns 76 rows. Query #4 is shown in Figure 2.

   1: -- Get instance-level configuration values for instance  (Query 4) (Configuration Values)

   2: SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced

   3: FROM sys.configurations WITH (NOLOCK)

   4: ORDER BY name OPTION (RECOMPILE);

   5:  

   6: -- Focus on these settings:

   7: -- automatic soft-NUMA disabled (should be 0 in most cases)

   8: -- backup checksum default (should be 1)

   9: -- backup compression default (should be 1 in most cases)

  10: -- clr enabled (only enable if it is needed)

  11: -- cost threshold for parallelism (depends on your workload)

  12: -- lightweight pooling (should be zero)

  13: -- max degree of parallelism (depends on your workload and hardware)

  14: -- max server memory (MB) (set to an appropriate value, not the default)

  15: -- optimize for ad hoc workloads (should be 1)

  16: -- priority boost (should be zero)

  17: -- remote admin connections (should be 1)

  18:  

  19: -- New options for SQL Server 2016

  20: -- hadoop connectivity

  21: -- polybase network encryption

  22: -- remote data archive (to enable Stretch Databases)

Figure 2: Query #4  Configuration Values

Even though there are many rows that are returned from this view, I usually focus on just a small number of them, as detailed in the comments below the query. If you find any of these instance-level configuration values that you would like to change, you have two ways of doing it (at least for many of the values). One way is to use the SQL Server Management Studio (SSMS) GUI to change the value, and then either click Ok or script out the change (which is always a much better idea). The other way is to use the sp_configure system stored procedure.

What you want to set these values to depends on your workload and a number of other factors. One cautionary piece of verbiage I recently spotted in BOL was this:

“Advanced options should be changed only by an experienced database administrator or certified SQL Server technician.”

I would agree that you should probably not change these configuration values unless you know what you are doing, but I am not sure what they really mean about a certified SQL Server technician…

SQL Server Diagnostic Information Queries Detailed, Day 1

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!

 

 

SQL Server Diagnostic Information Queries for January 2016

As I discussed yesterday,  I have done some fairly significant reorganization work for the SQL Server 2016, SQL Server 2014, and SQL Server 2012 versions of these queries this month. I have also made some very minor changes to the SQL Server 2008 R2 and older versions of these queries.

Rather than having a separate blog post for each version, I have just put the links for all six major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs that correspond to each query in the set. 

Here are links to the latest versions of these queries for SQL Server 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (January 2016)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (January 2016)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (January 2016)

SQL Server 2012 Blank Results

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy a while ago, and so far, I have not heard any complaints. I did update these queries this month though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2016)

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries (CY 2016)

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries (CY 2016)

SQL Server 2005 Blank Results

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have some comments in the script on how to interpret the results after each query.

You need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks

SQL Server 2014 Service Pack 1 Cumulative Update 4

On December 21, 2015, Microsoft released SQL Server 2014 Service Pack 1 Cumulative Update 4, which is Build 12.0.4436.0. This CU has 34 hotfixes in the public fix list, which is still a fairly substantial number of fixes. This shows that Microsoft has been busy fixing quite a few issues to further improve SQL Server 2014.

If you are still on the SQL Server RTM branch (which I think is a bad thing), Microsoft also released SQL Server 2014 RTM Cumulative Update 11, which is Build 12.0.2560, on the same day.

The official Microsoft build list is available from this KB article:

SQL Server 2014 build versions

This KB article has a lot of very useful information on how to properly configure SQL Server 2012/2014 for the best performance and scalability:

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads

SQL Server Diagnostic Query Reorganization

After much thought, and some procrastination, I have decided to implement a fairly significant reorganization of my SQL Server Diagnostic Information Queries, starting with the SQL Server 2016 version for January 2016. I use these queries on a daily basis during my consulting work at SQLskills.com, and I have been planning on making some changes to make them easier for me to use and analyze.

The main thrust of this reorganization effort to to change the order of the queries so that they are grouped more logically, making the results easier to comprehend and analyze. The general ordering is shown below:

  1. Instance-level information
    1. SQL Server configuration details
    2. OS configuration information
    3. Hardware details
    4. Storage details and performance information
    5. Database properties for all databases
    6. Instance-level performance information
  2. Specific database information
    1. File information and usage
    2. Query information
    3. Stored procedure information
    4. Index information
    5. Other performance information

In addition to this, I have revised and improved several of the queries so that they retrieve even more relevant information than before. I am continuing to add additional comments on how to interpret the results, along with additional links to useful sources of additional information about specific queries.

Finally, I have removed a number of queries that I have found to be less useful, taking the overall query count from 76 down to 69 for the SQL Server 2016 version. This will reduce the time required to run the queries and analyze the results.

Here are links to the current version of these queries for SQL Server 2016:

SQL Server 2016 Diagnostic Information Queries (January 2016)

SQL Server 2016 Blank Results

I am also planning on making some formatting changes in some of the queries, so that they work better with Excel.

I am going to make a similar reorganization effort for the SQL Server 2014 and SQL Server 2012 versions of these queries over the next couple of days. I will also make some minor updates to the SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005 versions of the queries for the January release.

Please let me know what you think of these reorganized queries, and whether you have any suggestions for improvements. Thanks!

Upgrading a SATA III SSD

Back in April of 2012, I built an Intel Z77 workstation that used a 512GB OCZ Vertex 4 SATA III SSD, plugged into a SATA III port for it’s Windows 7 boot drive. Back then, this was arguably the fastest consumer SSD available, with an MSRP of $699.99. This drive has soldiered on for over three and a half years, with no problems. It was getting a little low on disk space though, so I decided it was time for an upgrade to a larger, faster SSD.

I ended up getting a 1TB Samsung 850 EVO SATA III SSD, for $329.99 at my local Micro Center. The 850 EVO line has been around for about a year now, and prices have come down quite a bit since they were introduced. It is pretty amazing to get double the size (and better performance) at less than half the price, compared to what was available back in 2012.

Before I cloned the existing drive, I ran CrystalDiskMark 5.0.2 on it with a 4GB test file. The results are shown in Figure 1.

 

image

Figure 1: 512GB OCZ Vertex 4 SATA III SSD Benchmark results

 

I used the free Samsung Data Migration software (which only works with Samsung SSDs as the cloning target) to clone the old OCZ drive to the new Samsung drive. I used an Apricorn SATA Wire 3.0 plugged into a front-panel USB 3.0 port to connect the new Samsung drive for the cloning process. I could have shut down the system, and plugged the new Samsung drive into a native SATA III port to get better copy performance, but I was too lazy to do that… As it was, I was seeing about 125MB/sec during the cloning copy process, which was fast enough. If you are cloning/upgrading a drive in a laptop, you pretty much have to use a USB port to do it.

After the cloning process was complete, I shut down the system and swapped the drives. Windows 7 booted up without any problems, although it wanted a reboot once it realized that the drive had been changed. I also noticed that Windows 7 had lost it’s recollection of ever checking for Windows and Microsoft Updates, but asking it to check for updates fixed that issue.

Next, I fired up the Samsung Magician 4.9 software, which informed me that the new Samsung 850 EVO needed a firmware update. Before I ran the firmware update, I ran CrystalDiskMark 5.0.2 with the same settings as the previous test. The results are shown in Figure 2.

 

image

Figure 2: 1TB Samsung 850 EVO SATA III SSD Benchmark results (before firmware update)

 

After the drive firmware update, Windows 7 booted up without any problems, although it wanted another reboot once it realized that the drive firmware had been updated. I ran CrystalDiskMark 5.0.2 once again with the same settings as the previous test. The results are shown in Figure 3.

 

image

Figure 3: 1TB Samsung 850 EVO SATA III SSD Benchmark results (after firmware update)

As you can see, the benchmark results improved after the firmware update. I have not found any release notes for the firmware update (and it is not even listed on their web page), but at least the latest version of Samsung Magician knew about it.

 

image

Figure 4: Samsung Magician 4.9

I have not enabled RAPID Mode on the drive yet, but I know from prior experience that it can have a nice positive effect on performance. It does make it harder to analyze your storage performance when SQL Server is running on your workstation though. All in all, a pretty easy, trouble-free installation.

Windows Server 2016 Technical Preview 4

Microsoft recently (Nov 19, 2015) released Windows Server 2016 Technical Preview 4, which is the fourth pre-release version of Windows Server 2016. You can download it here. According to Mary Jo Foley, Windows Server 2016 is due for release in the second half of 2016. It looks like Windows Server 2016 will be moving to a per core licensing model (similar to what SQL Server has had since SQL Server 2012).

There are a lot of interesting new features and enhancements in Windows Server 2016, especially in Hyper-V and Storage. I think it makes sense to start paying some attention to Windows Server 2016 as it is getting closer to final release. Below, I have some links so you can read more about Windows Server 2016, and how it will be the best OS for SQL Server 2016.

Discover new storage technologies and new features for existing technologies in Windows Server 2016 Technical Preview

What’s New in File and Storage Services in Windows Server 2016 Technical Preview

 

Discover what’s new in clustering in Windows Server 2016 Technical Preview

What’s New in Failover Clustering in Windows Server Technical Preview

 

Storage Spaces Direct is a new feature in Windows Server 2016 Technical Preview that enables building highly available (HA) storage systems with local storage which is either disk devices that are internal to each storage node, or disk devices in JBODs where each JBOD is only connected to a single storage node.

Storage Spaces Direct in Windows Server 2016 Technical Preview

I talked about Storage Spaces Direct (S2D) in this article.

 

Storage Replica is a new feature in Windows Server 2016 Technical Preview that enables storage-agnostic, block-level, synchronous replication between clusters or servers for disaster recovery, as well as stretching of a failover cluster for high availability.

Storage Replica in Windows Server 2016 Technical Preview

 

Storage Quality of Service (Storage QoS) is a new feature in Windows Server 2016 Technical Preview that provides a way to centrally monitor and manage storage performance for virtual machines using Hyper-V and the Scale-Out File Server roles. The feature automatically improves storage resource fairness between multiple virtual machines using the same file server cluster and allows specific minimum and maximum performance goals to be configured in units of normalized IOPs.

Storage Quality of Service

 

Cluster Operating System Rolling Upgrade is a new feature in Windows Server 2016 Technical Preview that enables an administrator to upgrade the operating system of the cluster nodes from Windows Server 2012 R2 to Windows Server 2016 Technical Preview without stopping the Hyper-V or the Scale-Out File Server workloads. Using this feature, the downtime penalties against Service Level Agreements (SLA) can be avoided.

Cluster Operating System Rolling Upgrade

SQL Server Diagnostic Information Queries for December 2015

This month, I revised several queries, mainly based on suggestions from readers. The SQL Server 2016 version will continue to be improved with more SQL Server 2016-specific queries and new columns in existing queries over the next few months as I discover interesting new things in SQL Server 2016. I am also continuing to add more relevant comments and links to the other active versions of these queries in order to help better interpret the results of these queries and save me time in looking up supporting documentation. I am planning on making some improvements to the SQL Server 2012 version, to take advantage of some the DMV enhancements in SQL Server 2012 SP3.

Rather than having a separate blog post for each version, I have just put the links for all six major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs that correspond to each query in the set. 

Here are links to the latest versions of these queries for SQL Server 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (December 2015)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (December 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (December 2015)

SQL Server 2012 Blank Results

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy nine months ago, and so far, I have not heard any complaints. I am going to update these queries next month.

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have some comments in the script on how to interpret the results after each query.

You need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!