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