December 17, 2010 at 3:15 am
Hello,
I would like to know if it is possible to capture the actual SQL executed against the database where it appears to be different from the SQL shown in the input buffer. This is in an effort to alleviate a major blocking-locks problem.
I'll explain by way of example (incidentally, this is MS CRM, although that's not really relevant to the problem).
Once I have identified the offending blocking process, if I examine the trace Blocked Process report, I can see the following:
<inputbuf>
(@DeletionStateCode0 int,@ModifiedOn0 datetime,@PartyId0 uniqueidentifier)
select DISTINCT top 51 email0.ModifiedOn as 'modifiedon', email0.MessageId as
'messageid', email0.ActivityId as 'activityid', email0.RegardingObjectId as
'regardingobjectid', email0.RegardingObjectIdYomiName as
'regardingobjectidyominame', email0.RegardingObjectIdName as
'regardingobjectidname', email0.RegardingObjectTypeCode as
'regardingobjecttypecode', email0.RegardingObjectIdDsc as
'regardingobjectiddsc' from Email as email0 join ActivityParty as activityparty0 on
(email0.ActivityId = activityparty0.ActivityId and ((activityparty0.PartyId = @PartyId0))) where
((email0.DeletionStateCode in (@DeletionStateCode0)) and ( email0.ModifiedOn >= @ModifiedOn0
and email0.MessageId is not null)) order by email0.MessageId asc
</inputbuf>
which is unsurprising exactly the same result as when I run DBCC INPUTBUFFER () against the spid in question.
However, in a simultaneous trace against the spid, the RPC:Starting/Completed event against the spid shows this parameterised query as executed, e.g.:
EXEC Sp_executesql
N'select DISTINCT top 51 email0.ModifiedOn as ''modifiedon'', email0.MessageId as ''messageid'', email0.ActivityId as ''activityid'', email0.RegardingObjectId as ''regardingobjectid'', email0.RegardingObjectIdYomiName as ''regardingobjectidyominame'', email0.RegardingObjectIdName as ''regardingobjectidname'', email0.RegardingObjectTypeCode as ''regardingobjecttypecode'', email0.RegardingObjectIdDsc as ''regardingobjectiddsc'' from Email as email0 join ActivityParty as activityparty0 on (email0.ActivityId = activityparty0.ActivityId and ((activityparty0.PartyId = @PartyId0))) where ((email0.DeletionStateCode in (@DeletionStateCode0)) and ( email0.ModifiedOn >= @ModifiedOn0 and email0.MessageId is not null)) order by email0.MessageId asc'
,
N'@DeletionStateCode0 int,@ModifiedOn0 datetime,@PartyId0 uniqueidentifier',
@DeletionStateCode0=0,
@ModifiedOn0='2010-06-14 23:00:00',
@PartyId0='14DC358B-C43C-DF11-B069-00155D289860'
So I can actually see the values in the parameters that have hit the database. My question is, how (or if?!) I can get part 2 (the actual code) somehow directly from a SQL Script rather than via a trace?
Thanks in advance.
December 17, 2010 at 4:19 am
I don't think it can be done, but I would be greatly interested in others' thoughts.
-- Gianluca Sartori
October 19, 2011 at 1:00 pm
hi,
We are also getting this query running behind in CRM and taking high I/o.
What i found on net is this link
http://social.microsoft.com/Forums/en/crm/thread/df4a5f89-e52c-41b2-ab71-69ec1303172a
Still trying to figure out the solution to this, Let me know if you have reached to any conclusion.
Thanks
Richa
October 20, 2011 at 7:21 am
I don't know of a way to do this, but if you have an already-created trace on the server, you can start/stop the trace with sp_trace_setstatus and load the trace results into a table for querying using fn_trace_gettable.
Rich
October 22, 2011 at 1:56 am
This script will find a Head blocking script and in the temporary table all scripts running is shown.
Only tested in SQL 2008
/*
Check for the SPID that has a BlockingSPID set to zero (0). Thats the one to kill
Kill 167
*/
SELECT
s.spid
,BlockingSPID = s.blocked
,s.loginame
,DatabaseName = DB_NAME(s.dbid)
,[Host Name]= ISNULL(p.host_name, N'')
,LastBatch = s.last_batch
,Definition = CAST(text AS VARCHAR(MAX))
,p.cpu_time
,s.program_name
,ObjectName = OBJECT_NAME(objectid,s.dbid)
INTO#Processes
FROMsys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (s.sql_handle)
LEFT OUTER JOIN sys.dm_exec_sessions p ON p.session_id = s.spid
WHEREs.loginame <> 'psqldba'
AND s.spid > 50
--AND s.loginame = 'dynamo'
--AND p.host_name = 'SENWK-VS-EADM'
--AND DB_NAME(s.dbid) LIKE 'ATG_%'
ORDER BYs.blocked DESC, s.last_batch DESC
GO
WITH Blocking(SPID
,BlockingSPID
,loginame
,DatabaseName
,[Host Name]
,LastBatch
,BlockingStatement
,cpu_time
,RowNo
,LevelRow)
AS
(
SELECT
s.SPID
,s.BlockingSPID
,s.loginame
,s.DatabaseName
,s.[Host Name]
,s.LastBatch
,s.Definition
,s.cpu_time
,ROW_NUMBER() OVER(ORDER BY s.SPID)
,0 AS LevelRow
FROM
#Processes s
JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID
,r.BlockingSPID
,r.loginame
,r.DatabaseName
,r.[Host Name]
,r.LastBatch
,r.Definition
,r.cpu_time
,d.RowNo
,d.LevelRow + 1
FROM
#Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
SELECT*
FROM#Processes
DROP TABLE #Processes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply