What is going on behind the hood of SP_EXECUTESQL??

Question

Post reply

What is going on behind the hood of SP_EXECUTESQL??

Afolabi Adeleke-Adedoyin

SSC Journeyman

Points: 97
More actions
September 27, 2005 at 12:39 pm

#95349

Hello *,
I know that sp_executesql executes dynamic t-sql, however, is there a way to look at what sproc or statements actually executed? The observerd sp_executesql repeatedly deadlocks with another sproc, hence I would like to get more insight as to what is going on behind the hood of sp_executesql!!!
Thanks,

Fola

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

sp_executesql is exactly the same as sending an unparsed query from query analyzer.

It is parsed, normalized, compiled, optimized, and executed.

I would doubt its the sp_executesql method, but rather the query your executing thats causing the problem

Substitute sp_executesql with Execute(@sqlstring) and see if you still have your problem

Afolabi Adeleke-Adedoyin SSC Journeyman Points: 97 More actions · Answer 2

Thanks, I am confused about your reply though...I am not executing a query. I am reading from an error log similar to:-

Deadlock encountered .... Printing deadlock information

2005-09-22 07:28:23.60 spid2

2005-09-22 07:28:23.60 spid2 Wait-for graph

2005-09-22 07:28:23.60 spid2

2005-09-22 07:28:23.60 spid2 Node:1

2005-09-22 07:28:23.60 spid2 KEY: 4:1333253162:1 (3c023f8ce5f7) CleanCnt:1 Mode: U Flags: 0x0

2005-09-22 07:28:23.60 spid2 Grant List 2::

2005-09-22 07:28:23.60 spid2 Owner:0x59e554e0 Mode: S Flg:0x0 Ref:1 Life:02000000 SPID:173 ECID:0

2005-09-22 07:28:23.60 spid2 SPID: 173 ECID: 0 Statement Type: UPDATE Line #: 61

2005-09-22 07:28:23.60 spid2 Input Buf: RPC Event: AddTime;1

2005-09-22 07:28:23.60 spid2 Grant List 3::

2005-09-22 07:28:23.60 spid2 Requested By:

2005-09-22 07:28:23.60 spid2 ResType:LockOwner Stype:'OR' Mode: X SPID:75 ECID:0 Ec0x5E38F558) Value:0x28f06f20 Cost0/687AF60)

2005-09-22 07:28:23.60 spid2

2005-09-22 07:28:23.60 spid2 Node:2

2005-09-22 07:28:23.60 spid2 TAB: 4:1333253162 [] CleanCnt:1 Mode: SIX Flags: 0x0

2005-09-22 07:28:23.60 spid2 Grant List 2::

2005-09-22 07:28:23.60 spid2 Grant List 3::

2005-09-22 07:28:23.60 spid2 Owner:0x74681ce0 Mode: SIX Flg:0x0 Ref:2 Life:02000000 SPID:75 ECID:0

2005-09-22 07:28:23.60 spid2 SPID: 75 ECID: 0 Statement Type: UPDATE Line #: 8

2005-09-22 07:28:23.60 spid2 Input Buf: RPC Event: sp_executesql;1

2005-09-22 07:28:23.60 spid2 Requested By:

2005-09-22 07:28:23.60 spid2 ResType:LockOwner Stype:'OR' Mode: IX SPID:173 ECID:0 Ec0x0B511558) Value:0x762f2ac0 Cost0/1B70)

2005-09-22 07:28:23.60 spid2 Victim Resource Owner:

2005-09-22 07:28:23.60 spid2 ResType:LockOwner Stype:'OR' Mode: IX SPID:173 ECID:0 Ec0x0B511558) Value:0x762f2ac0 Cost0/1B70)

That is the root of the problem...how to see what dynamic t-sql is executing? I am thinking it might be a sproc, this error happens repeated at the same line number (line 8) of the sp_executesql!!!

Thanks.

Ray M SSC-Insane Points: 21093 More actions · Answer 3

You asked what was going on behind the hood with sp_executesql.

Hence my answer, but your really trying to find the sproc/batch that is calling the sp_executesql?

the only problem is you have to catch it while its happening

in query analyzer execute the procedure sp_who2 while locking is occurring

in the list you will see a blkby column, if any of the rows have a number then take that number, called the spidid and enter it in to the following command

dbcc inputbuffer(spidId)

and it will return you some/all of the text that executed, aka the stored procedure name.