open cursors on server using "dbcc cursorstats" cant figure out whats causing it

Question

Can someone please move this to the DB Engine forum - no responses all week - maybe they have an idea.  Thanks



~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_~_


We have the following version of sql server:

Microsoft

SQL Server 2005 - 9.00.4220.00 (X64) Apr 2 2009 18:34:24 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

It is the same version on 2 production boxes.  The production boxes are the same.  The code base should be (read - developers have told me) the same.

However when I run dbcc cursorstats, ProductionA  looks like the following: 

SPID Request Type Grant Type Count
0 Insensitive/Snapshot     Insensitive/Snapshot     27
0 Keyset                   Insensitive/Snapshot     0
0 Keyset                   Keyset                   0
0 Dynamic                  Insensitive/Snapshot     17
0 Dynamic                  Keyset                   205560
0 Dynamic                  Dynamic                  139
0 Fast Forward             Insensitive/Snapshot     0
0 Fast Forward             Keyset                   0
0 Fast Forward             Dynamic                  0
0 Fast Forward             Fast Forward             8140

 

while ProductionB looks like:

SPID Request Type Grant Type Count
0 Insensitive/Snapshot     Insensitive/Snapshot     48
0 Keyset                   Insensitive/Snapshot     0
0 Keyset                   Keyset                   0
0 Dynamic                  Insensitive/Snapshot     0
0 Dynamic                  Keyset                   0
0 Dynamic                  Dynamic                  198
0 Fast Forward             Insensitive/Snapshot     0
0 Fast Forward             Keyset                   0
0 Fast Forward             Dynamic                  0
0 Fast Forward             Fast Forward             9455

 

HOWEVER when I look at the following queries

 

dbcc activecursors (0)

select * from sys.dm_exec_cursors(0)

SELECT * FROM MASTER..SYSCURSORS

select * from  sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur

 

They all return no open cursors...it's hard to find much information on dbcc cursorstats i guess because its undocumented,
but we are having server memory issues and trying to figure this out.

----------

I've read that a SPID of zero is the system, not any user.  Does "dbcc cursorstats" list open system cursors used when running queries for joins and whatnot?

cursorstats can also pass an int of a SPID in question.  when I send in zero (whichh i believe is the same as not passing in anything - I get the same results), however any other SPID returns zero for all - this is why I think its a system thing,

but then again, why would ProdA and ProdB be so different.

Both environments have "close cursor on commit" set to false

The main thing i would like to point out and discuss is the disparity between the "dynamic keyset" cursors - over two hundred thousand between production A and Production B???  Also, are these system tables, DMVs and the DBCC command all the same?  Am I mixing up results???

looking for some assistance in how to track these down or who is opening / creating these...

Thanks,
Matt

Answer 1

Anyone want to take a guess?  Just giving it a bump...is there more info needed?  Do people just not know?  Anyone want to post as to how I can get some responses even if they dont know the answer?

I actually posted this in October but editing replies caused the post to become corrupt and was reposted.

I also had it in the Transact-SQL forum, no luck, until I asked Hunchback to move it here and give it a go.

Answer 2

DBCC CURSORSTATS is an aggregate collection of statistics for cursor usage.  It is like querying sys.dm_os_wait_stats to see the aggregation of wait types over time.  Values in the output doesn't equate to currently occuring waits which would be found in dm_exec_requests or dm_os_waiting_tasks.  You can actually clear the CURSORSTATS by calling:

dbcc cursorstats (0, CLEAR)

just like you can clear wait stats with DBCC SQLPERF('sys.dm_os_wait_stats', clear).  I think you are misinterpreting what you are seeing.  These may not be explicit DECLARE CURSOR/OPEN CURSOR calls in TSQL code.  Some of the API's use cursors when fetching rowsets or when performing RPC calls to SQL which are not problemattic.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!