Reverse Engineer sp_cursorexecute

Question

Post reply

Reverse Engineer sp_cursorexecute

JohnnyDBA

SSCertifiable

Points: 6355
More actions
December 15, 2009 at 12:22 pm

#218737

I have a customer with a performance issue. When I trace the session all I am getting back is a statement calling sp_cursorexecute which references an ID number. I am assuming this ID number is an ID for something stored in the SQL Server query cache. I'd like to know how I can reverse engineer this number back to a procedure name or SQL statement.
Can anyone point me in the right direction?
Thanks,
John

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

This link might provide you with the information you are looking for

http://www.tek-tips.com/viewthread.cfm?qid=832355

and

http://jtds.sourceforge.net/apiCursors.html

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 2

Unless I'm not reading these articles correctly, I don't think these answer my question.

Below is a sample of what I'm seeing in the SQL Server Profiler.

declare @p2 int set @p2=180203657 declare @p3 int set @p3=16 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073744149,@p2 output,@p3 output,@p4 output,@p5 output,N'cpd',N'LID05354539',4 select @p2, @p3, @p4, @p5

The ID in bold is the handle of the cursor. I see multiple SPIDs executing this handle. I need to know what code is being executed.

I understand that if I trace this when the statement was prepared I would be able to get the statement text but this is production and I cannot stop / start the services just for this so I need to know if there's any way of grabbing the associated statement in another way.

Thank you,

John

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 3

Just to follow up on this, when I run a ::fn_get_sql() on some of the SPIDs running these commands, it just returns something like ...

FETCH API_CURSOR000000000007F549

Everything I'm reading online says that the only way to get the code is to trace the initial Prepare or Open statement on the command.

Please let me know if there is another way or if that is really the answer.

Thanks,

John

mark.sloan 1854 Valued Member Points: 70 More actions · Answer 4

I have the same issue. But since this thread didn't appear to provide a solution, I've found something that met my needs. What I did was use Profiler and by enabling the Stored Procedures events RPC:Completed and SP:StmtCompleted with all columns checked. I also added filtering for database and ect to clear out other traffic. Then ran the trace. I found that by taking the SPID of the RPC:Completed events, with the sp_cursorexecute, and matching them to the SP:StmtCompleted events I could see what the sp_cursorexecute was doing.

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 5

Smart, thanks for that. Only took 7 years to get a reasonable answer:-D

dviacono Newbie Points: 1 More actions · Answer 6

This reply has been reported for inappropriate content.

Hello, I am from the future. Thank you for solving my problem.