合 MSSQL如何获取存储过程中正在执行的真实SQL语句(定位存储过程中最耗时部分)--SQL Server Profiler
Tags： MSSQLSQL Server整理自网络小麦苗常用存储过程慢SQLSQL语句性能排查正在执行SQL Server Profiler
👉 本文共约2220个字，系统预计阅读时间或需9分钟。
简介
SQL Server存储过程优化的重点和难点在于如何找到存储过程中最耗时的部分。尤其对于很长的存储过程，或者多层嵌套调用的存储过程，难度会更大。
需求
1、SQL server如何找到存储过程中的正在执行的真实的sql语句?????
2、如何定位存储过程中最耗时的SQL语句？？？
模拟存储过程一直在运行：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | drop PROCEDURE LongRunningProcedure; go CREATE PROCEDURE LongRunningProcedure AS BEGIN SET NOCOUNT ON; DECLARE @Counter INT = 0; WHILE @Counter < 1000000 BEGIN -- Perform complex queries and logic here WAITFOR DELAY '00:00:05'; -- Wait for 5 seconds WAITFOR DELAY '00:00:06'; SET @Counter = @Counter + 1; END END LongRunningProcedure |
方法1：直接查询
以下SQL的internal_SQL_TEXT就是存储过程真实的内部SQL，而Parent_SQL_TEXT就是存储过程全部内容。
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 | SELECT top 1000 (CASE WHEN ActualElapsedTime < 36000 THEN '0' ELSE '' END + RTRIM(ActualElapsedTime/3600) + ':' + RIGHT('0' + RTRIM((ActualElapsedTime)%3600/60), 2) + ':' + RIGHT('0' + RTRIM((ActualElapsedTime)%60), 2)) ActualElapsedTime, a.wait_time2, a.spid, a.thread_cnt, -- a.dop, blocking_session_id blocking_spid, a.database_id, db_name(a.database_id) dbname, start_time, sp.last_batch, wait_type, last_wait_type, wait_resource, open_transaction_count, a.status, a.command, a.total_elapsed_time/1000 total_elapsed_time_s, a.cpu_time/1000 cpu_time, a.writes, a.logical_reads, sp.physical_io, a.row_count, a.granted_query_memory*8/1024 granted_query_memory_MB, a.percent_complete, a.Time_Remaining, -- a.sql_handle, SUBSTRING (b.text,a.statement_start_offset/2, (CASE WHEN a.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), b.text)) * 2+ 2 ELSE a.statement_end_offset END - a.statement_start_offset)/2+ 2) Internal_SQL_TEXT, b.text Parent_SQL_TEXT, -- a.plan_handle, -- c.query_plan, (select query_plan from sys.dm_exec_query_plan(a.plan_handle) n1) query_plan, 'kill '+cast(a.spid as varchar) kill1 FROM ( select nb.session_id spid, nb.total_elapsed_time/1000 total_elapsed_time_s, (CASE WHEN estimated_completion_time < 36000000 THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2)) AS [Time_Remaining], (SELECT count(*) FROM master.dbo.sysprocesses sp where sp.spid=nb.session_id) thread_cnt, DATEDIFF(SECOND, nb.start_time, getdate()) ActualElapsedTime, (CASE WHEN wait_time < 36000000 THEN '0' ELSE '' END + RTRIM(wait_time/1000/3600) + ':' + RIGHT('0' + RTRIM((wait_time/1000)%3600/60), 2) + ':' + RIGHT('0' + RTRIM((wait_time/1000)%60), 2)) AS wait_time2, nb.* from sys.dm_exec_requests nb WITH(NOLOCK) ) a LEFT JOIN master.dbo.sysprocesses sp WITH(NOLOCK) on sp.spid=a.spid and sp.ecid=0 LEFT JOIN ( SELECT n1.session_id ,n2.text FROM sys.dm_exec_requests n1 WITH(NOLOCK) CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) b on a.spid=b.session_id WHERE a.spid <> @@spid and (a.status not in ('sleeping','background') or (a.status in ('sleeping','background') and a.spid IN (select blocking_session_id from sys.dm_exec_requests nb)) or (a.status in ('sleeping','background') and a.open_transaction_count > 0 ) or ( a.status in ('background') and a.command='DB STARTUP' ) ) -- and a.spid in (141,135) order by cast(ActualElapsedTime as numeric) desc GO |
方法2：查看执行计划
存储过程可能包含单个查询，也可能包含整个查询系列。
在后一种情况下，您将看到多个执行计划，但是处理每个计划的方式与其他任何执行计划没有区别。
在运行存储过程的时候，勾选“包括实际的执行计划（Ctrl+M）”，在执行完后就会显示存储过程中的所有语句的执行计划和花费的时间比例，这在调试慢的存储过程时非常有用。 也可以不执行存储过程而使用“显示预估的执行计划（Ctrl+L）”来预估一下存储过程中的所有语句的执行计划
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
