SQL Tuning query

Unused Index
SELECT
OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM
sys.indexes AS i
INNER JOIN
sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE
i.index_id NOT IN ( SELECT ddius.index_id FROM sys.dm_db_index_usage_stats AS ddius WHERE ddius.[object_id] = i.[object_id] AND i.index_id = ddius.index_id AND database_id = DB_ID() )
AND
o.[type] = 'U'
ORDER BY
OBJECT_NAME(i.[object_id]) ASC;

Missing Index

SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS [index_advantage] ,
dbmigs.last_user_seek ,
dbmid.[statement] AS [Database.Schema.Table] ,
dbmid.equality_columns ,
dbmid.inequality_columns ,
dbmid.included_columns ,
dbmigs.unique_compiles ,
dbmigs.user_seeks ,
dbmigs.avg_total_user_cost ,
dbmigs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK )
ON dbmigs.group_handle = dbmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK )
ON dbmig.index_handle = dbmid.index_handle
WHERE dbmid.[database_id] = DB_ID()
ORDER BY index_advantage DESC;

High I/O Procedure

SELECT
p.name AS [SP Name],
deps.cached_time,
deps.total_logical_reads AS [TotalLogicalReads],
deps.total_logical_writes as [TotalLogicalWrites],
deps.total_physical_reads as [TotalPhysicalReads],
(deps.total_logical_reads + deps.total_logical_writes + deps.total_physical_reads) as [Total_IO_Impact],
deps.total_elapsed_time as [TotalElapsedTime],
deps.execution_count as [ExecutionCount]
FROM
sys.procedures AS p
INNER JOIN
sys.dm_exec_procedure_stats AS deps
ON
p.[object_id] = deps.[object_id]
WHERE
deps.database_id = DB_ID()
ORDER BY
(deps.total_logical_reads + deps.total_logical_writes + deps.total_physical_reads) DESC;

--TOP 10 Highest IO Statements
SELECT
CASE WHEN deqs.statement_start_offset = 0 AND deqs.statement_end_offset = -1 THEN '-- see objectText column--' ELSE '-- query --' + CHAR(13) + CHAR(10) + SUBSTRING(execText.text, deqs.statement_start_offset / 2, ( ( CASE WHEN deqs.statement_end_offset = -1 THEN DATALENGTH(execText.text) ELSE deqs.statement_end_offset END ) - deqs.statement_start_offset ) / 2) END AS queryText,
deqs.total_logical_reads AS [TotalLogicalReads],
deqs.total_logical_writes as [TotalLogicalWrites],
deqs.total_physical_reads as [TotalPhysicalReads],
(deqs.total_logical_reads + deqs.total_logical_writes + deqs.total_physical_reads) as [Total_IO_Impact],
deqs.total_elapsed_time as [TotalElapsedTime],
deqs.execution_count as [ExecutionCount]
FROM
sys.dm_exec_query_stats deqs
CROSS APPLY
sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY
(deqs.total_logical_reads + deqs.total_logical_writes + deqs.total_physical_reads) DESC ;

SELECT TOP 10 total_worker_time,
execution_count,
total_worker_time / execution_count AS [Avg CPU Time],
CASE WHEN deqs.statement_start_offset = 0 AND deqs.statement_end_offset = - 1
THEN '-- see objectText column--' ELSE '-- query --' + CHAR(13) +
CHAR(10) + SUBSTRING(execText.TEXT, deqs.statement_start_offset
/ 2, (
(
CASE WHEN deqs.statement_end_offset = - 1 THEN
DATALENGTH(execText.TEXT) ELSE
deqs.statement_end_offset END
) - deqs.statement_start_offset
) / 2) END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC;

High Scan Index
SELECT TOP 10
OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] ,
ddius.index_id ,
ddius.user_seeks ,
ddius.user_scans ,
ddius.user_lookups ,
ddius.user_seeks + ddius.user_scans + ddius.user_lookups AS user_reads,
ddius.user_updates AS user_writes ,
ddius.last_user_scan ,
ddius.last_user_update
FROM
sys.dm_db_index_usage_stats ddius
WHERE
ddius.database_id > 4 -- filter out system tables
AND
OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
AND
ddius.index_id > 0 -- filter out heaps
ORDER BY
ddius.user_scans DESC;

Over updated Index
SELECT OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;

Lock Contention

SELECT OBJECT_NAME(ddios.object_id, ddios.database_id) AS object_name ,
i.name AS index_name ,
ddios.index_id ,
ddios.partition_number ,
ddios.page_lock_wait_count ,
ddios.page_lock_wait_in_ms ,
CASE WHEN DDMID.database_id IS NULL THEN 'N'
ELSE 'Y'
END AS missing_index_identified
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
AND ddios.index_id = i.index_id
LEFT OUTER JOIN ( SELECT DISTINCT
database_id ,
object_id
FROM sys.dm_db_missing_index_details
) AS DDMID ON DDMID.database_id = ddios.database_id
AND DDMID.object_id = ddios.object_id
WHERE ddios.page_lock_wait_in_ms > 0
ORDER BY ddios.page_lock_wait_count DESC ;

Lock Escalation

SELECT OBJECT_NAME(ddios.[object_id], ddios.database_id) AS [object_name] ,
i.name AS index_name ,
ddios.index_id ,
ddios.partition_number ,
ddios.index_lock_promotion_attempt_count ,
ddios.index_lock_promotion_count ,
( 1.0 * ddios.index_lock_promotion_count
/ ddios.index_lock_promotion_attempt_count ) AS percent_success
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
AND ddios.index_id = i.index_id
WHERE ddios.index_lock_promotion_count > 0
ORDER BY index_lock_promotion_count DESC ;

Locking and Blocking

SELECT
'[' + DB_NAME(ddios.[database_id]) + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
i.[name] AS 'index_name' ,
ddios.[partition_number] ,
ddios.[row_lock_count] ,
ddios.[row_lock_wait_count] ,
CAST (100.0 * ddios.[row_lock_wait_count] / ( ddios.[row_lock_count] ) AS DECIMAL(5, 2)) AS [%_times_blocked] ,
ddios.[row_lock_wait_in_ms] ,
CAST (1.0 * ddios.[row_lock_wait_in_ms]
/ ddios.[row_lock_wait_count] AS DECIMAL(15, 2))
AS [avg_row_lock_wait_in_ms]
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]
AND i.[index_id] = ddios.[index_id]
INNER JOIN sys.objects o ON ddios.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE ddios.row_lock_wait_count > 0
AND OBJECTPROPERTY(ddios.[object_id], 'IsUserTable') = 1
AND i.[index_id] > 0
ORDER BY [avg_row_lock_wait_in_ms] DESC

Letch Wait

SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddios.[object_id])
+ '].[' + OBJECT_NAME(ddios.[object_id]) + ']' AS [object_name] ,
i.[name] AS index_name ,
ddios.page_io_latch_wait_count ,
ddios.page_io_latch_wait_in_ms ,
( ddios.page_io_latch_wait_in_ms / ddios.page_io_latch_wait_count )
AS avg_page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]
AND i.index_id = ddios.index_id
WHERE ddios.page_io_latch_wait_count > 0
AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ddios.page_io_latch_wait_count DESC ,
avg_page_io_latch_wait_in_ms DESC;