包头整站优化,简书 wordpress,asp网站安全性,网页搭建初衷文章目录 1.缘起2.根因3.示例4.附录 1.缘起
mssql中查早阻塞与及其相关联的sql时#xff0c;遇到如下内容#xff0c;故记录一下#xff0c;
substring(dest_blocked.text,der.statement_start_offset/21,(case when der.statement_end_offset-1 then DATALENGTH(der.sta… 文章目录 1.缘起2.根因3.示例4.附录 1.缘起
mssql中查早阻塞与及其相关联的sql时遇到如下内容故记录一下
substring(dest_blocked.text,der.statement_start_offset/21,(case when der.statement_end_offset-1 then DATALENGTH(der.statement_end_offset)else der.statement_end_offset end-der.statement_start_offset)/21) as blocked_statement2.根因
statement_start_offset与statement_end_offset主要用于定位sql的起始与结束位置但是为何要/21从网路上查询得知
Its because data returned from sys.dm_exec_sql_text function is in Unicode.
1 character takes 2 bytes. The SUBSTRING works on character data types (not on bytes).
So we need to divide the number of bytes by 2 1 to have location of the first character in SQL Query that is inside text.主要是因为sys.dm_exec_sql_text返回的offset是unicode,也就是说返回的是bytes数1个字符要占用2个bytes,而substring使用character为单位取值故需要除以2
3.示例
看一下示例 主要用于返回substring取值的sql与不使用substring取值的完整sql substring取值的sql
select a as A_query,der.session_id,der.statement_start_offset,der.statement_end_offset, substring(dest.text,der.statement_start_offset/21,(case when der.statement_end_offset -1 then datalength(dest.text)
else der.statement_end_offset end - der.statement_start_offset)/21) as statement from sys.dm_exec_requests der
cross apply sys.dm_exec_sql_text(der.sql_handle) dest
union
select b as b_query,der.session_id,der.statement_start_offset,der.statement_end_offset, dest.text as statement from sys.dm_exec_requests der
cross apply sys.dm_exec_sql_text(der.sql_handle) dest
order by session_id返回如下 注意上图中的A_query字段 a 代表使用substring取值的sql b 代表不使用substring取值的完整sql
如下是返回的完整sql: substring取值的sqlsql_1)
EXECUTE [cmCriticalManufacturingODSLink]..[dbo].sp_executesql sqlcommand, Nsqlrowcnt int OUTPUT, sqlrowcntsqlrowcnt output不使用substring取值的完整sql(sql_2)
(sqlcommand nvarchar(max),sqlrowcnt As int OUTPUT)EXECUTE [cmCriticalManufacturingODSLink]..[dbo].sp_executesql sqlcommand, Nsqlrowcnt int OUTPUT, sqlrowcntsqlrowcnt output由于statement_start_offset返回是104个bytes,换算成字符就是52个字符 比对上面sql_1与sql_2,可以看到sql_1刚好从第53个字符开始截取这也就是要1的原因
4.附录
标题1中的完整sql
select dtl.resource_type,
case when dtl.resource_type in (database,file,metadata) then resource_typewhen dtl.resource_type in (object) then object_name(dtl.resource_associated_entity_id,dtl.resource_database_id) when dtl.resource_type in (key,page,rid) then (select object_name(object_id,dtl.resource_database_id) from sys.partitions where hobt_iddtl.resource_associated_entity_id)else unidentifer end as parent_object,dtl.request_mode,dtl.request_status,dowt.wait_duration_ms,dowt.wait_type,dowt.session_id as blocked_session_id,des_blocked.login_name as blocked_user,substring(dest_blocked.text,der.statement_start_offset/21,(case when der.statement_end_offset-1 then DATALENGTH(der.statement_end_offset)else der.statement_end_offset end-der.statement_start_offset)/21) as blocked_statement,dowt.blocking_session_id,der.blocking_session_id,des_blocking.login_name,dest_blocking.text,dowt.resource_descriptionfrom sys.dm_tran_locks dtl
join sys.dm_os_waiting_tasks dowt on dtl.lock_owner_addressdowt.resource_address
join sys.dm_exec_requests der on dowt.session_idder.session_id
join sys.dm_exec_sessions des_blocked on dowt.session_iddes_blocked.session_id
join sys.dm_exec_sessions des_blocking on dowt.blocking_session_iddes_blocking.session_id
join sys.dm_exec_connections dec on des_blocking.session_iddec.most_recent_session_id
cross apply sys.dm_exec_sql_text( dec.most_recent_sql_handle) dest_blocking
cross apply sys.dm_exec_sql_text( der.sql_handle) as dest_blocked
where dtl.resource_database_iddb_id() and dtl.resource_type not in (database,file)
