SQL优化语句、sqlserver会话进程阻塞：FETCH API_CURSOR00000xx语句是什么？
jhchina1、查询当前阻塞语句
SELECT TOP 500 ds.host_name , ds.program_name , der.[session_id] , [request_id] , der.[cpu_time] , [start_time] AS '开始时间' , der.[status] AS '状态' , [command] AS '命令' , CASE WHEN statement_end_offset = -1 THEN text ELSE SUBSTRING(text, statement_start_offset / 2 + 1, ( statement_end_offset - statement_start_offset ) / 2) END AS query_text , dest.[text] AS 'sql语句' , dest.objectid , DB_NAME([database_id]) AS '数据库名' , [blocking_session_id] AS '正在阻塞其他会话的会话ID' , der.[wait_type] AS '等待资源类型' , [wait_time] AS '等待时间' , [wait_resource] AS '等待的资源' , der.[reads] AS '物理读次数' , der.[writes] AS '写次数' , der.[logical_reads] AS '逻辑读次数' , der.[row_count] AS '返回结果行数' , percent_complete FROM sys.[dm_exec_requests] AS der INNER JOIN sys.dm_exec_sessions ds ON der.session_id = ds.session_id CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) qp WHERE --[session_id]>50 and der.session_id <> @@SPID ORDER BY der.[cpu_time] DESC;
2、当前进程及其语句：
SELECT PRO.loginame AS LoginName , DB.name AS DatabaseName , PRO.[status] AS ProcessStatus , PRO.cmd AS Command , PRO.last_batch AS LastBatch , PRO.cpu AS Cpu , PRO.physical_io AS PhysicalIo , SES.row_count AS [RowCount] , STM.[text] AS SQLStatement FROM sys.sysprocesses AS PRO INNER JOIN sys.databases AS DB ON PRO.dbid = DB.dbid INNER JOIN sys.dm_exec_sessions AS SES ON PRO.spid = SES.session_id CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM WHERE PRO.spid >= 50 -- Exclude system processes ORDER BY PRO.physical_io DESC , PRO.cpu DESC;
3、查看数据库中表的语句消耗
SELECT TOP 10 s2.dbid , DB_NAME(s2.dbid) AS [数据库名] , --s1.sql_handle , ( SELECT TOP 1 SUBSTRING(s2.text, statement_start_offset / 2 + 1, ( ( CASE WHEN statement_end_offset = -1 THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2 ) ELSE statement_end_offset END ) - statement_start_offset ) / 2 + 1) ) AS [语句] , execution_count AS [执行次数] , last_execution_time AS [上次开始执行计划的时间] , total_worker_time AS [自编译以来执行所用的 CPU 时间总量（微秒）] , last_worker_time AS [上次执行计划所用的 CPU 时间（微秒）] , min_worker_time AS [单次执行期间曾占用的最小 CPU 时间（微秒）] , max_worker_time AS [单次执行期间曾占用的最大 CPU 时间（微秒）] , total_logical_reads AS [总逻辑读] , last_logical_reads AS [上次逻辑读] , min_logical_reads AS [最少逻辑读] , max_logical_reads AS [最大逻辑读] , total_logical_writes AS [总逻辑写] , last_logical_writes AS [上次逻辑写] , min_logical_writes AS [最小逻辑写] , max_logical_writes AS [最大逻辑写] FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid IS NULL ORDER BY last_worker_time DESC;
4、查看存储过程执行的语句开销大
SELECT TOP 20 DB_NAME(ISNULL(EPS.database_id, '')) [数据库名称] , OBJECT_NAME(EPS.object_id, EPS.database_id) [存储过程名称] --AS ObjectName , EPS.cached_time [添加到缓存的时间]--AS CachedTime , EPS.last_elapsed_time '最近执行所耗费时间（微秒）'--AS LastElapsedTime , EPS.last_worker_time '上次执行存储过程所用的CPU时间(微秒)' , EPS.execution_count [上次编译以来所执行的次数]--AS ExecutionCount , EPS.total_worker_time / EPS.execution_count [平均每次执行所用的CPU时间总量(微秒)]--AS AvgWorkerTime , EPS.total_elapsed_time / EPS.execution_count [平均每次执行所用的时间(微秒)]--AS AvgElapsedTime , ( EPS.total_logical_reads + EPS.total_logical_writes ) / EPS.execution_count AS AvgLogicalIO , b.text [存储过程内容] FROM sys.dm_exec_procedure_stats AS EPS CROSS APPLY sys.dm_exec_sql_text(EPS.sql_handle) b ORDER BY EPS.last_elapsed_time DESC;
5、开销较大的查询：
SELECT ss.SUM_Execution_count , t.text , ss.SUM_total_elapsed_time , ss.SUM_total_worker_time , ss.SUM_total_logical_reads , ss.SUM_total_logical_writes FROM ( SELECT s.plan_handle , SUM(s.execution_count) SUM_Execution_count , SUM(s.total_elapsed_time) SUM_total_elapsed_time , SUM(s.total_worker_time) SUM_total_worker_time , SUM(s.total_logical_reads) SUM_total_logical_reads , SUM(s.total_logical_writes) SUM_total_logical_writes FROM sys.dm_exec_query_stats s GROUP BY s.plan_handle ) AS ss CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t ORDER BY SUM_total_logical_reads DESC;
查看SQLServer最耗资源时间的SQL语句
SELECT (total_elapsed_time / execution_count) / 1000 N'平均时间ms' , total_elapsed_time / 1000 N'总花费时间ms' , total_worker_time / 1000 N'所用的CPU总时间ms' , total_physical_reads N'物理读取总次数' , total_logical_reads / execution_count N'每次逻辑读次数' , total_logical_reads N'逻辑读取总次数' , total_logical_writes N'逻辑写入总次数' , execution_count N'执行次数' , SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1 , ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) N'执行语句' , creation_time N'语句编译时间' , last_execution_time N'上次执行时间' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) not like 'fetch%' ORDER BY total_elapsed_time / execution_count DESC;
sqlserver会话显示在执行的FETCH API_CURSOR00000xx语句是什么？
通过常用的两种方式，都没有看到原始SQL脚本
select t.spid, t.kpid, t.blocked, t.status, t.lastwaittype, t.waitresource, t.waittime, t.open_tran , DB_NAME(t.dbid) DbName, t.last_batch, t.loginame, t.program_name, t.hostname, t.hostprocess , t.cmd, t.stmt_start, t.stmt_end, t.request_id, dc.text from master.sys.sysprocesses t outer apply master.sys.dm_exec_sql_text(t.sql_handle) dc where t.spid = 53
SELECT t.text FROM sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t WHERE session_id = 53
通过DBCC INPUTBUFFER(spid)，返回结果也是：FETCH API_CURSOR00000xx
那么还有没有其他的视图来帮助我们呢？我们可以使用sys.dm_exec_cursors视图，将spid代入进去
SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text FROM sys.dm_exec_cursors(53) c CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
文章最后编辑时间：2023-10-02 08:25:37
