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