SQL Server Performance Tuning
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 |
-- Top 10 sessions caused blocking SELECT TOP 10 r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.blocking_session_id = 0 AND r.session_id IN ( SELECT DISTINCT (blocking_session_id) FROM sys.dm_exec_requests ) GROUP BY r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text ORDER BY r.total_elapsed_time DESC -- To identify blockers and victims ;WITH Blockers AS (SELECT DISTINCT blocking_session_id AS session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0) SELECT 'Blocker' AS type_desc, sys.dm_exec_sessions.session_id, sys.dm_exec_requests.start_time, sys.dm_exec_requests.status, sys.dm_exec_requests.command, sys.dm_exec_requests.wait_type, sys.dm_exec_requests.wait_time, sys.dm_exec_requests.blocking_session_id, '' AS stmt_text FROM sys.dm_exec_sessions LEFT JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id INNER JOIN Blockers ON Blockers.session_id = sys.dm_exec_sessions.session_id UNION SELECT 'Victim' AS type_desc, sys.dm_exec_sessions.session_id, sys.dm_exec_requests.start_time, sys.dm_exec_requests.status, sys.dm_exec_requests.command, sys.dm_exec_requests.wait_type, sys.dm_exec_requests.wait_time, sys.dm_exec_requests.blocking_session_id, ST.text AS stmt_text FROM sys.dm_exec_sessions INNER JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id CROSS APPLY sys.dm_exec_sql_text(sys.dm_exec_requests.sql_handle) AS ST WHERE blocking_session_id > 0 -- Top Blocker (> 1 min blocking) SELECT w.session_id [Spid], DB_NAME(r.database_id) [DBName], s.status, w.blocking_session_id, w.wait_duration_ms, s.[program_name], s.client_interface_name, s.[host_name], s.cpu_time [cpu_time_ms], s.memory_usage * 8 / 1024 [Memory_usage], w.wait_type, r.wait_resource, s.deadlock_priority, CASE s.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'Readcomitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL, s.original_login_name, s.login_time, s.last_request_start_time, s.last_request_end_time, r.percent_complete, s.row_count, t.text, q.query_plan, aa.text [BlockingText] FROM sys.dm_os_waiting_tasks w INNER JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS q OUTER APPLY ( SELECT S2.text FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS S2 WHERE sp.spid = w.blocking_session_id ) aa WHERE s.is_user_process = 1 AND w.blocking_session_id > 0 ORDER BY w.wait_duration_ms DESC -- Real Time Blocking information SELECT DB_NAME(Blocking.dbid) AS DBName, Blocking.spid AS Blocking_SPID, CASE Blocking.blocked WHEN 0 THEN 'Head Blocker' ELSE CAST(Blocking.blocked AS NVARCHAR(20)) END AS Blocking_BlockedBy, Blocking.status AS Blocking_Status, Blocking.last_batch AS Blocking_LastBatch, Blocking.cmd AS Blocking_Command, Blocking.nt_username AS Blocking_UserName, Blocking.login_time AS Blocking_LoginTime, Blocking.program_name AS Blocking_ProgramName, Blocking.hostname AS Blocking_HostName, Blocking.hostprocess AS Blocking_HostProcess, Blocking.waittime AS Blocking_WaitTime, Blocking.waittype AS Blocking_WaitType, Blocking.lastwaittype AS Blocking_LastWaitType, Blocking.waitresource AS Blocking_WaitResource, Blocking.open_tran AS Blocking_OpenTrans, Blocking.cpu AS Blocking_CPU, Blocking.memusage AS Blocking_MemoryUsage, Blocking.physical_io AS Blocking_PhysicalIO, Blocked.spid AS Blocked_SPID, Blocked.status AS Blocked_Status, Blocked.last_batch AS Blocked_LastBatch, Blocked.cmd AS Blocked_Command, Blocked.nt_username AS Blocked_UserName, Blocked.login_time AS Blocked_LoginTime, Blocked.program_name AS Blocked_ProgramName, Blocked.hostname AS Blocked_HostName, Blocked.hostprocess AS Blocked_HostProcess, Blocked.waittime AS Blocked_WaitTime, Blocked.waittype AS Blocked_WaitType, Blocked.lastwaittype AS Blocked_LastWaitType, Blocked.waitresource AS Blocked_WaitResource, Blocked.open_tran AS Blocked_OpenTrans, Blocked.cpu AS Blocked_CPU, Blocked.memusage AS Blocked_MemoryUsage, Blocked.physical_io AS Blocked_PhysicalIO FROM sys.sysprocesses AS Blocking WITH (NOLOCK) INNER JOIN sys.sysprocesses AS Blocked WITH (NOLOCK) ON Blocking.spid = Blocked.blocked ORDER BY Blocking.blocked ASC, Blocked.blocked ASC -- Blocking Details (millisecond detection) SELECT sp.spid, sp.status, sp.hostprocess, sp.hostname, sp.loginame, sp.cpu, sp.physical_io, sp.blocked, DB_NAME(sp.dbid) AS [Database], sp.cmd, sp.program_name, sp.uid, sp.memusage, sp.waitresource, sp.waittype, sp.lastwaittype, sp.login_time, sp.last_batch, sp.open_tran, sp.hostname, sp.[sql_handle], S2.text FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS S2 WHERE spid > 50 AND sp.blocked > 0 ORDER BY cpu DESC -- select * from Sys.Dm_Exec_Sql_Text(0x0300080028881470db6f25019a9b00000100000000000000) -- Locks established in current database SELECT (CASE sys.dm_tran_locks.request_session_id WHEN -2 THEN 'ORPHANED DISTRIBUTED TRANSACTION' WHEN -3 THEN 'DEFERRED RECOVERY TRANSACTION' ELSE sys.dm_tran_locks.request_session_id END ) AS session_id, DB_NAME(sys.dm_tran_locks.resource_database_id) AS database_name, sys.objects.name AS locked_obj_name, sys.dm_tran_locks.resource_type AS locked_resource, sys.dm_tran_locks.request_mode AS lock_type, st.text AS stmt_text, sys.dm_exec_sessions.login_name AS login_name, sys.dm_exec_sessions.host_name AS host_name, sys.dm_tran_locks.request_status AS request_status FROM sys.dm_tran_locks JOIN sys.objects ON sys.objects.object_id = sys.dm_tran_locks.resource_associated_entity_id JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id JOIN sys.dm_exec_connections ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id CROSS APPLY sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle) AS st WHERE sys.dm_tran_locks.resource_database_id = DB_ID() ORDER BY sys.dm_tran_locks.request_session_id |