SQL Server Diagnostic Information Queries Detailed, Day 9

For Day 9 of this series, we start out with Query #19, which is BPE Configuration.  This query retrieves information about your buffer pool extension (BPE) configuration from the sys.dm_os_buffer_pool_extension_configuration dynamic management view. Query #19 is shown in Figure 1.

   1: -- See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration)

   2: SELECT [path], state_description, current_size_in_kb, 

   3: CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]

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

   5:  

   6: -- BPE is available in both Standard Edition and Enterprise Edition

   7: -- It is a more interesting feature for Standard Edition

   8:  

   9: -- Buffer Pool Extension to SSDs in SQL Server 2014

  10: -- http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx

Figure 1: Query #19 BPE Configuration

BPE was a new feature that was added to SQL Server 2014. The idea behind it is that you can set aside some space in your file system for a read-only cache file of clean buffer pool pages, that makes it look like your buffer pool is larger than it actually is. SQL Server will look in the actual buffer pool first, and then in the BPE file, and finally in your storage subsystem to find the data that it needs. The ideal scenario for this to be helpful is if you have a read-intensive, OLTP workload that does a lot of random reads from your data files (but you don’t have enough RAM to fit the data that is being read into the actual buffer pool). If you are using magnetic storage that has relatively poor random read I/O performance, and if your BPE file is on fast, local flash storage, then, you might see a performance improvement from using BPE.

The problem with this in real life is that there is typically a lot of write activity to the BPE file to keep it up to date with the data that it is trying to cache. Some types of less expensive flash storage has performance issues when it is under sustained write pressure. It is also hard to drive an OLTP workload hard enough to cause it to actually use the BPE file in most scenarios, unless you set max server memory artificially low. The BPE feature is mainly interesting if you are using SQL Server 2014 Standard Edition, where you are restricted to using 128GB of RAM per instance for the Database Engine.

 

Query #20 is BPE Usage. This query retrieves data from the sys.dm_os_buffer_descriptors dynamic management view. Query #20 is shown in Figure 2.

   1: -- Look at buffer descriptors to see BPE usage by database (Query 20) (BPE Usage) 

   2: SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],

   3: CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)], 

   4: AVG(read_microsec) AS [Avg Read Time (microseconds)]

   5: FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

   6: WHERE database_id <> 32767

   7: AND is_in_bpool_extension = 1

   8: GROUP BY DB_NAME(database_id) 

   9: ORDER BY [Buffer size(MB)] DESC OPTION (RECOMPILE);

  10:  

  11: -- You will see no results if BPE is not enabled or if there is no BPE usage

Figure 2: Query #20 BPE Usage

This query (which usually takes a few seconds to run), will show you which databases are actually using your BPE file. I have not seen too many people using the BPE feature so far, which I think is a shame. If you have the right kind of workload, it may help performance. It is certainly worth some testing to try it out.

SQL Server Diagnostic Information Queries Detailed, Day 8

For Day 8 of this series, we start out with Query #16, which is Hardware Info.  This query retrieves some hardware-related information from the sys.dm_os_sys_info dynamic management view. Query #16 is shown in Figure 1.

   1: -- Hardware information from SQL Server 2016  (Query 16) (Hardware Info)

   2: SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],

   3: cpu_count/hyperthread_ratio AS [Physical CPU Count], 

   4: physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],

   5: committed_target_kb/1024 AS [Committed Target Memory (MB)],

   6: max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type], 

   7: sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type], 

   8: softnuma_configuration_desc AS [Soft NUMA Configuration]

   9: FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

  10:  

  11: -- Gives you some good basic hardware information about your database server

  12: -- Cannot distinguish between HT and multi-core

  13: -- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM

  14: -- It merely indicates that you have a hypervisor running on your host

  15: -- Soft NUMA configuration is a new column for SQL Server 2016

Figure 1: Query #16 Hardware Info

This query tells you some very useful information about your hardware, even though it is frustratingly incomplete. You can determine your physical socket count, and the total number of logical processors, but you cannot tell what type of processor you have or whether Intel Hyper-Threading is enabled or not. You can also determine how much RAM is visible to the machine you are running on (physical or virtual).

You can also tell whether a hypervisor is present on the host you are running on, and whether someone has manually modified CPU process affinity. New in SQL Server 2016, you can see whether soft NUMA has been enabled. Finally, you can see the last time the SQL Server Service was started, which is very important to know when you are interpreting the results of many of the other queries in this set. It would be very nice if the results from the next two queries were also included in this DMV.

 

Query #17 is System Manufacturer.  This query tries to read the SQL Server Error log to determine the manufacturer and model number of the server that you are running on. If you are running inside of a hypervisor virtual machine, you will see that instead of the actual host hardware. Query #17 is shown in Figure 2.

   1: -- Get System Manufacturer and model number from SQL Server Error log (Query 17) (System Manufacturer)

   2: EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer'; 

   3:  

   4: -- This can help you determine the capabilities and capacities of your database server

   5: -- Can also be used to confirm if you are running in a VM

   6: -- This query might take a few seconds if you have not recycled your error log recently

   7: -- This query will return no results if your error log has been recycled since the instance was started

Figure 2: Query #17 System Manufacturer

Knowing the brand and model of the server you are running on helps you figure out other useful information, such as how many PCIe expansion slots it has, how much memory it supports, and what type of processors it supports. It also helps you figure out how old it might be. This query will return no result if the SQL Server Error log has been recycled enough times to rollover since it was started. I really hate having to pull information like this from the SQL Server Error log, it would be much better to get it from a DMV.

 

Query #18 is Processor Description.  This query retrieves the processor description information from the Windows Registry. Query #18 is shown in Figure 3.

   1: -- Get processor description from Windows Registry  (Query 18) (Processor Description)

   2: EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

   3:  

   4: -- Gives you the model number and rated clock speed of your processor(s)

   5: -- Your processors may be running at less than the rated clock speed due

   6: -- to the Windows Power Plan or hardware power management

   7:  

   8: -- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information

   9: -- http://www.cpuid.com/softwares/cpu-z.html

  10:  

  11: -- You can learn more about processor selection for SQL Server by following this link

  12: -- http://www.sqlskills.com/blogs/glenn/processor-selection-for-sql-server/

Figure 3: Query #18 Processor Description

I think it is critical to know exactly what processor(s) you are running on your SQL Server instances. This helps you understand the capabilities of the system and how old it might be. It is also very important to know this if you are thinking about doing a server upgrade or consolidation effort. Even though the gory details of processors are probably not that interesting to many database professionals, it is actually pretty important for licensing purposes and for performance and scalability.

SQL Server Diagnostic Information Queries Detailed, Day 7

Note: The three queries I am discussing today are not particularly interesting or useful if you are not using either a traditional, failover cluster instance (FCI), or an AlwaysOn Availability Group.

For Day 7 of this series, we start out with Query #13, which is SQL Server Error Log. This query retrieves information from the sys.dm_os_server_diagnostics_log_configurations dynamic management view with the current configuration for the SQL Server failover cluster diagnostic log. Query #13 is shown in Figure 1.

   1: -- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured  (Query 13) (SQL Server Error Log)

   2: SELECT is_enabled, [path], max_size, max_files

   3: FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);

   4:  

   5: -- Knowing this information is important for troubleshooting purposes

   6: -- Also shows you the location of other error and diagnostic log files

Figure 1: Query #13 SQL Server Error Log

This query basically shows you where all the SQL Server related error and diagnostic logs are located. Knowing this can be very helpful if you need to troubleshoot problems with various parts of SQL Server. For example, if the SQL Server database engine will not start, opening the SQL Server Error log in a text editor will often give you some very useful information about what the problem is.

 

Query #14 is Cluster Node Properties. This query retrieves information from the sys.dm_os_cluster_nodes dynamic management view with one row for each node in the failover cluster instance configuration. If you have a standalone instance of SQL Server, this query will return an empty rowset. Query #14 is shown in Figure 2.

   1: -- Get information about your cluster nodes and their status  (Query 14) (Cluster Node Properties)

   2: -- (if your database server is in a failover cluster)

   3: SELECT NodeName, status_description, is_current_owner

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

   5:  

   6: -- Knowing which node owns the cluster resources is critical

   7: -- Especially when you are installing Windows or SQL Server updates

   8: -- You will see no results if your instance is not clustered

