-- Database specific queries *****************************************************************

-- **** Please switch to a user database that you are interested in! *****

--USE YourDatabaseName; -- make sure to change to an actual database on your instance, not the master system database

--GO

-- Get database scoped configuration values for current database (Query 55) (Database-scoped Configurations)

SELECT configuration_id, name, [value] AS [value_for_primary], value_for_secondary

FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);

------

-- This lets you see the value of these new properties for the current database

-- Clear plan cache for current database

-- ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

-- https://bit.ly/2sOH7nb

-- I/O Statistics by file for the current database (Query 56) (IO Stats By File)

SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], df.type_desc,

df.physical_name AS [Physical Name], CAST(vfs.size_on_disk_bytes/1048576.0 AS DECIMAL(15, 2)) AS [Size on Disk (MB)],

vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,

CAST(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],

CAST(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],

(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads],

CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(15, 2)) AS [MB Read],

CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(15, 2)) AS [MB Written],

CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(15,1)) AS [# Reads Pct],

CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(15,1)) AS [# Write Pct],

CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(15,1)) AS [Read Bytes Pct],

CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(15,1)) AS [Written Bytes Pct]

FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs

INNER JOIN sys.database_files AS df WITH (NOLOCK)

ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);

------

-- This helps you characterize your workload better from an I/O perspective for this database

-- It helps you determine whether you have an OLTP or DW/DSS type of workload

-- Get most frequently executed queries for this database (Query 57) (Query Execution Counts)

SELECT TOP(50) LEFT(t.[text], 50) AS [Short Query Text], qs.execution_count AS [Execution Count],

qs.total_logical_reads AS [Total Logical Reads],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

qs.total_worker_time AS [Total Worker Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time],

qs.total_elapsed_time AS [Total Elapsed Time],

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

qs.creation_time AS [Creation Time]

--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

WHERE t.dbid = DB_ID()

ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

------

-- Tells you which cached queries are called the most often

-- This helps you characterize and baseline your workload

-- It also helps you find possible caching opportunities

-- CREATE PROCEDURE (Transact-SQL)

-- https://bit.ly/3gxcuxG

-- Queries 58 through 64 are the "Bad Man List" for stored procedures

-- Top Cached SPs By Execution Count (Query 58) (SP Execution Counts)

SELECT TOP(100) p.name AS [SP Name], qs.execution_count AS [Execution Count],

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],

FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

------

-- Tells you which cached stored procedures are called the most often

-- This helps you characterize and baseline your workload

-- It also helps you find possible caching opportunities

-- Top Cached SPs By Avg Elapsed Time (Query 59) (SP Avg Elapsed Time)

SELECT TOP(25) p.name AS [SP Name], qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.total_worker_time AS [TotalWorkerTime],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],

FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);

------

-- This helps you find high average elapsed time cached stored procedures that

-- may be easy to optimize with standard query tuning techniques

-- Top Cached SPs By Total Worker time. Worker time relates to CPU cost (Query 60) (SP Worker Time)

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],

FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

------

-- This helps you find the most expensive cached stored procedures from a CPU perspective

-- You should look at this if you see signs of CPU pressure

-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure (Query 61) (SP Logical Reads)

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],

FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

------

-- This helps you find the most expensive cached stored procedures from a memory perspective

-- You should look at this if you see signs of memory pressure

-- Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure (Query 62) (SP Physical Reads)

SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],

qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,

qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],

FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND qs.total_physical_reads > 0

ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);

------

-- This helps you find the most expensive cached stored procedures from a read I/O perspective

-- You should look at this if you see signs of I/O pressure or of memory pressure

-- Top Cached SPs By Total Logical Writes (Query 63) (SP Logical Writes)

-- Logical writes relate to both memory and disk I/O pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites],

qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],

FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND qs.total_logical_writes > 0

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);

------

-- This helps you find the most expensive cached stored procedures from a write I/O perspective

-- You should look at this if you see signs of I/O pressure or of memory pressure

-- Cached SPs Missing Indexes by Execution Count (Query 64) (SP Missing Index)

SELECT TOP(25) p.name AS [SP Name], qs.execution_count AS [Execution Count],

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],

FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

AND CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%'

ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

------

-- This helps you find the most frequently executed cached stored procedures that have missing index warnings

