Significant SQL Server Queries

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)