· 7 years ago · Nov 08, 2018, 08:40 PM
1
2-- SQL Server 2016 Diagnostic Information Queries
3-- Glenn Berry
4-- Last Modified: November 3, 2018
5-- https://www.sqlskills.com/blogs/glenn/
6-- http://sqlserverperformance.wordpress.com/
7-- Twitter: GlennAlanBerry
8
9-- Please listen to my Pluralsight courses
10-- https://www.pluralsight.com/author/glenn-berry
11
12-- If you want to find all of our SQLskills SQL101 blog posts, check out https://www.sqlskills.com/help/sql101/
13
14
15-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server
16
17
18-- If you like PowerShell, there is a very useful community solution for running these queries in an automated fashion
19-- https://dbatools.io/
20
21-- Invoke-DbaDiagnosticQuery
22-- https://dbatools.io/functions/invoke-dbadiagnosticquery/
23
24
25--******************************************************************************
26--* Copyright (C) 2018 Glenn Berry, SQLskills.com
27--* All rights reserved.
28--*
29--* For more scripts and sample code, check out
30--* https://www.sqlskills.com/blogs/glenn
31--*
32--* You may alter this code for your own *non-commercial* purposes. You may
33--* republish altered code as long as you include this copyright and give due credit.
34--*
35--*
36--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
37--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
38--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
39--* PARTICULAR PURPOSE.
40--*
41--******************************************************************************
42
43-- Check the major product version to see if it is SQL Server 2016 CTP 2 or greater
44IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '13%')
45 BEGIN
46 DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));
47 RAISERROR ('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.' , 18 , 16 , @ProductVersion);
48 END
49 ELSE
50 PRINT N'You have the correct major version of SQL Server for this diagnostic information script';
51
52
53-- Instance level queries *******************************
54
55-- SQL and OS Version information for current instance (Query 1) (Version Info)
56SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];
57------
58
59-- SQL Server 2016 RTM Branch Builds -- SQL Server 2016 SP1 Branch Builds -- SQL Server 2016 SP2 Branch Builds
60-- Build Description Release Date Build Description Release Date Build Description Release Date
61-- 13.0.200.172 CTP 2.0 5/26/2015
62-- 13.0.300.44 CTP 2.1 6/14/2015
63-- 13.0.407.1 CTP 2.2 7/28/2015
64-- 13.0.500.53 CTP 2.3 9/4/2015
65-- 13.0.600.65 CTP 2.4 9/30/2015
66-- 13.0.700.242 CTP 3.0 10/29/2015
67-- 13.0.900.73 CTP 3.2 12/12/2015
68-- 13.0.1000.276 CTP 3.3 1/27/2016
69-- 13.0.1100.288 RC0 3/2/2016
70-- 13.0.1200.242 RC1 3/18/2016
71-- 13.0.1300.275 RC2 3/28/2016
72-- 13.0.1400.361 RC3 4/11/2016
73-- 13.0.1601.5 RTM 6/1/2016
74-- 13.0.1708.0 RTM-GDR 6/12/2016
75-- 13.0.2149.0 RTM CU1 7/25/2016
76-- 13.0.2164.0 RTM CU2 9/22/2016
77-- 13.0.2186.0 RTM CU3 11/16/2016 ----> 13.0.4001.0 SP1 RTM 11/16/2016
78-- 13.0.2193.0 RTM CU4 1/18/2017 ----> 13.0.4411.0 SP1 CU1 1/18/2017
79-- 13.0.2197.0 RTM CU5 3/20/2017 ----> 13.0.4422.0 SP1 CU2 3/20/2017
80-- 13.0.2204.0 RTM CU6 5/15/2017 ----> 13.0.4435.0 SP1 CU3 5/15/2017
81-- 13.0.2210.0 RTM CU7 8/8/2017 ----> 13.0.4446.0 SP1 CU4 8/8/2017
82-- 13.0.2213.0 RTM CU8 9/18/2017 ----> 13.0.4451.0 SP1 CU5 9/18/2017
83-- 13.0.2216.0 RTM CU9 11/21/2017 ----> 13.0.4457.0 SP1 CU6 11/21/2017
84-- 13.0.4466.4 SP1 CU7 1/4/2018
85-- 13.0.4474.0 SP1 CU8 3/20/2018 ----> 13.0.5026.0 SP2 RTM 4/24/2018
86-- 13.0.4502.0 SP1 CU9 5/30/2018 ----> 13.0.5149.0 SP2 CU1 5/30/2018
87-- 13.0.4514.0 SP1 CU10 7/16/2018 ----> 13.0.5153.0 SP2 CU2 7/16/2018
88-- 13.0.4528.0 SP1 CU11 9/17/2018 ----> 13.0.5216.0 SP2 CU3 9/20/2018
89
90
91-- How to determine the version, edition and update level of SQL Server and its components
92-- https://bit.ly/2oAjKgW
93
94-- How to obtain the latest Service Pack for SQL Server 2016
95-- https://bit.ly/2egtfzK
96
97-- Microsoft SQL Server 2016 SP1 Latest Cumulative Update
98-- https://bit.ly/2jTwxWC
99
100-- SQL Server 2016 build versions
101-- https://bit.ly/2epkTDT
102
103-- Where to find information about the latest SQL Server builds
104-- https://bit.ly/2IGHbfY
105
106-- Performance and Stability Related Fixes in Post-SQL Server 2016 SP1 Builds
107-- https://bit.ly/2gr7k9L
108
109-- Performance and Stability Related Fixes in Post-SQL Server 2016 SP2 Builds
110-- https://bit.ly/2K3LoPf
111
112-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
113-- https://bit.ly/1RzYITz
114
115-- Update Center for Microsoft SQL Server
116-- https://bit.ly/2pZptuQ
117
118-- Download SQL Server Management Studio (SSMS)
119-- https://bit.ly/1OcupT9
120
121-- Download and install Azure Data Studio
122-- https://bit.ly/2vgke1A
123
124
125
126-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
127-- This query might take a few seconds depending on the size of your error log
128EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
129------
130
131-- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
132-- It can also help you confirm your SQL Server licensing model
133-- Be on the lookout for this message "using 40 logical processors based on SQL Server licensing"
134-- (when you have more than 40 logical cores) which means grandfathered Server/CAL licensing
135-- This query will return no results if your error log has been recycled since the instance was last started
136
137
138
139-- Get selected server properties (Query 3) (Server Properties)
140SELECT SERVERPROPERTY('MachineName') AS [MachineName],
141SERVERPROPERTY('ServerName') AS [ServerName],
142SERVERPROPERTY('InstanceName') AS [Instance],
143SERVERPROPERTY('IsClustered') AS [IsClustered],
144SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
145SERVERPROPERTY('Edition') AS [Edition],
146SERVERPROPERTY('ProductLevel') AS [ProductLevel], -- What servicing branch (RTM/SP/CU)
147SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel], -- Within a servicing branch, what CU# is applied
148SERVERPROPERTY('ProductVersion') AS [ProductVersion],
149SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
150SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
151SERVERPROPERTY('ProductBuild') AS [ProductBuild],
152SERVERPROPERTY('ProductBuildType') AS [ProductBuildType], -- Is this a GDR or OD hotfix (NULL if on a CU build)
153SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
154SERVERPROPERTY('ProcessID') AS [ProcessID],
155SERVERPROPERTY('Collation') AS [Collation],
156SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
157SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
158SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
159SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
160SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
161SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],
162SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],
163SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version],
164SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
165SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled], -- New for SQL Server 2016
166SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled]; -- New for SQL Server 2016
167------
168
169-- This gives you a lot of useful information about your instance of SQL Server,
170-- such as the ProcessID for SQL Server and your collation
171-- Note: Some columns will be NULL on older SQL Server builds
172
173-- SERVERPROPERTY (Transact-SQL)
174-- https://bit.ly/2eeaXeI
175
176
177
178-- Get instance-level configuration values for instance (Query 4) (Configuration Values)
179SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
180FROM sys.configurations WITH (NOLOCK)
181ORDER BY name OPTION (RECOMPILE);
182------
183
184-- Focus on these settings:
185-- automatic soft-NUMA disabled (should be 0 in most cases)
186-- backup checksum default (should be 1)
187-- backup compression default (should be 1 in most cases)
188-- clr enabled (only enable if it is needed)
189-- cost threshold for parallelism (depends on your workload)
190-- lightweight pooling (should be zero)
191-- max degree of parallelism (depends on your workload and hardware)
192-- max server memory (MB) (set to an appropriate value, not the default)
193-- optimize for ad hoc workloads (should be 1)
194-- priority boost (should be zero)
195-- remote admin connections (should be 1)
196
197-- New configuration options for SQL Server 2016
198-- allow polybase export (Allow INSERT into a Hadoop external table)
199-- automatic soft-NUMA disabled (Automatic soft-NUMA is enabled by default)
200-- external scripts enabled (Allows execution of external scripts, for R Services)
201-- hadoop connectivity (Configure SQL Server to connect to external Hadoop or Microsoft Azure storage blob data sources through PolyBase)
202-- polybase network encryption (Configure SQL Server to encrypt control and data channels when using PolyBase)
203-- remote data archive (Allow the use of the REMOTE_DATA_ARCHIVE data access for Stretch databases)
204
205-- SQLSweet16!, Episode 1: Backup Compression for TDE-enabled Databases
206-- https://bit.ly/28Rpb2x
207
208
209
210-- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)
211DBCC TRACESTATUS (-1);
212------
213
214-- If no global trace flags are enabled, no results will be returned.
215-- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.
216
217-- Common trace flags that should be enabled in most cases
218-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log
219-- https://bit.ly/2p6MTjS
220
221-- TF 6534 - Enables use of native code to improve performance with spatial data
222-- https://bit.ly/2HrQUpU
223
224-- The behavior of TF 1117, 1118 are enabled for tempdb in SQL Server 2016 by default
225-- SQL 2016 \96 It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
226-- https://bit.ly/2lbNWxK
227
228-- The behavior of TF 2371 is enabled by default in SQL Server 2016 and newer (in compat level 130 and higher)
229
230-- DBCC TRACEON - Trace Flags (Transact-SQL)
231-- https://bit.ly/2FuSvPg
232
233
234
235-- Returns status of instant file initialization (Query 6) (IFI Status)
236EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';
237------
238
239-- Lets you determine whether Instant File Initialization (IFI) is enabled for the instance
240-- This should be enabled in the vast majority of cases
241-- SQL Server 2016 lets you enable this during the SQL server installation process
242
243-- Database Instant File Initialization
244-- https://bit.ly/2nTX74y
245
246-- Misconceptions around instant file initialization
247-- https://bit.ly/2oBSKgZ
248
249
250
251-- SQL Server Process Address space info (Query 7) (Process Memory)
252-- (shows whether locked pages is enabled, among other things)
253SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
254 locked_page_allocations_kb/1024 AS [SQL Server Locked Pages Allocation (MB)],
255 large_page_allocations_kb/1024 AS [SQL Server Large Pages Allocation (MB)],
256 page_fault_count, memory_utilization_percentage, available_commit_limit_kb,
257 process_physical_memory_low, process_virtual_memory_low
258FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
259------
260
261-- You want to see 0 for process_physical_memory_low
262-- You want to see 0 for process_virtual_memory_low
263-- This indicates that you are not under internal memory pressure
264-- If locked_page_allocations_kb > 0, then LPIM is enabled
265
266-- How to enable the "locked pages" feature in SQL Server 2012
267-- https://bit.ly/2F5UjOA
268
269-- Memory Management Architecture Guide
270-- https://bit.ly/2JKkadC
271
272
273
274-- SQL Server Services information (Query 8) (SQL Server Services Info)
275SELECT servicename, process_id, startup_type_desc, status_desc,
276last_startup_time, service_account, is_clustered, cluster_nodename, [filename],
277instant_file_initialization_enabled -- New in SQL Server 2016 SP1
278FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
279------
280
281-- Tells you the account being used for the SQL Server Service and the SQL Agent Service
282-- Shows the process_id, when they were last started, and their current status
283-- Also shows whether you are running on a failover cluster instance, and what node you are running on
284-- Also shows whether IFI is enabled
285
286-- sys.dm_server_services (Transact-SQL)
287-- https://bit.ly/2oKa1Un
288
289
290-- Last backup information by database (Query 9) (Last Backup By Database)
291SELECT ISNULL(d.[name], bs.[database_name]) AS [Database], d.recovery_model_desc AS [Recovery Model],
292 d.log_reuse_wait_desc AS [Log Reuse Wait Desc],
293 MAX(CASE WHEN [type] = 'D' THEN bs.backup_finish_date ELSE NULL END) AS [Last Full Backup],
294 MAX(CASE WHEN [type] = 'I' THEN bs.backup_finish_date ELSE NULL END) AS [Last Differential Backup],
295 MAX(CASE WHEN [type] = 'L' THEN bs.backup_finish_date ELSE NULL END) AS [Last Log Backup]
296FROM sys.databases AS d WITH (NOLOCK)
297LEFT OUTER JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
298ON bs.[database_name] = d.[name]
299AND bs.backup_finish_date > GETDATE()- 30
300WHERE d.name <> N'tempdb'
301GROUP BY ISNULL(d.[name], bs.[database_name]), d.recovery_model_desc, d.log_reuse_wait_desc, d.[name]
302ORDER BY d.recovery_model_desc, d.[name] OPTION (RECOMPILE);
303------
304
305-- This helps you spot runaway transaction logs and other issues with your backup schedule
306
307
308-- Get SQL Server Agent jobs and Category information (Query 10) (SQL Server Agent Jobs)
309SELECT sj.name AS [Job Name], sj.[description] AS [Job Description], SUSER_SNAME(sj.owner_sid) AS [Job Owner],
310sj.date_created AS [Date Created], sj.[enabled] AS [Job Enabled],
311sj.notify_email_operator_id, sj.notify_level_email, sc.name AS [CategoryName],
312s.[enabled] AS [Sched Enabled], js.next_run_date, js.next_run_time
313FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)
314INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)
315ON sj.category_id = sc.category_id
316LEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)
317ON sj.job_id = js.job_id
318LEFT OUTER JOIN msdb.dbo.sysschedules AS s WITH (NOLOCK)
319ON js.schedule_id = s.schedule_id
320ORDER BY sj.name OPTION (RECOMPILE);
321------
322
323-- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured
324-- Look for Agent jobs that are not owned by sa
325-- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)
326-- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)
327--
328-- MSDN sysjobs documentation
329-- https://bit.ly/2paDEOP
330
331-- SQL Server Maintenance Solution
332-- https://bit.ly/1pgchQu
333
334
335-- Get SQL Server Agent Alert Information (Query 11) (SQL Server Agent Alerts)
336SELECT name, event_source, message_id, severity, [enabled], has_notification,
337 delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time
338FROM msdb.dbo.sysalerts WITH (NOLOCK)
339ORDER BY name OPTION (RECOMPILE);
340------
341
342-- Gives you some basic information about your SQL Server Agent Alerts
343-- (which are different from SQL Server Agent jobs)
344-- Read more about Agent Alerts here: https://bit.ly/2Giz0Xf
345
346
347
348-- Windows information (Query 12) (Windows Info)
349SELECT windows_release, windows_service_pack_level,
350 windows_sku, os_language_version
351FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);
352------
353
354-- Gives you major OS version, Service Pack, Edition, and language info for the operating system
355-- 10.0 is either Windows 10 or Windows Server 2016
356-- 6.3 is either Windows 8.1 or Windows Server 2012 R2
357-- 6.2 is either Windows 8 or Windows Server 2012
358
359
360-- Windows SKU codes
361-- 4 is Enterprise Edition
362-- 7 is Standard Server Edition
363-- 8 is Datacenter Server Edition
364-- 10 is Enterprise Server Edition
365-- 48 is Professional Edition
366-- 161 is Pro for Workstations
367
368-- 1033 for os_language_version is US-English
369
370-- SQL Server 2016 requires Windows Server 2012 or newer
371
372-- Quick-Start Installation of SQL Server 2016
373-- https://bit.ly/2qtxQ3G
374
375-- Hardware and Software Requirements for Installing SQL Server 2016
376-- https://bit.ly/2JJIUTl
377
378-- Using SQL Server in Windows 8 and later versions of Windows operating system
379-- https://bit.ly/2F7Ax0P
380
381
382-- SQL Server NUMA Node information (Query 13) (SQL Server NUMA Info)
383SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
384 idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state
385FROM sys.dm_os_nodes WITH (NOLOCK)
386WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
387------
388
389-- Gives you some useful information about the composition and relative load on your NUMA nodes
390-- You want to see an equal number of schedulers on each NUMA node
391-- Watch out if SQL Server 2016 Standard Edition has been installed
392-- on a physical or virtual machine with more than four sockets or more than 24 physical cores
393
394-- sys.dm_os_nodes (Transact-SQL)
395-- https://bit.ly/2pn5Mw8
396
397-- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes
398-- https://bit.ly/2vfC4Rq
399
400
401
402-- Good basic information about OS memory amounts and state (Query 14) (System Memory)
403SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],
404 available_physical_memory_kb/1024 AS [Available Memory (MB)],
405 total_page_file_kb/1024 AS [Total Page File (MB)],
406 available_page_file_kb/1024 AS [Available Page File (MB)],
407 system_cache_kb/1024 AS [System Cache (MB)],
408 system_memory_state_desc AS [System Memory State]
409FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
410------
411
412-- You want to see "Available physical memory is high" for System Memory State
413-- This indicates that you are not under external memory pressure
414
415-- Possible System Memory State values:
416-- Available physical memory is high
417-- Physical memory usage is steady
418-- Available physical memory is low
419-- Available physical memory is running low
420-- Physical memory state is transitioning
421
422-- sys.dm_os_sys_memory (Transact-SQL)
423-- https://bit.ly/2pcV0xq
424
425
426
427-- You can skip the next two queries if you know you don't have a clustered instance
428
429
430-- Get information about your cluster nodes and their status (Query 15) (Cluster Node Properties)
431-- (if your database server is in a failover cluster)
432SELECT NodeName, status_description, is_current_owner
433FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
434------
435
436-- Knowing which node owns the cluster resources is critical
437-- Especially when you are installing Windows or SQL Server updates
438-- You will see no results if your instance is not clustered
439
440-- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
441-- https://bit.ly/1z5BfCw
442
443
444-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 16) (AlwaysOn AG Cluster)
445SELECT cluster_name, quorum_type_desc, quorum_state_desc
446FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
447------
448
449-- You will see no results if your instance is not using AlwaysOn AGs
450
451
452-- Good overview of AG health and status (Query 17) (AlwaysOn AG Status)
453SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name],
454 drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,
455 drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,
456 drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn,
457 drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size,
458 drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate,
459 drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc
460FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
461INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
462ON drs.group_id = adc.group_id
463AND drs.group_database_id = adc.group_database_id
464INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
465ON ag.group_id = drs.group_id
466INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
467ON drs.group_id = ar.group_id
468AND drs.replica_id = ar.replica_id
469ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);
470
471-- You will see no results if your instance is not using AlwaysOn AGs
472
473-- SQL Server 2016 \96 It Just Runs Faster: Always On Availability Groups Turbocharged
474-- https://bit.ly/2dn1H6r
475
476
477-- Hardware information from SQL Server 2016 (Query 18) (Hardware Info)
478SELECT cpu_count AS [Logical CPU Count], scheduler_count,
479 hyperthread_ratio AS [Hyperthread Ratio],
480 cpu_count/hyperthread_ratio AS [Physical CPU Count],
481 physical_memory_kb/1024 AS [Physical Memory (MB)],
482 committed_kb/1024 AS [Committed Memory (MB)],
483 committed_target_kb/1024 AS [Committed Target Memory (MB)],
484 max_workers_count AS [Max Workers Count],
485 affinity_type_desc AS [Affinity Type],
486 sqlserver_start_time AS [SQL Server Start Time],
487 virtual_machine_type_desc AS [Virtual Machine Type],
488 softnuma_configuration_desc AS [Soft NUMA Configuration],
489 sql_memory_model_desc -- New in SQL Server 2016
490FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
491------
492
493-- Gives you some good basic hardware information about your database server
494-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM
495-- It merely indicates that you have a hypervisor running on your host
496
497-- sys.dm_os_sys_info (Transact-SQL)
498-- https://bit.ly/2pczOYs
499
500-- Soft NUMA configuration was a new column for SQL Server 2016
501-- OFF = Soft-NUMA feature is OFF
502-- ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA
503-- MANUAL = Manually configured soft-NUMA
504
505-- Configure SQL Server to Use Soft-NUMA (SQL Server)
506-- https://bit.ly/2HTpKJt
507
508-- sql_memory_model_desc values (Added in SQL Server 2016 SP1)
509-- CONVENTIONAL
510-- LOCK_PAGES
511-- LARGE_PAGES
512
513
514-- Get System Manufacturer and model number from SQL Server Error log (Query 19) (System Manufacturer)
515EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
516------
517
518-- This can help you determine the capabilities and capacities of your database server
519-- Can also be used to confirm if you are running in a VM
520-- This query might take a few seconds if you have not recycled your error log recently
521-- This query will return no results if your error log has been recycled since the instance was started
522
523
524-- Get pvscsi info from Windows Registry (Query 20) (PVSCSI Driver Parameters)
525EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\services\pvscsi\Parameters\Device', N'DriverParameter';
526------
527
528-- This is valid for VMware VMs
529-- Recommended value for intensive I/O patterns from VMware is: RequestRingPages=32,MaxQueueDepth=254
530-- https://kb.vmware.com/s/article/2053145
531
532
533
534-- Get BIOS date from Windows Registry (Query 21) (BIOS Date)
535EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';
536------
537
538-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware
539-- Not as useful for virtualization
540
541
542-- Get processor description from Windows Registry (Query 22) (Processor Description)
543EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
544------
545
546-- Gives you the model number and rated clock speed of your processor(s)
547-- Your processors may be running at less than the rated clock speed due
548-- to the Windows Power Plan or hardware power management
549
550-- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information
551-- https://bit.ly/QhR6xF
552
553-- You can learn more about processor selection for SQL Server by following this link
554-- https://bit.ly/2F3aVlP
555
556
557
558-- See if buffer pool extension (BPE) is enabled (Query 23) (BPE Configuration)
559SELECT [path], state_description, current_size_in_kb,
560CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
561FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);
562------
563
564-- BPE is available in both Standard Edition and Enterprise Edition
565-- It is a more interesting feature for Standard Edition
566
567-- Buffer Pool Extension to SSDs in SQL Server 2014
568-- https://bit.ly/1bm08m8
569
570-- Buffer Pool Extension
571-- https://bit.ly/2oBuieO
572
573
574
575-- Look at buffer descriptors to see BPE usage by database (Query 24) (BPE Usage)
576SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
577CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
578AVG(read_microsec) AS [Avg Read Time (microseconds)]
579FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
580WHERE database_id <> 32767
581AND is_in_bpool_extension = 1
582GROUP BY DB_NAME(database_id)
583ORDER BY [Buffer size(MB)] DESC OPTION (RECOMPILE);
584------
585
586-- You will see no results if BPE is not enabled or if there is no BPE usage
587
588
589-- Get information on location, time and size of any memory dumps from SQL Server (Query 25) (Memory Dump Info)
590SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]
591FROM sys.dm_server_memory_dumps WITH (NOLOCK)
592ORDER BY creation_time DESC OPTION (RECOMPILE);
593------
594
595-- This will not return any rows if you have
596-- not had any memory dumps (which is a good thing)
597
598-- sys.dm_server_memory_dumps (Transact-SQL)
599-- https://bit.ly/2elwWll
600
601
602
603-- Look at Suspect Pages table (Query 26) (Suspect Pages)
604SELECT DB_NAME(database_id) AS [Database Name], [file_id], page_id,
605 event_type, error_count, last_update_date
606FROM msdb.dbo.suspect_pages WITH (NOLOCK)
607ORDER BY database_id OPTION (RECOMPILE);
608------
609
610-- event_type value descriptions
611-- 1 = 823 error caused by an operating system CRC error
612-- or 824 error other than a bad checksum or a torn page (for example, a bad page ID)
613-- 2 = Bad checksum
614-- 3 = Torn page
615-- 4 = Restored (The page was restored after it was marked bad)
616-- 5 = Repaired (DBCC repaired the page)
617-- 7 = Deallocated by DBCC
618
619-- Ideally, this query returns no results. The table is limited to 1000 rows.
620-- If you do get results here, you should do further investigation to determine the root cause
621
622-- Manage the suspect_pages Table
623-- https://bit.ly/2Fvr1c9
624
625
626-- Get number of data files in tempdb database (Query 27) (TempDB Data Files)
627EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has';
628------
629
630-- Get the number of data files in the tempdb database
631-- 4-8 data files that are all the same size is a good starting point
632-- This query will return no results if your error log has been recycled since the instance was last started
633
634
635-- File names and paths for all user and system databases on instance (Query 28) (Database Filenames and Paths)
636SELECT DB_NAME([database_id]) AS [Database Name],
637 [file_id], [name], physical_name, [type_desc], state_desc,
638 is_percent_growth, growth,
639 CONVERT(bigint, growth/128.0) AS [Growth in MB],
640 CONVERT(bigint, size/128.0) AS [Total Size in MB]
641FROM sys.master_files WITH (NOLOCK)
642ORDER BY DB_NAME([database_id]), [file_id] OPTION (RECOMPILE);
643------
644
645-- Things to look at:
646-- Are data files and log files on different drives?
647-- Is everything on the C: drive?
648-- Is tempdb on dedicated drives?
649-- Is there only one tempdb data file?
650-- Are all of the tempdb data files the same size?
651-- Are there multiple data files for user databases?
652-- Is percent growth enabled for any files (which is bad)?
653
654
655-- Volume info for all LUNS that have database files on the current instance (Query 29) (Volume Info)
656SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name,
657CONVERT(DECIMAL(18,2), vs.total_bytes/1073741824.0) AS [Total Size (GB)],
658CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],
659CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],
660vs.supports_compression, vs.is_compressed,
661vs.supports_sparse_files, vs.supports_alternate_streams
662FROM sys.master_files AS f WITH (NOLOCK)
663CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
664ORDER BY vs.volume_mount_point OPTION (RECOMPILE);
665------
666
667-- Shows you the total and free space on the LUNs where you have database files
668-- Being low on free space can negatively affect performance
669
670-- sys.dm_os_volume_stats (Transact-SQL)
671-- https://bit.ly/2oBPNNr
672
673
674
675-- Drive level latency information (Query 30) (Drive Level Latency)
676-- Based on code from Jimmy May
677SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point],
678 CASE
679 WHEN num_of_reads = 0 THEN 0
680 ELSE (io_stall_read_ms/num_of_reads)
681 END AS [Read Latency],
682 CASE
683 WHEN num_of_writes = 0 THEN 0
684 ELSE (io_stall_write_ms/num_of_writes)
685 END AS [Write Latency],
686 CASE
687 WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
688 ELSE (io_stall/(num_of_reads + num_of_writes))
689 END AS [Overall Latency],
690 CASE
691 WHEN num_of_reads = 0 THEN 0
692 ELSE (num_of_bytes_read/num_of_reads)
693 END AS [Avg Bytes/Read],
694 CASE
695 WHEN num_of_writes = 0 THEN 0
696 ELSE (num_of_bytes_written/num_of_writes)
697 END AS [Avg Bytes/Write],
698 CASE
699 WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
700 ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes))
701 END AS [Avg Bytes/Transfer]
702FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
703 SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
704 SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
705 SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point
706 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
707 INNER JOIN sys.master_files AS mf WITH (NOLOCK)
708 ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
709 CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs
710 GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab
711ORDER BY [Overall Latency] OPTION (RECOMPILE);
712------
713
714-- Shows you the drive-level latency for reads and writes, in milliseconds
715-- Latency above 30-40ms is usually a problem
716-- These latency numbers include all file activity against all SQL Server
717-- database files on each drive since SQL Server was last started
718
719
720-- Calculates average stalls per read, per write, and per total input/output for each database file (Query 31) (IO Latency by File)
721SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_latency_ms],
722CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_latency_ms],
723CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_latency_ms],
724CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads,
725fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],
726io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)]
727FROM sys.dm_io_virtual_file_stats(null,null) AS fs
728INNER JOIN sys.master_files AS mf WITH (NOLOCK)
729ON fs.database_id = mf.database_id
730AND fs.[file_id] = mf.[file_id]
731ORDER BY avg_io_latency_ms DESC OPTION (RECOMPILE);
732------
733
734-- Helps determine which database files on the entire instance have the most I/O bottlenecks
735-- This can help you decide whether certain LUNs are overloaded and whether you might
736-- want to move some files to a different location or perhaps improve your I/O performance
737-- These latency numbers include all file activity against each SQL Server
738-- database file since SQL Server was last started
739
740
741-- Look for I/O requests taking longer than 15 seconds in the six most recent SQL Server Error Logs (Query 32) (IO Warnings)
742CREATE TABLE #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));
743
744 INSERT INTO #IOWarningResults
745 EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';
746
747 INSERT INTO #IOWarningResults
748 EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';
749
750 INSERT INTO #IOWarningResults
751 EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';
752
753 INSERT INTO #IOWarningResults
754 EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';
755
756 INSERT INTO #IOWarningResults
757 EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';
758
759 INSERT INTO #IOWarningResults
760 EXEC xp_readerrorlog 5, 1, N'taking longer than 15 seconds';
761
762SELECT LogDate, ProcessInfo, LogText
763FROM #IOWarningResults
764ORDER BY LogDate DESC;
765
766DROP TABLE #IOWarningResults;
767------
768
769-- Finding 15 second I/O warnings in the SQL Server Error Log is useful evidence of
770-- poor I/O performance (which might have many different causes)
771-- Look to see if you see any patterns in the results (same files, same drives, same time of day, etc.)
772
773-- Diagnostics in SQL Server help detect stalled and stuck I/O operations
774-- https://bit.ly/2qtaw73
775
776
777-- Resource Governor Resource Pool information (Query 33) (RG Resource Pools)
778SELECT pool_id, [Name], statistics_start_time,
779 min_memory_percent, max_memory_percent,
780 max_memory_kb/1024 AS [max_memory_mb],
781 used_memory_kb/1024 AS [used_memory_mb],
782 target_memory_kb/1024 AS [target_memory_mb],
783 min_iops_per_volume, max_iops_per_volume
784FROM sys.dm_resource_governor_resource_pools WITH (NOLOCK)
785OPTION (RECOMPILE);
786------
787
788-- sys.dm_resource_governor_resource_pools (Transact-SQL)
789-- https://bit.ly/2MVU0Vy
790
791
792
793-- Recovery model, log reuse wait description, log file size, log usage size (Query 34) (Database Properties)
794-- and compatibility level for all databases on instance
795SELECT db.[name] AS [Database Name], SUSER_SNAME(db.owner_sid) AS [Database Owner], db.recovery_model_desc AS [Recovery Model],
796db.state_desc, db.containment_desc, db.log_reuse_wait_desc AS [Log Reuse Wait Description],
797CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)],
798CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
799db.[compatibility_level] AS [DB Compatibility Level],
800db.is_mixed_page_allocation_on, db.page_verify_option_desc AS [Page Verify Option],
801db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced,
802db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on, db.is_auto_close_on, db.is_auto_shrink_on,
803db.target_recovery_time_in_seconds, db.is_cdc_enabled, db.is_published, db.is_distributor, db.is_encrypted,
804db.group_database_id, db.replica_id,db.is_memory_optimized_elevate_to_snapshot_on,
805db.delayed_durability_desc, db.is_auto_create_stats_incremental_on,
806db.is_query_store_on, db.is_sync_with_backup,
807db.is_supplemental_logging_enabled, db.is_remote_data_archive_enabled,
808db.is_encrypted, de.encryption_state, de.percent_complete, de.key_algorithm, de.key_length
809FROM sys.databases AS db WITH (NOLOCK)
810INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
811ON db.name = lu.instance_name
812INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
813ON db.name = ls.instance_name
814LEFT OUTER JOIN sys.dm_database_encryption_keys AS de WITH (NOLOCK)
815ON db.database_id = de.database_id
816WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
817AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
818AND ls.cntr_value > 0
819ORDER BY db.[name] OPTION (RECOMPILE);
820------
821
822-- Things to look at:
823-- How many databases are on the instance?
824-- What recovery models are they using?
825-- What is the log reuse wait description?
826-- How full are the transaction logs?
827-- What compatibility level are the databases on?
828-- What is the Page Verify Option? (should be CHECKSUM)
829-- Is Auto Update Statistics Asynchronously enabled?
830-- Is Delayed Durability enabled
831-- Make sure auto_shrink and auto_close are not enabled!
832
833-- is_mixed_page_allocation_on is a new property for SQL Server 2016. Equivalent to TF 1118 for a user database
834-- SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases
835-- https://bit.ly/2evRZSR
836
837-- A non-zero value for target_recovery_time_in_seconds means that indirect checkpoint is enabled
838-- If the setting has a zero value it indicates that automatic checkpoint is enabled
839
840-- Changes in SQL Server 2016 Checkpoint Behavior
841-- https://bit.ly/2pdggk3
842
843
844-- Missing Indexes for all databases by Index Advantage (Query 35) (Missing Indexes All Databases)
845SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],
846FORMAT(migs.last_user_seek, 'yyyy-MM-dd HH:mm:ss') AS [last_user_seek],
847mid.[statement] AS [Database.Schema.Table],
848COUNT(1) OVER(PARTITION BY mid.[statement]) AS [missing_indexes_for_table],
849COUNT(1) OVER(PARTITION BY mid.[statement], equality_columns) AS [similar_missing_indexes_for_table],
850mid.equality_columns, mid.inequality_columns, mid.included_columns,
851migs.unique_compiles, migs.user_seeks,
852CONVERT(decimal(18,2), migs.avg_total_user_cost) AS [avg_total_user_cost], migs.avg_user_impact
853FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
854INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
855ON migs.group_handle = mig.index_group_handle
856INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
857ON mig.index_handle = mid.index_handle
858ORDER BY index_advantage DESC OPTION (RECOMPILE);
859------
860
861-- Getting missing index information for all of the databases on the instance is very useful
862-- Look at last user seek time, number of user seeks to help determine source and importance
863-- Also look at avg_user_impact and avg_total_user_cost to help determine importance
864-- SQL Server is overly eager to add included columns, so beware
865-- Do not just blindly add indexes that show up from this query!!!
866
867-- SQL Server Index Design Guide
868-- https://bit.ly/2qtZr4N
869
870
871
872-- Get VLF Counts for all databases on the instance (Query 36) (VLF Counts)
873-- (adapted from Michelle Ufford)
874CREATE TABLE #VLFInfo (RecoveryUnitID int, FileID int,
875 FileSize bigint, StartOffset bigint,
876 FSeqNo bigint, [Status] bigint,
877 Parity bigint, CreateLSN numeric(38));
878
879CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);
880
881EXEC sp_MSforeachdb N'Use [?];
882
883 INSERT INTO #VLFInfo
884 EXEC sp_executesql N''DBCC LOGINFO([?])'';
885
886 INSERT INTO #VLFCountResults
887 SELECT DB_NAME(), COUNT(*)
888 FROM #VLFInfo;
889
890 TRUNCATE TABLE #VLFInfo;'
891
892SELECT DatabaseName, VLFCount
893FROM #VLFCountResults
894ORDER BY VLFCount DESC;
895
896DROP TABLE #VLFInfo;
897DROP TABLE #VLFCountResults;
898------
899
900-- High VLF counts can affect write performance to the log file
901-- and they can make full database restores and crash recovery take much longer
902-- Try to keep your VLF counts under 200 in most cases (depending on log file size)
903
904-- Important change to VLF creation algorithm in SQL Server 2014
905-- https://bit.ly/2Hsjbg4
906
907-- SQL Server Transaction Log Architecture and Management Guide
908-- https://bit.ly/2JjmQRZ
909
910
911
912
913-- Get CPU utilization by database (Query 37) (CPU Usage by Database)
914WITH DB_CPU_Stats
915AS
916(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
917 FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
918 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
919 FROM sys.dm_exec_plan_attributes(qs.plan_handle)
920 WHERE attribute = N'dbid') AS pa
921 GROUP BY DatabaseID)
922SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
923 [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
924 CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
925FROM DB_CPU_Stats
926WHERE DatabaseID <> 32767 -- ResourceDB
927ORDER BY [CPU Rank] OPTION (RECOMPILE);
928------
929
930-- Helps determine which database is using the most CPU resources on the instance
931
932
933-- Get I/O utilization by database (Query 38) (IO Usage By Database)
934WITH Aggregate_IO_Statistics
935AS (SELECT DB_NAME(database_id) AS [Database Name],
936 CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS [ioTotalMB],
937 CAST(SUM(num_of_bytes_read ) / 1048576 AS DECIMAL(12, 2)) AS [ioReadMB],
938 CAST(SUM(num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS [ioWriteMB]
939 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
940 GROUP BY database_id)
941SELECT ROW_NUMBER() OVER (ORDER BY ioTotalMB DESC) AS [I/O Rank],
942 [Database Name], ioTotalMB AS [Total I/O (MB)],
943 CAST(ioTotalMB / SUM(ioTotalMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Total I/O %],
944 ioReadMB AS [Read I/O (MB)],
945 CAST(ioReadMB / SUM(ioReadMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Read I/O %],
946 ioWriteMB AS [Write I/O (MB)],
947 CAST(ioWriteMB / SUM(ioWriteMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Write I/O %]
948FROM Aggregate_IO_Statistics
949ORDER BY [I/O Rank] OPTION (RECOMPILE);
950------
951
952-- Helps determine which database is using the most I/O resources on the instance
953-- These numbers are cumulative since the last service restart
954-- They include all I/O activity, not just the nominal I/O workload
955
956
957-- Get total buffer usage by database for current instance (Query 39) (Total Buffer Usage by Database)
958-- This make take some time to run on a busy instance
959WITH AggregateBufferPoolUsage
960AS
961(SELECT DB_NAME(database_id) AS [Database Name],
962CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize]
963FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
964WHERE database_id <> 32767 -- ResourceDB
965GROUP BY DB_NAME(database_id))
966SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)],
967 CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
968FROM AggregateBufferPoolUsage
969ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);
970------
971
972-- Tells you how much memory (in the buffer pool)
973-- is being used by each database on the instance
974
975
976-- Clear Wait Stats with this command
977-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
978
979-- Isolate top waits for server instance since last restart or wait statistics clear (Query 40) (Top Waits)
980WITH [Waits]
981AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
982 (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
983 signal_wait_time_ms / 1000.0 AS [SignalS],
984 waiting_tasks_count AS [WaitCount],
985 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
986 ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
987 FROM sys.dm_os_wait_stats WITH (NOLOCK)
988 WHERE [wait_type] NOT IN (
989 N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
990 N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
991 N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
992 N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
993 N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
994 N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
995 N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
996 N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
997 N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',
998 N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
999 N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST',
1000 N'PARALLEL_REDO_WORKER_SYNC', N'PARALLEL_REDO_WORKER_WAIT_WORK',
1001 N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
1002 N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
1003 N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
1004 N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
1005 N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',
1006 N'PREEMPTIVE_OS_WRITEFILE',
1007 N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',
1008 N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',
1009 N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',
1010 N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
1011 N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
1012 N'QDS_ASYNC_QUEUE',
1013 N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
1014 N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
1015 N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
1016 N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
1017 N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
1018 N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
1019 N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
1020 N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',
1021 N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',
1022 N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
1023 AND waiting_tasks_count > 0)
1024SELECT
1025 MAX (W1.wait_type) AS [WaitType],
1026 CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
1027 CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
1028 CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
1029 CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
1030 CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
1031 CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
1032 CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
1033 MAX (W1.WaitCount) AS [Wait Count],
1034 CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
1035FROM Waits AS W1
1036INNER JOIN Waits AS W2
1037ON W2.RowNum <= W1.RowNum
1038GROUP BY W1.RowNum, W1.wait_type
1039HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
1040OPTION (RECOMPILE);
1041------
1042
1043-- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure
1044
1045-- SQL Server Wait Types Library (Paul Randal)
1046-- https://bit.ly/2ePzYO2
1047
1048-- The SQL Server Wait Type Repository
1049-- https://bit.ly/1afzfjC
1050
1051-- Wait statistics, or please tell me where it hurts
1052-- https://bit.ly/2wsQHQE
1053
1054-- SQL Server 2005 Performance Tuning using the Waits and Queues
1055-- https://bit.ly/1o2NFoF
1056
1057-- sys.dm_os_wait_stats (Transact-SQL)
1058-- https://bit.ly/2Hjq9Yl
1059
1060
1061
1062-- Get a count of SQL connections by IP address (Query 41) (Connection Counts by IP Address)
1063SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name,
1064COUNT(ec.session_id) AS [connection count]
1065FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
1066INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
1067ON es.session_id = ec.session_id
1068GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name
1069ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);
1070------
1071
1072-- This helps you figure where your database load is coming from
1073-- and verifies connectivity from other machines
1074
1075-- Solving Connectivity errors to SQL Server
1076-- https://bit.ly/2EgzoD0
1077
1078
1079
1080-- Get Average Task Counts (run multiple times) (Query 42) (Avg Task Counts)
1081SELECT AVG(current_tasks_count) AS [Avg Task Count],
1082AVG(work_queue_count) AS [Avg Work Queue Count],
1083AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
1084AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
1085FROM sys.dm_os_schedulers WITH (NOLOCK)
1086WHERE scheduler_id < 255 OPTION (RECOMPILE);
1087------
1088
1089-- Sustained values above 10 suggest further investigation in that area
1090-- High Avg Task Counts are often caused by blocking/deadlocking or other resource contention
1091
1092-- Sustained values above 1 suggest further investigation in that area
1093-- High Avg Runnable Task Counts are a good sign of CPU pressure
1094-- High Avg Pending DiskIO Counts are a sign of disk pressure
1095
1096-- How to Do Some Very Basic SQL Server Monitoring
1097-- https://bit.ly/2q3Btgt
1098
1099
1100
1101-- Detect blocking (run multiple times) (Query 43) (Detect Blocking)
1102SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database],
1103t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], -- lock requested
1104t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time], -- spid of waiter
1105(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK) -- get sql for waiter
1106CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
1107WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
1108(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,
1109 (CASE WHEN r.statement_end_offset = -1
1110 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
1111 ELSE r.statement_end_offset END - r.statement_start_offset)/2)
1112FROM sys.dm_exec_requests AS r WITH (NOLOCK)
1113CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
1114WHERE r.session_id = t1.request_session_id) AS [waiter_stmt], -- statement blocked
1115t2.blocking_session_id AS [blocker sid], -- spid of blocker
1116(SELECT [text] FROM sys.sysprocesses AS p -- get sql for blocker
1117CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
1118WHERE p.spid = t2.blocking_session_id) AS [blocker_batch]
1119FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
1120INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
1121ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);
1122------
1123
1124-- Helps troubleshoot blocking and deadlocking issues
1125-- The results will change from second to second on a busy system
1126-- You should run this query multiple times when you see signs of blocking
1127
1128
1129
1130-- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 44) (CPU Utilization History)
1131DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
1132
1133SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
1134 SystemIdle AS [System Idle Process],
1135 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
1136 DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
1137FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
1138 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
1139 AS [SystemIdle],
1140 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
1141 AS [SQLProcessUtilization], [timestamp]
1142 FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]
1143 FROM sys.dm_os_ring_buffers WITH (NOLOCK)
1144 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
1145 AND record LIKE N'%<SystemHealth>%') AS x) AS y
1146ORDER BY record_id DESC OPTION (RECOMPILE);
1147------
1148
1149-- Look at the trend over the entire period
1150-- Also look at high sustained 'Other Process' CPU Utilization values
1151-- Note: This query sometimes gives inaccurate results (negative values)
1152-- on high core count (> 64 cores) systems
1153
1154
1155-- Get top total worker time queries for entire instance (Query 45) (Top Worker Time Queries)
1156SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
1157REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],
1158qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
1159qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
1160qs.max_worker_time AS [Max Worker Time],
1161qs.min_elapsed_time AS [Min Elapsed Time],
1162qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
1163qs.max_elapsed_time AS [Max Elapsed Time],
1164qs.min_logical_reads AS [Min Logical Reads],
1165qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
1166qs.max_logical_reads AS [Max Logical Reads],
1167qs.execution_count AS [Execution Count],
1168CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1169qs.creation_time AS [Creation Time]
1170--,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
1171FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
1172CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
1173CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
1174ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
1175------
1176
1177
1178-- Helps you find the most expensive queries from a CPU perspective across the entire instance
1179-- Can also help track down parameter sniffing issues
1180
1181
1182
1183-- Page Life Expectancy (PLE) value for each NUMA node in current instance (Query 46) (PLE by NUMA Node)
1184SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], instance_name, cntr_value AS [Page Life Expectancy]
1185FROM sys.dm_os_performance_counters WITH (NOLOCK)
1186WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
1187AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
1188------
1189
1190-- PLE is a good measurement of internal memory pressure
1191-- Higher PLE is better. Watch the trend over time, not the absolute value
1192-- This will only return one row for non-NUMA systems
1193
1194-- Page Life Expectancy isn\92t what you think\85
1195-- https://bit.ly/2EgynLa
1196
1197
1198-- Memory Grants Pending value for current instance (Query 47) (Memory Grants Pending)
1199SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], cntr_value AS [Memory Grants Pending]
1200FROM sys.dm_os_performance_counters WITH (NOLOCK)
1201WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
1202AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
1203------
1204
1205-- Run multiple times, and run periodically if you suspect you are under memory pressure
1206-- Memory Grants Pending above zero for a sustained period is a very strong indicator of internal memory pressure
1207
1208
1209-- Memory Clerk Usage for instance (Query 48) (Memory Clerk Usage)
1210-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
1211SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
1212 CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
1213FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
1214GROUP BY mc.[type]
1215ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
1216------
1217
1218-- MEMORYCLERK_SQLBUFFERPOOL was new for SQL Server 2012. It should be your highest consumer of memory
1219
1220-- CACHESTORE_SQLCP SQL Plans
1221-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
1222-- Watch out for high values for CACHESTORE_SQLCP
1223-- Enabling 'optimize for ad hoc workloads' at the instance level can help reduce this
1224-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
1225
1226-- CACHESTORE_OBJCP Object Plans
1227-- These are compiled plans for stored procedures, functions and triggers
1228
1229
1230
1231-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache (Query 49) (Ad hoc Queries)
1232SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], t.[text] AS [Query Text],
1233cp.objtype AS [Object Type], cp.cacheobjtype AS [Cache Object Type],
1234cp.size_in_bytes/1024 AS [Plan Size in KB]
1235FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
1236CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
1237WHERE cp.cacheobjtype = N'Compiled Plan'
1238AND cp.objtype IN (N'Adhoc', N'Prepared')
1239AND cp.usecounts = 1
1240ORDER BY cp.size_in_bytes DESC, DB_NAME(t.[dbid]) OPTION (RECOMPILE);
1241------
1242
1243-- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache
1244-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)
1245-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
1246-- Enabling forced parameterization for the database can help, but test first!
1247
1248-- Plan cache, adhoc workloads and clearing the single-use plan cache bloat
1249-- https://bit.ly/2EfYOkl
1250
1251
1252-- Get top total logical reads queries for entire instance (Query 50) (Top Logical Reads Queries)
1253SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
1254REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],
1255qs.total_logical_reads AS [Total Logical Reads],
1256qs.min_logical_reads AS [Min Logical Reads],
1257qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
1258qs.max_logical_reads AS [Max Logical Reads],
1259qs.min_worker_time AS [Min Worker Time],
1260qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
1261qs.max_worker_time AS [Max Worker Time],
1262qs.min_elapsed_time AS [Min Elapsed Time],
1263qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
1264qs.max_elapsed_time AS [Max Elapsed Time],
1265qs.execution_count AS [Execution Count],
1266CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1267qs.creation_time AS [Creation Time]
1268--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
1269FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
1270CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
1271CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
1272ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
1273------
1274
1275
1276-- Helps you find the most expensive queries from a memory perspective across the entire instance
1277-- Can also help track down parameter sniffing issues
1278
1279
1280-- Get top average elapsed time queries for entire instance (Query 51) (Top Avg Elapsed Time Queries)
1281SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
1282REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],
1283qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
1284qs.min_elapsed_time, qs.max_elapsed_time, qs.last_elapsed_time,
1285qs.execution_count AS [Execution Count],
1286qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
1287qs.total_physical_reads/qs.execution_count AS [Avg Physical Reads],
1288qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
1289CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1290qs.creation_time AS [Creation Time]
1291--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
1292FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
1293CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
1294CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
1295ORDER BY qs.total_elapsed_time/qs.execution_count DESC OPTION (RECOMPILE);
1296------
1297
1298-- Helps you find the highest average elapsed time queries across the entire instance
1299-- Can also help track down parameter sniffing issues
1300
1301
1302-- Look at UDF execution statistics (Query 52) (UDF Stats by DB)
1303SELECT TOP (25) DB_NAME(database_id) AS [Database Name],
1304 OBJECT_NAME(object_id, database_id) AS [Function Name],
1305 total_worker_time, execution_count, total_elapsed_time,
1306 total_elapsed_time/execution_count AS [avg_elapsed_time],
1307 last_elapsed_time, last_execution_time, cached_time
1308FROM sys.dm_exec_function_stats WITH (NOLOCK)
1309ORDER BY total_worker_time DESC OPTION (RECOMPILE);
1310------
1311
1312-- sys.dm_exec_function_stats (Transact-SQL)
1313-- https://bit.ly/2q1Q6BM
1314
1315-- Showplan Enhancements for UDFs
1316-- https://bit.ly/2LVqiQ1
1317
1318
1319-- Database specific queries *****************************************************************
1320
1321-- **** Please switch to a user database that you are interested in! *****
1322--USE YourDatabaseName; -- make sure to change to an actual database on your instance, not the master system database
1323--GO
1324
1325-- Individual File Sizes and space available for current database (Query 53) (File Sizes and Space)
1326SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
1327CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
1328CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
1329AS [Available Space In MB], f.[file_id], fg.name AS [Filegroup Name],
1330f.is_percent_growth, f.growth, fg.is_default, fg.is_read_only,
1331fg.is_autogrow_all_files -- New in SQL Server 2016
1332FROM sys.database_files AS f WITH (NOLOCK)
1333LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)
1334ON f.data_space_id = fg.data_space_id
1335ORDER BY f.[file_id] OPTION (RECOMPILE);
1336------
1337
1338-- Look at how large and how full the files are and where they are located
1339-- Make sure the transaction log is not full!!
1340
1341-- is_autogrow_all_files is new for SQL Server 2016. Equivalent to TF 1117 for user databases
1342
1343-- SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases
1344-- https://bit.ly/2evRZSR
1345
1346
1347-- Log space usage for current database (Query 54) (Log Space Usage)
1348SELECT DB_NAME(lsu.database_id) AS [Database Name], db.recovery_model_desc AS [Recovery Model],
1349 CAST(lsu.total_log_size_in_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Total Log Space (MB)],
1350 CAST(lsu.used_log_space_in_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Used Log Space (MB)],
1351 CAST(lsu.used_log_space_in_percent AS DECIMAL(10, 2)) AS [Used Log Space %],
1352 CAST(lsu.log_space_in_bytes_since_last_backup/1048576.0 AS DECIMAL(10, 2)) AS [Used Log Space Since Last Backup (MB)],
1353 db.log_reuse_wait_desc
1354FROM sys.dm_db_log_space_usage AS lsu WITH (NOLOCK)
1355INNER JOIN sys.databases AS db WITH (NOLOCK)
1356ON lsu.database_id = db.database_id
1357OPTION (RECOMPILE);
1358------
1359
1360-- Look at log file size and usage, along with the log reuse wait description for the current database
1361
1362
1363
1364-- Get database scoped configuration values for current database (Query 55) (Database-scoped Configurations)
1365SELECT configuration_id, name, [value] AS [value_for_primary], value_for_secondary
1366FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);
1367------
1368
1369-- This lets you see the value of these new properties for the current database
1370
1371-- Clear plan cache for current database
1372-- ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
1373
1374-- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
1375-- https://bit.ly/2sOH7nb
1376
1377
1378-- I/O Statistics by file for the current database (Query 56) (IO Stats By File)
1379SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], df.type_desc,
1380df.physical_name AS [Physical Name], CAST(vfs.size_on_disk_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Size on Disk (MB)],
1381vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,
1382CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],
1383CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
1384(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads],
1385CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read],
1386CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written],
1387CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
1388CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
1389CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],
1390CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]
1391FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
1392INNER JOIN sys.database_files AS df WITH (NOLOCK)
1393ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);
1394------
1395
1396-- This helps you characterize your workload better from an I/O perspective for this database
1397-- It helps you determine whether you has an OLTP or DW/DSS type of workload
1398
1399
1400
1401-- Get most frequently executed queries for this database (Query 57) (Query Execution Counts)
1402SELECT TOP(50) LEFT(t.[text], 50) AS [Short Query Text], qs.execution_count AS [Execution Count],
1403qs.total_logical_reads AS [Total Logical Reads],
1404qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
1405qs.total_worker_time AS [Total Worker Time],
1406qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
1407qs.total_elapsed_time AS [Total Elapsed Time],
1408qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
1409CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1410qs.creation_time AS [Creation Time]
1411--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
1412FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
1413CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
1414CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
1415WHERE t.dbid = DB_ID()
1416ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
1417------
1418
1419
1420-- Queries 56 through 61 are the "Bad Man List" for stored procedures
1421
1422-- Top Cached SPs By Execution Count (Query 58) (SP Execution Counts)
1423SELECT TOP(100) p.name AS [SP Name], qs.execution_count AS [Execution Count],
1424ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
1425qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
1426qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
1427qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
1428CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1429FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
1430FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
1431-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
1432FROM sys.procedures AS p WITH (NOLOCK)
1433INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
1434ON p.[object_id] = qs.[object_id]
1435CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
1436WHERE qs.database_id = DB_ID()
1437AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
1438ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
1439------
1440
1441-- Tells you which cached stored procedures are called the most often
1442-- This helps you characterize and baseline your workload
1443
1444
1445-- Top Cached SPs By Avg Elapsed Time (Query 59) (SP Avg Elapsed Time)
1446SELECT TOP(25) p.name AS [SP Name], qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
1447qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count,
1448ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
1449qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
1450qs.total_worker_time AS [TotalWorkerTime],
1451FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
1452FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
1453-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
1454FROM sys.procedures AS p WITH (NOLOCK)
1455INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
1456ON p.[object_id] = qs.[object_id]
1457CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
1458WHERE qs.database_id = DB_ID()
1459AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
1460ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);
1461------
1462
1463-- This helps you find high average elapsed time cached stored procedures that
1464-- may be easy to optimize with standard query tuning techniques
1465
1466
1467
1468-- Top Cached SPs By Total Worker time. Worker time relates to CPU cost (Query 60) (SP Worker Time)
1469SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
1470qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
1471ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
1472qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
1473CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1474FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
1475FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
1476-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
1477FROM sys.procedures AS p WITH (NOLOCK)
1478INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
1479ON p.[object_id] = qs.[object_id]
1480CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
1481WHERE qs.database_id = DB_ID()
1482AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
1483ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
1484------
1485
1486-- This helps you find the most expensive cached stored procedures from a CPU perspective
1487-- You should look at this if you see signs of CPU pressure
1488
1489
1490-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure (Query 61) (SP Logical Reads)
1491SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
1492qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
1493ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
1494qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
1495CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1496FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
1497FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
1498-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
1499FROM sys.procedures AS p WITH (NOLOCK)
1500INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
1501ON p.[object_id] = qs.[object_id]
1502CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
1503WHERE qs.database_id = DB_ID()
1504AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
1505ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
1506------
1507
1508-- This helps you find the most expensive cached stored procedures from a memory perspective
1509-- You should look at this if you see signs of memory pressure
1510
1511
1512-- Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure (Query 62) (SP Physical Reads)
1513SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],
1514qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,
1515qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
1516CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1517FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
1518FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
1519-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
1520FROM sys.procedures AS p WITH (NOLOCK)
1521INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
1522ON p.[object_id] = qs.[object_id]
1523CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
1524WHERE qs.database_id = DB_ID()
1525AND qs.total_physical_reads > 0
1526ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);
1527------
1528
1529-- This helps you find the most expensive cached stored procedures from a read I/O perspective
1530-- You should look at this if you see signs of I/O pressure or of memory pressure
1531
1532
1533
1534-- Top Cached SPs By Total Logical Writes (Query 63) (SP Logical Writes)
1535-- Logical writes relate to both memory and disk I/O pressure
1536SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites],
1537qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
1538ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
1539qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
1540CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
1541FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
1542FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
1543-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
1544FROM sys.procedures AS p WITH (NOLOCK)
1545INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
1546ON p.[object_id] = qs.[object_id]
1547CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
1548WHERE qs.database_id = DB_ID()
1549AND qs.total_logical_writes > 0
1550AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
1551ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
1552------
1553
1554-- This helps you find the most expensive cached stored procedures from a write I/O perspective
1555-- You should look at this if you see signs of I/O pressure or of memory pressure
1556
1557
1558-- Lists the top statements by average input/output usage for the current database (Query 64) (Top IO Statements)
1559SELECT TOP(50) OBJECT_NAME(qt.objectid, dbid) AS [SP Name],
1560(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], qs.execution_count AS [Execution Count],
1561SUBSTRING(qt.[text],qs.statement_start_offset/2,
1562 (CASE
1563 WHEN qs.statement_end_offset = -1
1564 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
1565 ELSE qs.statement_end_offset
1566 END - qs.statement_start_offset)/2) AS [Query Text]
1567FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
1568CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
1569WHERE qt.[dbid] = DB_ID()
1570ORDER BY [Avg IO] DESC OPTION (RECOMPILE);
1571------
1572
1573-- Helps you find the most expensive statements for I/O by SP
1574
1575
1576
1577-- Possible Bad NC Indexes (writes > reads) (Query 65) (Bad NC Indexes)
1578SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
1579i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
1580s.user_updates AS [Total Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
1581s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]
1582FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
1583INNER JOIN sys.indexes AS i WITH (NOLOCK)
1584ON s.[object_id] = i.[object_id]
1585AND i.index_id = s.index_id
1586WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
1587AND s.database_id = DB_ID()
1588AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
1589AND i.index_id > 1 AND i.[type_desc] = N'NONCLUSTERED'
1590AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0
1591ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
1592------
1593
1594-- Look for indexes with high numbers of writes and zero or very low numbers of reads
1595-- Consider your complete workload, and how long your instance has been running
1596-- Investigate further before dropping an index!
1597
1598
1599-- Missing Indexes for current database by Index Advantage (Query 66) (Missing Indexes)
1600SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],
1601migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
1602COUNT(1) OVER(PARTITION BY mid.[statement]) AS [missing_indexes_for_table],
1603COUNT(1) OVER(PARTITION BY mid.[statement], equality_columns) AS [similar_missing_indexes_for_table],
1604mid.equality_columns, mid.inequality_columns, mid.included_columns,
1605migs.unique_compiles, migs.user_seeks,
1606CONVERT(decimal(18,2), migs.avg_total_user_cost) AS [avg_total_user_cost], migs.avg_user_impact,
1607OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
1608FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
1609INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
1610ON migs.group_handle = mig.index_group_handle
1611INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
1612ON mig.index_handle = mid.index_handle
1613INNER JOIN sys.partitions AS p WITH (NOLOCK)
1614ON p.[object_id] = mid.[object_id]
1615WHERE mid.database_id = DB_ID()
1616AND p.index_id < 2
1617ORDER BY index_advantage DESC OPTION (RECOMPILE);
1618------
1619
1620-- Look at index advantage, last user seek time, number of user seeks to help determine source and importance
1621-- SQL Server is overly eager to add included columns, so beware
1622-- Do not just blindly add indexes that show up from this query!!!
1623
1624
1625-- Find missing index warnings for cached plans in the current database (Query 67) (Missing Index Warnings)
1626-- Note: This query could take some time on a busy instance
1627SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],
1628 cp.objtype, cp.usecounts, cp.size_in_bytes, query_plan
1629FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
1630CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
1631WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
1632AND dbid = DB_ID()
1633ORDER BY cp.usecounts DESC OPTION (RECOMPILE);
1634------
1635
1636-- Helps you connect missing indexes to specific stored procedures or queries
1637-- This can help you decide whether to add them or not
1638
1639
1640-- Breaks down buffers used by current database by object (table, index) in the buffer cache (Query 68) (Buffer Usage)
1641-- Note: This query could take some time on a busy instance
1642SELECT OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id,
1643CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
1644COUNT(*) AS [BufferCount], p.[Rows] AS [Row Count],
1645p.data_compression_desc AS [Compression Type]
1646FROM sys.allocation_units AS a WITH (NOLOCK)
1647INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
1648ON a.allocation_unit_id = b.allocation_unit_id
1649INNER JOIN sys.partitions AS p WITH (NOLOCK)
1650ON a.container_id = p.hobt_id
1651WHERE b.database_id = CONVERT(int, DB_ID())
1652AND p.[object_id] > 100
1653AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
1654AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
1655AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
1656GROUP BY p.[object_id], p.index_id, p.data_compression_desc, p.[Rows]
1657ORDER BY [BufferCount] DESC OPTION (RECOMPILE);
1658------
1659
1660-- Tells you what tables and indexes are using the most memory in the buffer cache
1661-- It can help identify possible candidates for data compression
1662
1663
1664-- Get Table names, row counts, and compression status for clustered index or heap (Query 69) (Table Sizes)
1665SELECT OBJECT_NAME(object_id) AS [ObjectName],
1666SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]
1667FROM sys.partitions WITH (NOLOCK)
1668WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
1669AND OBJECT_NAME(object_id) NOT LIKE N'sys%'
1670AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'
1671AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%'
1672AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
1673AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'
1674AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'
1675AND OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%'
1676AND OBJECT_NAME(object_id) NOT LIKE N'sqlagent_job%'
1677AND OBJECT_NAME(object_id) NOT LIKE N'plan_persist%'
1678GROUP BY object_id, data_compression_desc
1679ORDER BY SUM(Rows) DESC OPTION (RECOMPILE);
1680------
1681
1682-- Gives you an idea of table sizes, and possible data compression opportunities
1683
1684
1685
1686-- Get some key table properties (Query 70) (Table Properties)
1687SELECT OBJECT_NAME(t.[object_id]) AS [ObjectName], p.[rows] AS [Table Rows], p.index_id,
1688 p.data_compression_desc AS [Index Data Compression],
1689 t.create_date, t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter,
1690 t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_filetable,
1691 t.is_memory_optimized, t.durability_desc,
1692 t.temporal_type_desc, t.is_remote_data_archive_enabled, t.is_external -- new for SQL Server 2016
1693FROM sys.tables AS t WITH (NOLOCK)
1694INNER JOIN sys.partitions AS p WITH (NOLOCK)
1695ON t.[object_id] = p.[object_id]
1696WHERE OBJECT_NAME(t.[object_id]) NOT LIKE N'sys%'
1697ORDER BY OBJECT_NAME(t.[object_id]), p.index_id OPTION (RECOMPILE);
1698------
1699
1700-- Gives you some good information about your tables
1701-- is_memory_optimized and durability_desc were new in SQL Server 2014
1702-- temporal_type_desc, is_remote_data_archive_enabled, is_external are new in SQL Server 2016
1703
1704-- sys.tables (Transact-SQL)
1705-- https://bit.ly/2Gk7998
1706
1707
1708
1709-- When were Statistics last updated on all indexes? (Query 71) (Statistics Update)
1710SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.[NAME] AS [Object Name], o.[type_desc] AS [Object Type],
1711 i.[name] AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
1712 s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary,
1713 st.row_count, st.used_page_count
1714FROM sys.objects AS o WITH (NOLOCK)
1715INNER JOIN sys.indexes AS i WITH (NOLOCK)
1716ON o.[object_id] = i.[object_id]
1717INNER JOIN sys.stats AS s WITH (NOLOCK)
1718ON i.[object_id] = s.[object_id]
1719AND i.index_id = s.stats_id
1720INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
1721ON o.[object_id] = st.[object_id]
1722AND i.[index_id] = st.[index_id]
1723WHERE o.[type] IN ('U', 'V')
1724AND st.row_count > 0
1725ORDER BY STATS_DATE(i.[object_id], i.index_id) DESC OPTION (RECOMPILE);
1726------
1727
1728-- Helps discover possible problems with out-of-date statistics
1729-- Also gives you an idea which indexes are the most active
1730
1731-- sys.stats (Transact-SQL)
1732-- https://bit.ly/2GyAxrn
1733
1734-- UPDATEs to Statistics (Erin Stellato)
1735-- https://bit.ly/2vhrYQy
1736
1737
1738
1739-- Look at most frequently modified indexes and statistics (Query 72) (Volatile Indexes)
1740SELECT o.[name] AS [Object Name], o.[object_id], o.[type_desc], s.[name] AS [Statistics Name],
1741 s.stats_id, s.no_recompute, s.auto_created, s.is_incremental, s.is_temporary,
1742 sp.modification_counter, sp.[rows], sp.rows_sampled, sp.last_updated
1743FROM sys.objects AS o WITH (NOLOCK)
1744INNER JOIN sys.stats AS s WITH (NOLOCK)
1745ON s.object_id = o.object_id
1746CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
1747WHERE o.[type_desc] NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')
1748AND sp.modification_counter > 0
1749ORDER BY sp.modification_counter DESC, o.name OPTION (RECOMPILE);
1750------
1751
1752-- This helps you understand your workload and make better decisions about
1753-- things like data compression and adding new indexes to a table
1754
1755
1756
1757-- Get fragmentation info for all indexes above a certain size in the current database (Query 73) (Index Fragmentation)
1758-- Note: This query could take some time on a very large database
1759SELECT DB_NAME(ps.database_id) AS [Database Name], SCHEMA_NAME(o.[schema_id]) AS [Schema Name],
1760OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], i.[name] AS [Index Name], ps.index_id,
1761ps.index_type_desc, ps.avg_fragmentation_in_percent,
1762ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter,
1763i.filter_definition, i.[allow_page_locks]
1764FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps
1765INNER JOIN sys.indexes AS i WITH (NOLOCK)
1766ON ps.[object_id] = i.[object_id]
1767AND ps.index_id = i.index_id
1768INNER JOIN sys.objects AS o WITH (NOLOCK)
1769ON i.[object_id] = o.[object_id]
1770WHERE ps.database_id = DB_ID()
1771AND ps.page_count > 2500
1772ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
1773------
1774
1775-- Helps determine whether you have framentation in your relational indexes
1776-- and how effective your index maintenance strategy is
1777
1778
1779--- Index Read/Write stats (all tables in current DB) ordered by Reads (Query 74) (Overall Index Usage - Reads)
1780SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.[name] AS [IndexName], i.index_id,
1781 s.user_seeks, s.user_scans, s.user_lookups,
1782 s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
1783 s.user_updates AS [Writes],
1784 i.[type_desc] AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,
1785 s.last_user_scan, s.last_user_lookup, s.last_user_seek
1786FROM sys.indexes AS i WITH (NOLOCK)
1787LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
1788ON i.[object_id] = s.[object_id]
1789AND i.index_id = s.index_id
1790AND s.database_id = DB_ID()
1791WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
1792ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC OPTION (RECOMPILE); -- Order by reads
1793------
1794
1795-- Show which indexes in the current database are most active for Reads
1796
1797
1798--- Index Read/Write stats (all tables in current DB) ordered by Writes (Query 75) (Overall Index Usage - Writes)
1799SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.[name] AS [IndexName], i.index_id,
1800 s.user_updates AS [Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
1801 i.[type_desc] AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,
1802 s.last_system_update, s.last_user_update
1803FROM sys.indexes AS i WITH (NOLOCK)
1804LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
1805ON i.[object_id] = s.[object_id]
1806AND i.index_id = s.index_id
1807AND s.database_id = DB_ID()
1808WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
1809ORDER BY s.user_updates DESC OPTION (RECOMPILE); -- Order by writes
1810------
1811
1812-- Show which indexes in the current database are most active for Writes
1813
1814
1815-- Get in-memory OLTP index usage (Query 76) (XTP Index Usage)
1816SELECT OBJECT_NAME(i.[object_id]) AS [Object Name], i.index_id, i.[name] AS [Index Name],
1817 i.[type_desc], xis.scans_started, xis.scans_retries,
1818 xis.rows_touched, xis.rows_returned
1819FROM sys.dm_db_xtp_index_stats AS xis WITH (NOLOCK)
1820INNER JOIN sys.indexes AS i WITH (NOLOCK)
1821ON i.[object_id] = xis.[object_id]
1822AND i.index_id = xis.index_id
1823ORDER BY OBJECT_NAME(i.[object_id]) OPTION (RECOMPILE);
1824------
1825
1826-- This gives you some index usage statistics for in-memory OLTP
1827-- Returns no data if you are not using in-memory OLTP
1828
1829-- Guidelines for Using Indexes on Memory-Optimized Tables
1830-- https://bit.ly/2GCP8lF
1831
1832
1833-- Look at Columnstore index physical statistics (Query 77) (Columnstore Index Physical Stat)
1834SELECT OBJECT_NAME(ps.object_id) AS [TableName],
1835 i.[name] AS [IndexName], ps.index_id, ps.partition_number,
1836 ps.delta_store_hobt_id, ps.state_desc, ps.total_rows, ps.size_in_bytes,
1837 ps.trim_reason_desc, ps.generation, ps.transition_to_compressed_state_desc,
1838 ps.has_vertipaq_optimization, ps.deleted_rows,
1839 100 * (ISNULL(ps.deleted_rows, 0))/ps.total_rows AS [Fragmentation]
1840FROM sys.dm_db_column_store_row_group_physical_stats AS ps WITH (NOLOCK)
1841INNER JOIN sys.indexes AS i WITH (NOLOCK)
1842ON ps.object_id = i.object_id
1843AND ps.index_id = i.index_id
1844ORDER BY ps.object_id, ps.partition_number, ps.row_group_id OPTION (RECOMPILE);
1845------
1846
1847-- sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)
1848-- https://bit.ly/2q276XQ
1849
1850
1851-- Get lock waits for current database (Query 78) (Lock Waits)
1852SELECT o.name AS [table_name], i.name AS [index_name], ios.index_id, ios.partition_number,
1853 SUM(ios.row_lock_wait_count) AS [total_row_lock_waits],
1854 SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms],
1855 SUM(ios.page_lock_wait_count) AS [total_page_lock_waits],
1856 SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms],
1857 SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]
1858FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
1859INNER JOIN sys.objects AS o WITH (NOLOCK)
1860ON ios.[object_id] = o.[object_id]
1861INNER JOIN sys.indexes AS i WITH (NOLOCK)
1862ON ios.[object_id] = i.[object_id]
1863AND ios.index_id = i.index_id
1864WHERE o.[object_id] > 100
1865GROUP BY o.name, i.name, ios.index_id, ios.partition_number
1866HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0
1867ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE);
1868------
1869
1870-- This query is helpful for troubleshooting blocking and deadlocking issues
1871
1872
1873
1874-- Look at UDF execution statistics (Query 79) (UDF Statistics)
1875SELECT OBJECT_NAME(object_id) AS [Function Name], execution_count,
1876 total_worker_time, total_logical_reads, total_physical_reads, total_elapsed_time,
1877 total_elapsed_time/execution_count AS [avg_elapsed_time],
1878 FORMAT(cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
1879FROM sys.dm_exec_function_stats WITH (NOLOCK)
1880WHERE database_id = DB_ID()
1881ORDER BY total_worker_time DESC OPTION (RECOMPILE);
1882------
1883
1884-- New for SQL Server 2016
1885-- Helps you investigate scalar UDF performance issues
1886-- Does not return information for table valued functions
1887
1888-- sys.dm_exec_function_stats (Transact-SQL)
1889-- https://bit.ly/2q1Q6BM
1890
1891
1892-- Get Query Store Options for this database (Query 80) (QueryStore Options)
1893SELECT actual_state_desc, desired_state_desc, [interval_length_minutes],
1894 current_storage_size_mb, [max_storage_size_mb],
1895 query_capture_mode_desc, size_based_cleanup_mode_desc
1896FROM sys.database_query_store_options WITH (NOLOCK) OPTION (RECOMPILE);
1897------
1898
1899-- New for SQL Server 2016
1900-- Requires that Query Store is enabled for this database
1901
1902-- Tuning Workload Performance with Query Store
1903-- https://bit.ly/1kHSl7w
1904
1905
1906-- Get highest aggregate duration queries over last hour (Query 81) (High Aggregate Duration Queries)
1907WITH AggregatedDurationLastHour
1908AS
1909(SELECT q.query_id, SUM(count_executions * avg_duration) AS total_duration,
1910 COUNT (distinct p.plan_id) AS number_of_plans
1911 FROM sys.query_store_query_text AS qt WITH (NOLOCK)
1912 INNER JOIN sys.query_store_query AS q WITH (NOLOCK)
1913 ON qt.query_text_id = q.query_text_id
1914 INNER JOIN sys.query_store_plan AS p WITH (NOLOCK)
1915 ON q.query_id = p.query_id
1916 INNER JOIN sys.query_store_runtime_stats AS rs WITH (NOLOCK)
1917 ON rs.plan_id = p.plan_id
1918 INNER JOIN sys.query_store_runtime_stats_interval AS rsi WITH (NOLOCK)
1919 ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
1920 WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())
1921 AND rs.execution_type_desc = N'Regular'
1922 GROUP BY q.query_id),
1923OrderedDuration AS
1924(SELECT query_id, total_duration, number_of_plans,
1925 ROW_NUMBER () OVER (ORDER BY total_duration DESC, query_id) AS RN
1926 FROM AggregatedDurationLastHour)
1927SELECT OBJECT_NAME(q.object_id) AS [Containing Object], qt.query_sql_text,
1928od.total_duration AS [Total Duration (microsecs)],
1929od.number_of_plans AS [Plan Count],
1930p.is_forced_plan, p.is_parallel_plan, p.is_trivial_plan,
1931q.query_parameterization_type_desc, p.[compatibility_level],
1932p.last_compile_start_time, q.last_execution_time,
1933CONVERT(xml, p.query_plan) AS query_plan_xml
1934FROM OrderedDuration AS od
1935INNER JOIN sys.query_store_query AS q WITH (NOLOCK)
1936ON q.query_id = od.query_id
1937INNER JOIN sys.query_store_query_text AS qt WITH (NOLOCK)
1938ON q.query_text_id = qt.query_text_id
1939INNER JOIN sys.query_store_plan AS p WITH (NOLOCK)
1940ON q.query_id = p.query_id
1941WHERE od.RN <= 50
1942ORDER BY total_duration DESC OPTION (RECOMPILE);
1943------
1944
1945-- New for SQL Server 2016
1946-- Requires that QueryStore is enabled for this database
1947
1948
1949-- Get input buffer information for the current database (Query 82) (Input Buffer)
1950SELECT es.session_id, DB_NAME(es.database_id) AS [Database Name],
1951es.login_time, es.cpu_time, es.logical_reads,
1952es.[status], ib.event_info AS [Input Buffer]
1953FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
1954CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
1955WHERE es.database_id = DB_ID()
1956AND es.session_id > 50
1957AND es.session_id <> @@SPID OPTION (RECOMPILE);
1958
1959-- Gives you input buffer information from all non-system sessions for the current database
1960-- Replaces DBCC INPUTBUFFER
1961
1962-- New DMF for retrieving input buffer in SQL Server
1963-- https://bit.ly/2uHKMbz
1964
1965
1966
1967-- Look at recent Full backups for the current database (Query 83) (Recent Full Backups)
1968SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,
1969CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
1970CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
1971CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
1972CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,
1973DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
1974bs.backup_finish_date AS [Backup Finish Date], bmf.physical_device_name AS [Backup Location], bmf.physical_block_size
1975FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
1976INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
1977ON bs.media_set_id = bmf.media_set_id
1978WHERE bs.database_name = DB_NAME(DB_ID())
1979AND bs.[type] = 'D' -- Change to L if you want Log backups
1980ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
1981------
1982
1983-- Are your backup sizes and times changing over time?
1984-- Are you using backup compression?
1985-- Are you using backup checksums?
1986-- Are you doing copy_only backups?
1987-- Are you doing encrypted backups?
1988-- Have you done any backup tuning with striped backups, or changing the parameters of the backup command?
1989
1990-- In SQL Server 2016, native SQL Server backup compression actually works much better with databases that are using TDE than in previous versions
1991-- https://bit.ly/28Rpb2x
1992
1993
1994-- These four Pluralsight Courses go into more detail about how to run these queries and interpret the results
1995
1996-- SQL Server 2017: Diagnosing Configuration Issues with DMVs
1997-- https://bit.ly/2MSUDUL
1998
1999-- SQL Server 2014 DMV Diagnostic Queries \96 Part 1
2000-- https://bit.ly/2plxCer
2001
2002-- SQL Server 2014 DMV Diagnostic Queries \96 Part 2
2003-- https://bit.ly/2IuJpzI
2004
2005-- SQL Server 2014 DMV Diagnostic Queries \96 Part 3
2006-- https://bit.ly/2FIlCPb
2007
2008
2009
2010-- Sign up for Microsoft Visual Studio Dev Essentials and get a free three month pass to Pluralsight
2011
2012-- Microsoft Visual Studio Dev Essentials
2013-- http://bit.ly/1q6xbDL
2014
2015
2016-- Sign up for Microsoft Azure Essentials and get lots of free Azure usage credits, MCP exam voucher, three month Pluralsight subscription
2017
2018-- Microsoft Azure Essentials
2019-- https://bit.ly/2JMWe8x
2020
2021
2022-- August 2017 blog series about upgrading and migrating to SQL Server 2016/2017
2023-- https://bit.ly/2ftKVrX