Figure 2: Query #14 Cluster Node Properties

This query is useful because it lets you identify each node in the cluster, see it’s current status, and see which node currently owns the instance.

 

Query #15 is AlwaysOn AG Cluster. This query retrieves information from the sys.dm_hadr_cluster dynamic management view about the Windows Server Failover Clustering (WSFC) quorum, if one exists on the node that is hosting the SQL Server instance, and you are using an AlwaysOn AG. Query #15 is shown in Figure 3.

   1: -- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)

   2: SELECT cluster_name, quorum_type_desc, quorum_state_desc

   3: FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);

   4:  

   5: -- You will see no results if your instance is not using AlwaysOn AGs

   6:  

   7: -- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters

   8: -- http://support.microsoft.com/kb/2920151

Figure 3: Query #15 AlwaysOn AG Cluster

This query is one way of detecting the fact that there is an AlwaysOn AG in place on your instance, and then seeing what quorum type is being used.

If you are going to be using either traditional FCIs or AlwaysOn AGs (which require WSFC, but not shared storage), it is very important that you use Windows Server 2012 or newer for your operating system. The clustering code was basically rewritten in Windows Server 2012, and it was further improved in Windows Server 2012 R2.

SQL Server Diagnostic Information Queries Detailed, Day 6

For Day 6 of this series, we start out with Query #11, which is SQL Server NUMA Info. This query retrieves information from the sys.dm_os_nodes dynamic management view about the NUMA node structure that has been created by the SQLOS. By default, this will mimic the hardware processor locality (whether it is the actual hardware of the host, or a virtual machine), but this structure can be altered by using soft-NUMA. Query #11 is shown in Figure 1.

   1: -- SQL Server NUMA Node information  (Query 11) (SQL Server NUMA Info)

   2: SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, 

   3:        active_worker_count, avg_load_balance, resource_monitor_state

   4: FROM sys.dm_os_nodes WITH (NOLOCK) 

   5: WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

   6:  

   7: -- Gives you some useful information about the composition and relative load on your NUMA nodes

   8: -- You want to see an equal number of schedulers on each NUMA node

   9: -- Watch out if SQL Server 2016 Standard Edition has been installed on a machine with more than 16 physical cores

  10:  

  11: -- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

  12: -- http://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

Figure 1: Query #11 SQL Server NUMA Info

This query tells you how many NUMA nodes are being used by SQLOS. In a modern, NUMA-capable server, you should see one NUMA node for each physical processor socket (unless someone has disabled NUMA in the UEFI/BIOS). So a two-socket server should have two NUMA nodes. If you are using virtualization, you might see a  VM with only one NUMA node and many virtual processor cores, or whoever created the VM might have used multiple NUMA nodes with fewer virtual processor cores per NUMA node. You also want to confirm that you are seeing the same online_scheduler_count on each NUMA node.

Query #12 is System Memory. This query retrieves information from the sys.dm_os_sys_memory dynamic management view about the memory usage by the operating system. Query #12 is shown in Figure 2.

   1: -- Good basic information about OS memory amounts and state  (Query 12) (System Memory)

   2: SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 

   3:        available_physical_memory_kb/1024 AS [Available Memory (MB)], 

   4:        total_page_file_kb/1024 AS [Total Page File (MB)], 

   5:        available_page_file_kb/1024 AS [Available Page File (MB)], 

   6:        system_cache_kb/1024 AS [System Cache (MB)],

   7:        system_memory_state_desc AS [System Memory State]

   8: FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

   9:  

  10: -- You want to see "Available physical memory is high" for System Memory State

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

Figure 2: Query #12 System Memory

This query tells you how much memory is visible to the operating system (whether it is the host OS or the guest OS for a virtual machine). It also tells you how much memory is available to the operating system, which is normally a relatively low amount when SQL Server has seen a normal workload. It is very important to look at the system_memory_state_desc column result, which is the result of two separate flags about the system memory state.

What you want to see is “Available physical memory is high”, which means that the OS has plenty of available memory, and you are not under external memory pressure. What you might see in some cases is  “Available physical memory is steady”, or “Available physical memory is low”, which means that you are under external memory pressure, which is something you don’t want to see.

