Prev: Force Protocol Encryption
Next: DB Mail - Error formatting query, probably invalid parameters
From: KnightFall1 on 13 Jan 2009 16:46
I am running a SQL 2000 Enterprise SP4 server. Periodically I am seeing
blocking issues apparently created by a sp_cursorfetch statement being
executed under domain account. The blocking issue doesn't occur every day but
generally the same time of day. Specifically the statement is "exec
sp_cursorfetch 180150000, 2, 1, 1". I have trying searching BOL but can find
info directly about the sp_cursorfetch statement other than reading that it
can be a highly inefficient way to retrieve data. During this time the
Average OS Disk Queue Length spikes to more than 140.

I have run at least 3 different tools (Quest Coefficient, Idera SQL
Diagnostic Manager, and SQL Profiler) but none of the tools identify the
application name (only the domain account) that is running the
sp_cursorfetch. So finding out what is running the sp_cursorfetch is a big
problem.

This statement appears to be running from the database server itself however
the only scheduled task that I can find on the database server that runs
under the same domain account is a maintenance job for backing up the
transaction log. But I don't see how a transaction log backup could trigger a
sp_cursorfetch operation???

Basically I need help to find out the source of the statement. Even when run
locally from the server, SQL Profiler skips so many lines of activity. I get
the generic message (Some trace events have not been reported to SQL Profiler
because the server has reached its maximum amount of available memory for the
process). That happens whether the Profiler is run locally or remotely. So
there may be more information that could help me find the source of the
issue, but the Profiler is not capturing all activity.

So I'm basically asking how do I find the source code/T-SQL executing an
sp_cursorfetch when various SQL tools cannot identify what is running this
statement?
From: MikeWalsh on 13 Jan 2009 22:33
On Jan 13, 4:46 pm, KnightFall1
<KnightFa...(a)discussions.microsoft.com> wrote:
> I am running a SQL 2000 Enterprise SP4 server. Periodically I am seeing
> blocking issues apparently created by a sp_cursorfetch statement being
> executed under domain account. The blocking issue doesn't occur every day but
> generally the same time of day. Specifically the statement is "exec
> sp_cursorfetch 180150000, 2, 1, 1". I have trying searching BOL but can find
> info directly about the sp_cursorfetch statement other than reading that it
> can be a highly inefficient way to retrieve data. During this time the
> Average OS Disk Queue Length spikes to more than 140.
>
> I have run at least 3 different tools (Quest Coefficient, Idera SQL
> Diagnostic Manager, and SQL Profiler) but none of the tools identify the
> application name (only the domain account) that is running the
> sp_cursorfetch. So finding out what is running the sp_cursorfetch is a big
> problem.
>
> This statement appears to be running from the database server itself however
> the only scheduled task that I can find on the database server that runs
> under the same domain account is a maintenance job for backing up the
> transaction log. But I don't see how a transaction log backup could trigger a
> sp_cursorfetch operation???
>
> Basically I need help to find out the source of the statement. Even when run
> locally from the server, SQL Profiler skips so many lines of activity. I get
> the generic message (Some trace events have not been reported to SQL Profiler
> because the server has reached its maximum amount of available memory for the
> process). That happens whether the Profiler is run locally or remotely. So
> there may be more information that could help me find the source of the
> issue, but the Profiler is not capturing all activity.
>
> So I'm basically asking how do I find the source code/T-SQL executing an
> sp_cursorfetch when various SQL tools cannot identify what is running this
> statement?

So that proc you are seeing is basically stemming from a connection
method that is using an older style. It is a server side cursor, most
likely an ADO connection and you probably see multiple fetches for
that same cursor.

You can try and search for the prepare to see the actual statement
being executed. Trying a trace filtered for sql text (not the most
performant filter to add and it may still be dropped because of when
the filter is added) trying to find text like sp_cursorprepare or
sp_cursorprepexec may help show you the query that is doing the work.
This may help narrow down the cause.

Also look at the database ID to see if that helps. I agree that the
transaction log backup job (if it is a standard backup only) is not
your culprit. I am thinking the cause could be monitoring software, a
home built application that is doing monitoring/database work or any
number of causes but those are the first to think about. I have seen
this behavior in older ERP systems but it really could be anything.

This link helps give you an overview of the various cursor commands
and what the input is to help you correlate the information.
http://jtds.sourceforge.net/apiCursors.html

Also this link talks a bit about server side cursors:
http://www.sqlteam.com/article/server-side-cursors-and-ado-cursor-types

HTH
Mike Walsh
www.straightpathsql.com/blog
 | 
Pages: 1
Prev: Force Protocol Encryption
Next: DB Mail - Error formatting query, probably invalid parameters