我有一个几天前编写的SQL脚本,通过使用一些DMV/DMF和.sys表(主要来自sys.dm_exec_query_stats)来查找CPU密集型查询。但是,为了确定在某个时间点执行的确切 SQL 语句,我在 SELECT 语句中使用以下语句(可在 BOL 和各种博客中找到):
,SUBSTRING(t.text,s.statement_start_offset/2 +1,
(CASE WHEN s.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE s.statement_end_offset END -
s.statement_start_offset)/2) AS 'TSQL-Query'
有人可以解释为什么"s.statement_start_offset"首先除以二,然后再除以一吗?还有为什么 LEN() 语句乘以 2?
我在许多博客和 MSDN 上看到这些示例,但我似乎找不到这些细节。
我还在下面包含了我的整个脚本,以便您获得更大的图景。
SELECT TOP 20
COALESCE(DB_NAME(t.dbid),
DB_NAME(CAST(a.value as int))) AS DBNAME
,SUBSTRING(t.text,s.statement_start_offset/2 +1,
(CASE WHEN s.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE s.statement_end_offset END -
s.statement_start_offset)/2)
AS 'TSQL-Query'
,s.execution_count AS 'Execution Count'
,s.total_worker_time AS 'Total CPU'
,s.total_worker_time/s.execution_count AS 'Avg CPU (ms)'
,s.total_physical_reads AS 'Total Physical Reads'
,s.total_physical_reads/s.execution_count AS 'Avg Physical Reads'
,s.total_logical_reads AS 'Total Logical Reads'
,s.total_logical_reads/s.execution_count AS 'Avg Logical Reads'
,s.total_logical_writes AS 'Total Logical Writes'
,s.total_logical_writes/s.execution_count AS 'Avg Logical Writes'
,s.total_elapsed_time AS 'Total Duration (ms)'
,s.total_elapsed_time/s.execution_count AS 'Avg Duration/execution (ms)'
,p.query_plan AS 'ExecutionPlan'
,(SELECT sqlserver_start_time FROM sys.dm_os_sys_info) as 'Last Reboot'
,GETDATE() AS 'TimeStamp'
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS a
WHERE attribute = 'dbid'
ORDER BY s.total_worker_time DESC
这是因为从函数返回的数据是 Unicode sys.dm_exec_sql_text
。 1 个字符需要 2 个字节。该SUBSTRING
适用于字符数据类型(而不是字节)。因此,我们需要将字节数除以 2 + 1,以获得 SQL 查询中第一个字符的位置,该字符位于 text
内。