SQL Server Diagnostic Information Queries Detailed, Day 5

For Day 5 of the series, we have Query #9, which is SQL Server Agent Alerts. This query looks at the sysalerts table on the msdb system database. Query #9 is shown in Figure 1.

   1: -- Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts)

   2: SELECT name, event_source, message_id, severity, [enabled], has_notification, 

   3:        delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time

   4: FROM msdb.dbo.sysalerts WITH (NOLOCK)

   5: ORDER BY name OPTION (RECOMPILE);

   6:  

   7: -- Gives you some basic information about your SQL Server Agent Alerts (which are different from SQL Server Agent jobs)

   8: -- Read more about Agent Alerts here: http://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

Figure 1: Query #9 SQL Server Agent Alerts

In my experience, probably 90% of the SQL Server instances that I look at don’t have any SQL Server Agent Alerts in place, often because many DBAs are not sure what they are or why they are useful.

Despite the name, SQL Server Agent Alerts have very little to do with SQL Server Agent jobs. I have run into quite a few DBAs that mistakenly assume that SQL Server Agent Alerts are what occur when SQL Server Agent jobs fail. This is not the case. These are actually special alerts that you can create to detect when certain errors or other conditions occur, such as SQL Server performance condition alerts or WMI event alerts.

When one of these alerts is triggered, you can have SQL Server Agent run a SQL Server Agent job and/or notify a SQL Server operator via e-mail or pager/smart phone. This makes it much more likely that someone will notice a problem sooner rather than later. This blog post explains SQL Server Agent Alerts in more detail, and it also has a link to where you can download a T-SQL script that I wrote that you can use to create some very useful Agent Alerts on your server.

 

Query #10 is Windows Info. This query retrieves information from the sys.dm_os_windows_info dynamic management view about the operating system that you are running your instance of SQL Server on. Query #10 is shown in Figure 2.

   1: -- Windows information (Query 10) (Windows Info)

   2: SELECT windows_release, windows_service_pack_level, 

   3:        windows_sku, os_language_version

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

   5:  

   6: -- Gives you major OS version, Service Pack, Edition, and language info for the operating system

   7: -- 10.0 is either Windows 10 or Windows Server 2016

   8: -- 6.3 is either Windows 8.1 or Windows Server 2012 R2 

   9: -- 6.2 is either Windows 8 or Windows Server 2012

  10: -- 6.1 is either Windows 7 or Windows Server 2008 R2

  11: -- 6.0 is either Windows Vista or Windows Server 2008

  12:  

  13: -- Windows SKU codes

  14: -- 4 is Enterprise Edition

  15: -- 7 is Standard Server Edition

  16: -- 8 is Datacenter Server Edition

  17: -- 10 is Enterprise Server Edition

  18: -- 48 is Professional Edition

  19:  

  20: -- 1033 for os_language_version is US-English

  21:  

  22: -- SQL Server 2014 requires Windows Server 2012 or newer

  23:  

  24: -- Quick-Start Installation of SQL Server 2016

  25: -- https://msdn.microsoft.com/en-us/library/bb500433(v=sql.130).aspx

  26:  

  27: -- Hardware and Software Requirements for Installing SQL Server 2016

  28: -- https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx

  29:  

  30: -- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments

  31: -- http://support.microsoft.com/kb/2681562

Figure 2: Query #10 Windows Info

Unfortunately, the raw information that this query returns is a little cryptic. For example, the windows_release column returns a numeric value for the major release number of the operating system, such as 6.3. Unless you have a very good knowledge for computer trivia, you might not know that 6.2 means either Windows 8.1 or Windows Server 2012 R2. That is why I list the relevant possibilities in the comments below the query.

You also cannot tell directly from the version number alone whether you are dealing with the server operating system (like Windows Server 2012 R2) or a client operating system (like Windows 8.1), even though you should not be running a Production instance of SQL Server on a client operating system. The windows_sku column can sometimes help you indirectly determine that.

Knowing the details of the operating edition version and edition can be very useful. For example, Windows Server 2008 R2 Standard Edition is limited to 32GB of RAM, which often catches people by surprise.

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