-- This can often help you find index tuning candidates

-- Lists the top statements by average input/output usage for the current database (Query 65) (Top IO Statements)

SELECT TOP(50) OBJECT_NAME(qt.objectid, dbid) AS [SP Name],

(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], qs.execution_count AS [Execution Count],

SUBSTRING(qt.[text],qs.statement_start_offset/2,

(CASE

WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) AS [Query Text]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.[dbid] = DB_ID()

ORDER BY [Avg IO] DESC OPTION (RECOMPILE);

------

-- Helps you find the most expensive statements for I/O by SP

-- Possible Bad NC Indexes (writes > reads) (Query 66) (Bad NC Indexes)

SELECT SCHEMA_NAME(o.[schema_id]) AS [Schema Name],

OBJECT_NAME(s.[object_id]) AS [Table Name],

i.name AS [Index Name], i.index_id,

i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,

s.user_updates AS [Total Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],

s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON i.[object_id] = o.[object_id]

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)

AND i.index_id > 1 AND i.[type_desc] = N'NONCLUSTERED'

AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

------

-- Look for indexes with high numbers of writes and zero or very low numbers of reads

-- Consider your complete workload, and how long your instance has been running

-- Investigate further before dropping an index!

-- Missing Indexes for current database by Index Advantage (Query 67) (Missing Indexes)

SELECT CONVERT(decimal(18,2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01)) AS [index_advantage],

FORMAT(migs.last_user_seek, 'yyyy-MM-dd HH:mm:ss') AS [last_user_seek], mid.[statement] AS [Database.Schema.Table],

COUNT(1) OVER(PARTITION BY mid.[statement]) AS [missing_indexes_for_table],

COUNT(1) OVER(PARTITION BY mid.[statement], mid.equality_columns) AS [similar_missing_indexes_for_table],

mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.user_seeks,

CONVERT(decimal(18,2), migs.avg_total_user_cost) AS [avg_total_user_,cost], migs.avg_user_impact,

REPLACE(REPLACE(LEFT(st.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],

OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]

FROM sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_group_stats_query AS migs WITH(NOLOCK)

ON mig.index_group_handle = migs.group_handle

CROSS APPLY sys.dm_exec_sql_text(migs.last_sql_handle) AS st

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON p.[object_id] = mid.[object_id]

WHERE mid.database_id = DB_ID()

AND p.index_id < 2

ORDER BY index_advantage DESC OPTION (RECOMPILE);

------

-- Look at index advantage, last user seek time, number of user seeks to help determine source and importance

-- SQL Server is overly eager to add included columns, so beware

-- Do not just blindly add indexes that show up from this query!!!

-- Hkan Winther has given me some great suggestions for this query

-- Find missing index warnings for cached plans in the current database (Query 68) (Missing Index Warnings)

-- Note: This query could take some time on a busy instance

SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],

cp.objtype, cp.usecounts, cp.size_in_bytes, qp.query_plan

FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'

AND dbid = DB_ID()

ORDER BY cp.usecounts DESC OPTION (RECOMPILE);

------

-- Helps you connect missing indexes to specific stored procedures or queries

-- This can help you decide whether to add them or not

-- Breaks down buffers used by current database by object (table, index) in the buffer cache (Query 69) (Buffer Usage)

-- Note: This query could take some time on a busy instance

SELECT fg.name AS [Filegroup Name], SCHEMA_NAME(o.Schema_ID) AS [Schema Name],

OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id,

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

COUNT(*) AS [BufferCount], p.[Rows] AS [Row Count],

p.data_compression_desc AS [Compression Type]

FROM sys.allocation_units AS a WITH (NOLOCK)

INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)

ON a.allocation_unit_id = b.allocation_unit_id

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON a.container_id = p.hobt_id

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON p.object_id = o.object_id

INNER JOIN sys.database_files AS f WITH (NOLOCK)

ON b.file_id = f.file_id

INNER JOIN sys.filegroups AS fg WITH (NOLOCK)

ON f.data_space_id = fg.data_space_id

WHERE b.database_id = CONVERT(int, DB_ID())

AND p.[object_id] > 100

AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'

AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'

AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'

GROUP BY fg.name, o.Schema_ID, p.[object_id], p.index_id,

p.data_compression_desc, p.[Rows]

