May 24, 2006 at 11:37 am
Hi all -
I have a JDEdwards installation with a SQL2K back end, and we are having performance issues.
I have set up a job to run a trace on SPIDs that have a duration longer than 5 seconds, and in the results, I'll pick out RPC_Completed events where the call took a REALLY long time (sometimes minutes). What I usually see is that Text column often contains a single line with a call to a server-side cursor function (exec sp_cursorexecute or exec sp_cursorfetch).
Is there a way to set my trace to give me what the SQL command was that initiated the cursor function? I don't have access to the source code at the client, so I'd like to be able to track these long processes back through ADO or ODBC to see what the client is asking SQL for. The only way I can think to do it is to run the trace on every transaction and trace it back using the SPID. However, that would involve me running the trace on every event, which bogs performance enough to make my users scream even more.
Any thoughts or suggestions are greatly appreciated.
Thanks
Steve
May 29, 2006 at 8:00 am
This was removed by the editor as SPAM
May 29, 2006 at 2:47 pm
You can track a server cursor by its cursor id which is generated when the cursor is opened, and referenced in all subsequent calls to that cursor. The call to sp_cursoropen, as well as having a variable containing the cursor id as its first parameter, has the SQL statement on which the cursor is to be based as its second parameter. So if capturing all cursor open statements wouldn't be too much additonal load for the system, doing so would allow you to match the offending fetches to their corresponding SQL statements.
Server-side cursors in SQL 7 can be very troublesome and wasteful of resources. You might want to look at client side cursors if contention is a problem. Also, you mention tracing SPIDs with duration > 5s. I assume you mean events longer than that, not connections. You haven't got one of those apps where a new connection is made and dropped for every statement I hope?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply