Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server Diagnostics
SELECT DB_NAME([database_id]) AS [Database]
,iops.[object_id] AS [ObjectID]
,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
,i.[index_id] AS [IndexID]
,i.[name] AS [IndexName]
,i.[fill_factor] AS [IndexFillFactor]
,iops.[partition_number] AS [PartitionNumber]
,CASE
WHEN i.[is_unique] = 1
THEN 'UNIQUE '
ELSE ''
END + i.[type_desc] AS [IndexType]
,iops.[row_lock_count] AS [RowLockCount]
,iops.[row_lock_wait_count] AS [RowLockWaitCount]
,CAST(100.0 * iops.[row_lock_wait_count] / (iops.[row_lock_count] + 1) AS NUMERIC(15, 2)) AS [BlockedPercent]
,iops.[row_lock_wait_in_ms] AS [RowLockWaitInMilliseconds]
,CAST(1.0 * iops.[row_lock_wait_in_ms] / (1 + iops.[row_lock_wait_count]) AS NUMERIC(15, 2)) AS [AverageRowLockWaitInMilliseconds]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
ON i.[object_id] = iops.[object_id]
AND i.[index_id] = iops.[index_id]
AND iops.[row_lock_wait_count] > 0
WHERE OBJECTPROPERTY(iops.[object_id], 'IsUserTable') = 1
ORDER BY iops.[row_lock_wait_count] DESC;
-- Query 3 – Execute to find how many times Database Engine row or page lock:
SELECT DB_NAME([database_id]) AS [Database]
,iops.[object_id] AS [ObjectID]
,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
,iops.[row_lock_count] AS [RowLockCount]
,iops.[page_lock_count] AS [PageLockCount]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
ON i.[object_id] = iops.[object_id]
AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count] DESC
--Query 2 – Execute to analyse statistics of physical I/Os on an index or heap partition:
SELECT DB_NAME([database_id]) AS [Database]
,iops.[object_id] AS [ObjectID]
,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
,i.[name] AS [IndexName]
,CASE
WHEN i.[is_unique] = 1
THEN 'UNIQUE '
ELSE ''
END + i.[type_desc] AS [IndexType]
,iops.[page_latch_wait_count] AS [PageLatchWaitCount]
,iops.[page_io_latch_wait_count] AS [PageIOLatchWaitCount]
,iops.[page_io_latch_wait_in_ms] AS [PageIOLatchWaitInMilliseconds]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
ON i.[object_id] = iops.[object_id]
AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count] DESC
CREATE PROCEDURE [dbo].[sp_who3]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
SPID = er.session_id
,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
,ElapsedMS = er.total_elapsed_time
,CPU = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,Executions = ec.execution_count
,CommandType = er.command
,LastWaitType = er.last_wait_type
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
,STATUS = ses.STATUS
,[Login] = ses.login_name
,Host = ses.host_name
,DBName = DB_Name(er.database_id)
,StartTime = er.start_time
,Protocol = con.net_transport
,transaction_isolation =
CASE ses.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
,DatetimeSnapshot = GETDATE()
,plan_handle = er.plan_handle
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
SELECT execution_count = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle = er.plan_handle
) ec
OUTER APPLY
(
SELECT
lead_blocker = 1
FROM master.dbo.sysprocesses sp
WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND sp.blocked = 0
AND sp.spid = er.session_id
) lb
WHERE er.sql_handle IS NOT NULL
AND er.session_id != @@SPID
ORDER BY
CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,
er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id
END
--Query 4 – Execute to determine number of update, insert, and delete operations against each index of the database:
SELECT DB_NAME([database_id]) AS [Database]
,iops.[object_id] AS [ObjectID]
,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
,i.[index_id] AS [IndexID]
,i.[name] AS [IndexName]
,i.[fill_factor] AS [IndexFillFactor]
,iops.[partition_number] AS [PartitionNumber]
,CASE
WHEN i.[is_unique] = 1
THEN 'UNIQUE '
ELSE ''
END + i.[type_desc] AS [IndexType]
,iops.[leaf_insert_count] AS [LeafInsertCount]
,iops.[leaf_delete_count] AS [LeafDeleteCount]
,iops.[leaf_update_count] AS [LeafUpdateCount]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
ON i.[object_id] = iops.[object_id]
AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment