合 MSSQL如何获取存储过程中正在执行的真实SQL语句(定位存储过程中最耗时部分)--SQL Server Profiler

发布日期 2023年4月21日 · 已更新 2024年5月15日

0 185 5

Tags： MSSQL SQL Server 整理自网络小麦苗常用存储过程慢SQL 性能排查 SQL语句正在执行 SQL Server Profiler

👉 本文共约2258个字，系统预计阅读时间或需9分钟。

简介
需求
方法1：直接查询
方法2：查看执行计划
方法3：SQL Server Profiler（推荐）
方法4：查存储过程中各部分sql执行时间
方法5：写详细日志
方法6：版本大于2016可以使用查询存储（ qsq.object_id就是存储过程的名称）
总结
参考

简介

SQL Server存储过程优化的重点和难点在于如何找到存储过程中最耗时的部分。尤其对于很长的存储过程，或者多层嵌套调用的存储过程，难度会更大。

需求

1、SQL server如何找到存储过程中的正在执行的真实的sql语句?????

2、如何定位存储过程中最耗时的SQL语句？？？

模拟存储过程一直在运行：

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

drop PROCEDURE LongRunningProcedure;

CREATE PROCEDURE LongRunningProcedure

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

LongRunningProcedure

方法1：直接查询

以下SQL的internal_SQL_TEXT就是存储过程真实的内部SQL，而Parent_SQL_TEXT就是存储过程全部内容。


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

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

方法2：查看执行计划

存储过程可能包含单个查询，也可能包含整个查询系列。

在后一种情况下，您将看到多个执行计划，但是处理每个计划的方式与其他任何执行计划没有区别。

在运行存储过程的时候，勾选“包括实际的执行计划（Ctrl+M）”，在执行完后就会显示存储过程中的所有语句的执行计划和花费的时间比例，这在调试慢的存储过程时非常有用。也可以不执行存储过程而使用“显示预估的执行计划（Ctrl+L）”来预估一下存储过程中的所有语句的执行计划

方法3：SQL Server Profiler（推荐）

路径：工具--》SQL Server Profiler

通过 SQL Server Management Studio，可以使用 SQL Server Profiler 来监视数据库活动。在 SQL Server Profiler 中，选择 "TSQL_SPs" 模板，建议保存到表中，选择事件和需要的列，然后选择spid列，然后启动跟踪会话。在跟踪结果中，可以查看每个存储过程的执行情况，以及存储过程中执行的实际 SQL 语句。

勾选显示所有列，一定要有Duration列，表示运行时间

勾选显示所有事件，然后设置事件：

a、Stored Procedures下的第2个和倒数第2个选项：

RPC:Completed
SP:StmtCompleted

b、TSQL下的第3、4、5、6、7个：

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(5)

标签： MSSQL SQL Server 整理自网络小麦苗常用存储过程慢SQL 性能排查 SQL语句正在执行 SQL Server Profiler

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合 MSSQL如何获取存储过程中正在执行的真实SQL语句(定位存储过程中最耗时部分)--SQL Server Profiler

简介

需求

方法1：直接查询

方法2：查看执行计划

方法3：SQL Server Profiler（推荐）

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				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

合 MSSQL如何获取存储过程中正在执行的真实SQL语句(定位存储过程中最耗时部分)--SQL Server Profiler

简介

需求

方法1：直接查询

方法2：查看执行计划

方法3：SQL Server Profiler（推荐）

相关文章

您可能还喜欢...

MSSQL变更数据捕获（CDC）和 更改跟踪（Chang Tracking）介绍

Linux和docker上的 SQL Server 的常见问题解答 (FAQ)

无法还原此差异备份，因为该数据库尚未还原到正确的早期状态

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

MSSQL变更数据捕获（CDC）和更改跟踪（Chang Tracking）介绍

发表回复取消回复