sp_executesql, actual sql ?

Hi Guys

I pick up the following in a profiler trace.

exec sp_cursorexecute 197, @P1 output, @P2 output, @P3 output, @P4 output, 'PSRF_REPORTING_FOLDERS', 'Jan 1 1900 12:00:00:000AM'

Normally to find the actual sql that executes i go up in the trace until i find the relevant cursor prepare. In this case the one for 197 cursor prepare.

But this time around i cannot find it, these are app servers connected so for all i know the cursor could be prepared at 5am in the morning and re-used all day long.

Is there anyway (adding certain profiler events or something), so that i can see the actual statement held in the cursor

Thanx

Answer this question

sp_executesql, actual sql ?

Tellek Liberty

You can do the following:

1. Query master.dbo.syscacheobjects for all rows with objtype = 'Prepared'

2. Analyze the sql text to get rows with parameterized SELECT statements

3. Find the parameterized SELECT statements that have same number of columns as the output parameters and try to see if it is the one for your cursor

This is the closest you can get to finding the SELECT statement. Even this may not work if the cached plan has been removed from memory.
Dr.9

You should probably review the client code for cursor usage. This is a case where server-side API cursor is being used to prepare a SELECT statement, execute it and fetch each row. Obviously, this is going to have performance problems.

Btw, you are still referring to the cursor prepare/execute as sp_executesql but these are different mechanisms. Look in Books Online for server-side API cursors and the cursor system stored procedures for more details.
fcastell
Anyone please
Raviatr
Hi Umachandar

Thanx for the help unfortunately there are over 16000 of these it would be impossible to find the one i am looking for.

This is worrying me since we have a very badly performing query from one of the app servers effecting production, and it is this sp_executesql , and there is no way to start troubleshooting it unless we can actually see what the sql is

Perhaps i should log a microsoft case I will if i get nothing back on this forum by monday.

Or i can try run a profiler trace for a few days and see if i can sift out the cursor preapre i ma looking for (but i know here are thousands fo these!!).

I just thought there must be an easier way of getting to the sql.

Thanx
sp_executesql, actual sql ?

Answer this question