ORDER BY [BufferCount] DESC OPTION (RECOMPILE);

------

-- Tells you what tables and indexes are using the most memory in the buffer cache

-- It can help identify possible candidates for data compression

-- Get Schema names, Table names, object size, row counts, and compression status for clustered index or heap (Query 70) (Table Sizes)

SELECT SCHEMA_NAME(o.Schema_ID) AS [Schema Name], OBJECT_NAME(p.object_id) AS [Object Name],

CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(19,2)) AS [Object Size (MB)],

SUM(p.Rows) AS [Row Count],

p.data_compression_desc AS [Compression Type]

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON p.object_id = o.object_id

INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)

ON p.object_id = ps.object_id

WHERE ps.index_id < 2 -- ignore the partitions from the non-clustered indexes if any

AND p.index_id < 2 -- ignore the partitions from the non-clustered indexes if any

AND o.type_desc = N'USER_TABLE'

GROUP BY SCHEMA_NAME(o.Schema_ID), p.object_id, ps.reserved_page_count, p.data_compression_desc

ORDER BY SUM(ps.reserved_page_count) DESC, SUM(p.Rows) DESC OPTION (RECOMPILE);

------

-- Gives you an idea of table sizes, and possible data compression opportunities

-- Get some key table properties (Query 71) (Table Properties)

SELECT OBJECT_NAME(t.[object_id]) AS [ObjectName], p.[rows] AS [Table Rows], p.index_id,

p.data_compression_desc AS [Index Data Compression],

t.create_date, t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter,

t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_filetable,

t.is_memory_optimized, t.durability_desc,

t.temporal_type_desc, t.is_remote_data_archive_enabled, t.is_external -- new for SQL Server 2016

FROM sys.tables AS t WITH (NOLOCK)

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON t.[object_id] = p.[object_id]

WHERE OBJECT_NAME(t.[object_id]) NOT LIKE N'sys%'

ORDER BY OBJECT_NAME(t.[object_id]), p.index_id OPTION (RECOMPILE);

------

-- Gives you some good information about your tables

-- is_memory_optimized and durability_desc were new in SQL Server 2014

-- temporal_type_desc, is_remote_data_archive_enabled, is_external were new in SQL Server 2016

-- sys.tables (Transact-SQL)

-- https://bit.ly/2Gk7998

-- When were Statistics last updated on all indexes? (Query 72) (Statistics Update)

SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.[NAME] AS [Object Name], o.[type_desc] AS [Object Type],

i.[name] AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],

s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary,

st.row_count, st.used_page_count

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON o.[object_id] = i.[object_id]

INNER JOIN sys.stats AS s WITH (NOLOCK)

ON i.[object_id] = s.[object_id]

AND i.index_id = s.stats_id

INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)

ON o.[object_id] = st.[object_id]

AND i.[index_id] = st.[index_id]

WHERE o.[type] IN ('U', 'V')

AND st.row_count > 0

ORDER BY STATS_DATE(i.[object_id], i.index_id) DESC OPTION (RECOMPILE);

------

-- Helps discover possible problems with out-of-date statistics

-- Also gives you an idea which indexes are the most active

-- sys.stats (Transact-SQL)

-- https://bit.ly/2GyAxrn

-- UPDATEs to Statistics (Erin Stellato)

-- https://bit.ly/2vhrYQy

-- Look at most frequently modified indexes and statistics (Query 73) (Volatile Indexes)

SELECT o.[name] AS [Object Name], o.[object_id], o.[type_desc], s.[name] AS [Statistics Name],

s.stats_id, s.no_recompute, s.auto_created, s.is_incremental, s.is_temporary,

sp.modification_counter, sp.[rows], sp.rows_sampled, sp.last_updated

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.stats AS s WITH (NOLOCK)

ON s.object_id = o.object_id

CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp

WHERE o.[type_desc] NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')

AND sp.modification_counter > 0

ORDER BY sp.modification_counter DESC, o.name OPTION (RECOMPILE);

------

-- This helps you understand your workload and make better decisions about

-- things like data compression and adding new indexes to a table

-- Get fragmentation info for all indexes above a certain size in the current database (Query 74) (Index Fragmentation)

-- Note: This query could take some time on a very large database

