Converting XML to Table
WITH cte
AS (SELECT Cast(expression AS XML) AS Expression
FROM [table name]
WHERE id = 109)
SELECT p.value('@targetField', 'varchar(max)') AS TargetField,
p.value('@sourceExpression', 'varchar(max)') AS SourceField
FROM cte
CROSS apply expression.nodes('/parentNode/childNode') t(p)
Check which all Tables contains a particular column
SELECT t.NAME AS table_name,
Schema_name(schema_id) AS schema_name,
c.NAME AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
WHERE c.NAME LIKE '%ColumnName%'
ORDER BY schema_name,
table_name
Cached query inspector
The purpose of this query is to check the query plan cache for large queries
and gives you the execution plan for each query.
SELECT refcounts AS 'Reference_Counts',
size_in_bytes AS 'Size_in_bytes',
cacheobjtype AS 'Cache_type',
st.encrypted AS 'Is_encrypted_text',
text AS 'SQL_text',
query_plan AS 'Query_plan'
FROM sys.dm_exec_cached_plans cp WITH (nolock)
CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) st
CROSS apply sys.Dm_exec_query_plan(cp.plan_handle) qp
ORDER BY refcounts DESC,
size_in_bytes DESC
Current Locks with Execution Plan
The purpose of this query is to see what queries are currently holding locks and gives you the execution plan for the blocking and executing queries.
SELECT Db_name(locks.resource_database_id) AS 'Database',
locks.request_status AS 'Status',
bs.host_name AS 'Blocking_host',
bs.nt_domain + '\' + bs.nt_user_name AS 'Blocking_user',
tasks.wait_duration_ms / 1000. AS 'Current_wait_duration_seconds',
blockText.text AS 'Blocking_sql_text',
blockPlan.query_plan AS 'Blocking_query_plan',
blockText.text AS 'Executing_sql_text',
blockPlan.query_plan AS 'Executing_query_plan',
locks.resource_type AS 'Resource_type',
locks.resource_subtype AS 'Resource_sub_type',
locks.resource_description AS 'Resource_description',
locks.request_mode AS 'Request_mode',
locks.request_type AS 'Request_type',
tasks.wait_type AS 'Wait_type'
FROM sys.dm_tran_locks locks WITH (nolock)
JOIN sys.dm_os_waiting_tasks tasks WITH (nolock)
ON locks.lock_owner_address = tasks.resource_address
JOIN sys.dm_exec_sessions bs WITH (nolock)
ON tasks.blocking_session_id = bs.session_id -- Blocking session
JOIN sys.dm_exec_requests br WITH (nolock)
ON br.session_id = tasks.blocking_session_id -- Blocking request
JOIN sys.dm_exec_sessions cs WITH (nolock)
ON tasks.session_id = cs.session_id -- Current session
JOIN sys.dm_exec_requests cr WITH (nolock)
ON cr.session_id = tasks.session_id -- Current request
CROSS apply sys.Dm_exec_sql_text(br.sql_handle) blockText
CROSS apply sys.Dm_exec_query_plan(br.plan_handle) blockPlan
CROSS apply sys.Dm_exec_sql_text(cr.sql_handle) currText
CROSS apply sys.Dm_exec_query_plan(cr.plan_handle) currPlan
Currently running queries
The purpose of this query is to check to see what queries are currently running and gives you the execution plan for each query. It ignores any query that is on the current SQL connection.
SELECT req.session_id AS 'Session_ID',
Db_name(req.database_id) AS 'Database_Name',
ses.host_name AS 'Executing_Host',
ses.nt_domain + '\' + ses.nt_user_name AS 'Executing_User',
req.command AS 'Command_Type',
req.status AS 'Command_Status',
ses.deadlock_priority AS 'Deadlock_Priority',
req.cpu_time AS 'CPU_Time',
req.total_elapsed_time AS 'Elapsed_Time',
sqltext.text AS 'SQL_Text',
query_plan AS 'Query_plan'
FROM sys.dm_exec_requests req WITH (nolock)
JOIN sys.dm_exec_sessions ses WITH (nolock)
ON req.session_id = ses.session_id
CROSS apply sys.Dm_exec_sql_text(sql_handle) sqltext
CROSS apply sys.Dm_exec_query_plan(plan_handle) qp
WHERE req.session_id <> @@SPID
ORDER BY req.total_elapsed_time DESC,
req.cpu_time DESC
Virtual file statistics
- For data files, MS recommend: < 10 ms = Good | 10 - 20 ms = Acceptable | > 20 ms = Unacceptable
- For log files, MS recommend: < 5 ms = Good | 5 - 15 ms = Acceptable | > 15 ms = Unacceptable
SELECT Db_name(database_id) AS 'Database_Name',
CASE
WHEN file_id = 2 THEN 'Log'
ELSE 'Data'
END AS 'File_Type',
( ( size_on_disk_bytes / 1024 ) / 1024.0 ) AS 'Size_On_Disk_in_MB',
io_stall_read_ms / num_of_reads AS 'Avg_Read_Transfer_in_Ms',
CASE
WHEN file_id = 2 THEN
CASE
WHEN io_stall_read_ms / num_of_reads < 5 THEN 'Good'
WHEN io_stall_read_ms / num_of_reads < 15 THEN 'Acceptable'
ELSE 'Unacceptable'
END
ELSE
CASE
WHEN io_stall_read_ms / num_of_reads < 10 THEN 'Good'
WHEN io_stall_read_ms / num_of_reads < 20 THEN 'Acceptable'
ELSE 'Unacceptable'
END
END AS 'Average_Read_Performance',
io_stall_write_ms / num_of_writes AS 'Avg_Write_Transfer_in_Ms',
CASE
WHEN file_id = 2 THEN
CASE
WHEN io_stall_write_ms / num_of_writes < 5 THEN 'Good'
WHEN io_stall_write_ms / num_of_writes < 15 THEN 'Acceptable'
ELSE 'Unacceptable'
END
ELSE
CASE
WHEN io_stall_write_ms / num_of_writes < 10 THEN 'Good'
WHEN io_stall_write_ms / num_of_writes < 20 THEN 'Acceptable'
ELSE 'Unacceptable'
END
END AS 'Average_Write_Performance'
FROM sys.Dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads > 0
AND num_of_writes > 0
Wait statistics finder
WITH waits
AS (SELECT wait_type AS 'Wait_type',
wait_time_ms / 1000.0 AS 'Wait_time_seconds',
100.0 * wait_time_ms / Sum(wait_time_ms)
OVER() AS 'Percent_of_results',
Row_number()
OVER(
ORDER BY wait_time_ms DESC) AS 'Row_number'
FROM sys.dm_os_wait_stats WITH (nolock)
WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
'WAITFOR',
'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH',
'XE_TIMER_EVENT',
'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE'
,
'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT',
'XE_DISPATCHER_JOIN',
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER',
'SLEEP_BPOOL_FLUSH' ))
SELECT W1.wait_type AS 'Wait_Type',
Cast(W1.wait_time_seconds AS DECIMAL(12, 2)) AS 'Wait_time_seconds'
,
Cast(W1.percent_of_results AS DECIMAL(12, 2)) AS
'Percent_of_results',
Cast(Sum(W2.percent_of_results) AS DECIMAL(12, 2)) AS
'Running_percentage'
FROM waits AS W1
INNER JOIN waits AS W2
ON W2.[row_number] <= W1.[row_number]
GROUP BY W1.[row_number],
W1.wait_type,
W1.wait_time_seconds,
W1.percent_of_results
HAVING Sum(W2.percent_of_results) - W1.percent_of_results < 99
OPTION (recompile)