SELECT DB_NAME(ps.database_id) AS [Database Name], SCHEMA_NAME(o.[schema_id]) AS [Schema Name],

OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], i.[name] AS [Index Name], ps.index_id,

ps.index_type_desc, ps.avg_fragmentation_in_percent,

ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter,

i.filter_definition, i.[allow_page_locks]

FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON ps.[object_id] = i.[object_id]

AND ps.index_id = i.index_id

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON i.[object_id] = o.[object_id]

WHERE ps.database_id = DB_ID()

AND ps.page_count > 2500

ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

------

-- Helps determine whether you have framentation in your relational indexes

-- and how effective your index maintenance strategy is

--- Index Read/Write stats (all tables in current DB) ordered by Reads (Query 75) (Overall Index Usage - Reads)

SELECT SCHEMA_NAME(t.[schema_id]) AS [SchemaName], OBJECT_NAME(i.[object_id]) AS [ObjectName],

i.[name] AS [IndexName], i.index_id,

s.user_seeks, s.user_scans, s.user_lookups,

s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],

s.user_updates AS [Writes],

i.[type_desc] AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,

s.last_user_scan, s.last_user_lookup, s.last_user_seek

FROM sys.indexes AS i WITH (NOLOCK)

LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

ON i.[object_id] = s.[object_id]

AND i.index_id = s.index_id

AND s.database_id = DB_ID()

LEFT OUTER JOIN sys.tables AS t WITH (NOLOCK)

ON t.[object_id] = i.[object_id]

WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1

ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC OPTION (RECOMPILE); -- Order by reads

------

-- Show which indexes in the current database are most active for Reads

--- Index Read/Write stats (all tables in current DB) ordered by Writes (Query 76) (Overall Index Usage - Writes)

SELECT SCHEMA_NAME(t.[schema_id]) AS [SchemaName],OBJECT_NAME(i.[object_id]) AS [ObjectName],

i.[name] AS [IndexName], i.index_id,

s.user_updates AS [Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],

i.[type_desc] AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,

s.last_system_update, s.last_user_update

FROM sys.indexes AS i WITH (NOLOCK)

LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

ON i.[object_id] = s.[object_id]

AND i.index_id = s.index_id

AND s.database_id = DB_ID()

LEFT OUTER JOIN sys.tables AS t WITH (NOLOCK)

ON t.[object_id] = i.[object_id]

WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1

ORDER BY s.user_updates DESC OPTION (RECOMPILE); -- Order by writes

------

-- Show which indexes in the current database are most active for Writes

-- Get lock waits for current database (Query 77) (Lock Waits)

SELECT o.name AS [table_name], i.name AS [index_name], ios.index_id, ios.partition_number,

SUM(ios.row_lock_wait_count) AS [total_row_lock_waits],

SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms],

SUM(ios.page_lock_wait_count) AS [total_page_lock_waits],

SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms],

SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]

FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON ios.[object_id] = o.[object_id]

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON ios.[object_id] = i.[object_id]

AND ios.index_id = i.index_id

WHERE o.[object_id] > 100

GROUP BY o.name, i.name, ios.index_id, ios.partition_number

HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0

ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE);

------

-- This query is helpful for troubleshooting blocking and deadlocking issues

-- Look at UDF execution statistics (Query 78) (UDF Statistics)

SELECT OBJECT_NAME(object_id) AS [Function Name], execution_count,

total_worker_time, total_logical_reads, total_physical_reads, total_elapsed_time,

total_elapsed_time/execution_count AS [avg_elapsed_time],

FORMAT(cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]

FROM sys.dm_exec_function_stats WITH (NOLOCK)

WHERE database_id = DB_ID()

ORDER BY total_worker_time DESC OPTION (RECOMPILE);

------

-- New for SQL Server 2016

-- Helps you investigate scalar UDF performance issues

-- Does not return information for table valued functions

-- sys.dm_exec_function_stats (Transact-SQL)

-- https://bit.ly/2q1Q6BM

-- Determine which scalar UDFs are in-lineable (Query 79) (Inlineable UDFs)

SELECT OBJECT_NAME(m.object_id) AS [Function Name], is_inlineable, inline_type

FROM sys.sql_modules AS m WITH (NOLOCK)

LEFT OUTER JOIN sys.dm_exec_function_stats AS efs WITH (NOLOCK)

ON m.object_id = efs.object_id

WHERE efs.type_desc = N'SQL_SCALAR_FUNCTION'

OPTION (RECOMPILE);

------

-- Scalar UDF Inlining

-- https://bit.ly/2JU971M

-- sys.sql_modules (Transact-SQL)

-- https://bit.ly/2Qt216S

-- Get QueryStore Options for this database (Query 80) (QueryStore Options)

SELECT actual_state_desc, desired_state_desc, [interval_length_minutes],

current_storage_size_mb, [max_storage_size_mb],

query_capture_mode_desc, size_based_cleanup_mode_desc

FROM sys.database_query_store_options WITH (NOLOCK) OPTION (RECOMPILE);

------

-- New for SQL Server 2016

-- Requires that Query Store is enabled for this database

-- Make sure that the actual_state_desc is the same as desired_state_desc

-- Make sure that the current_storage_size_mb is less than the max_storage_size_mb

-- Tuning Workload Performance with Query Store

-- https://bit.ly/1kHSl7w

-- Emergency shutoff for Query Store (SQL Server 2019 CU6 or newer)

-- ALTER DATABASE [DatabaseName] SET QUERY_STORE = OFF(FORCED);

-- Get input buffer information for the current database (Query 81) (Input Buffer)

SELECT es.session_id, DB_NAME(es.database_id) AS [Database Name],

es.login_time, es.cpu_time, es.logical_reads, es.memory_usage,

es.[status], ib.event_info AS [Input Buffer]

FROM sys.dm_exec_sessions AS es WITH (NOLOCK)

CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib

WHERE es.database_id = DB_ID()

AND es.session_id > 50

AND es.session_id <> @@SPID OPTION (RECOMPILE);

------

-- Gives you input buffer information from all non-system sessions for the current database

-- Replaces DBCC INPUTBUFFER

-- New DMF for retrieving input buffer in SQL Server

-- https://bit.ly/2uHKMbz

-- sys.dm_exec_input_buffer (Transact-SQL)

-- https://bit.ly/2J5Hf9q

-- Get any resumable index rebuild operation information (Query 82) (Resumable Index Rebuild)

SELECT OBJECT_NAME(iro.object_id) AS [Object Name], iro.index_id, iro.name AS [Index Name],

iro.sql_text, iro.last_max_dop_used, iro.partition_number, iro.state_desc,

iro.start_time, iro.percent_complete

FROM sys.index_resumable_operations AS iro WITH (NOLOCK)

OPTION (RECOMPILE);

------

-- index_resumable_operations (Transact-SQL)

-- https://bit.ly/2pYSWqq

-- Get database automatic tuning options (Query 83) (Automatic Tuning Options)

SELECT [name], desired_state_desc, actual_state_desc, reason_desc

FROM sys.database_automatic_tuning_options WITH (NOLOCK)

OPTION (RECOMPILE);

------

-- sys.database_automatic_tuning_options (Transact-SQL)

-- https://bit.ly/2FHhLkL

-- Look at recent Full backups for the current database (Query 84) (Recent Full Backups)

SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,

CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],

CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],

CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /

CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,

DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],

bs.backup_finish_date AS [Backup Finish Date], bmf.physical_device_name AS [Backup Location], bmf.physical_block_size

FROM msdb.dbo.backupset AS bs WITH (NOLOCK)

INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)

ON bs.media_set_id = bmf.media_set_id

WHERE bs.database_name = DB_NAME(DB_ID())

AND bs.[type] = 'D' -- Change to L if you want Log backups

ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);

------

-- Things to look at:

-- Are your backup sizes and times changing over time?

-- Are you using backup compression?

-- Are you using backup checksums?

-- Are you doing copy_only backups?

-- Are you doing encrypted backups?

-- Have you done any backup tuning with striped backups, or changing the parameters of the backup command?

-- Where are the backups going to?

-- In SQL Server 2016, native SQL Server backup compression actually works

-- much better with databases that are using TDE than in previous versions

-- https://bit.ly/28Rpb2x

-- Microsoft Visual Studio Dev Essentials

-- https://bit.ly/2qjNRxi

-- Microsoft Azure Learn

-- https://bit.ly/2O0Hacc