Here is the situation. I have a fairly simple query. It returns ~22k rows:

select * from tbl_A

When I run this query in SSMS (2017 7.6), I get a fairly simple plan:

SELECT <= Clustered Index Scan (PK_tbl_A) (Cost 100%)

However, SQL Server 2014 has has cached this plan for this same query:

DECLARE CURSOR (Snap Shot) <= Populate Query <= Clustered Index Insert [CWT_PrimaryKey] (Cost 99.1%) <= Sequence Project <= Segment <= Clustered Index Scan (PK_tbl_A) (0.9%)

                             \___ Fetch Query <= Clustered Index Seek [CWT_PrimaryKey]

Sorry for the formatting, I had to obfuscate some information, so I didn't want to "paste the plan".

I did some research and found the CWT table is the Cursor Work Table in the tempdb.

The only thing I have found "odd" about tbl_A is that there are a mix of char LineOfBusiness char(2) and varchar columns like Manager varchar(255). There is one constraint, a default on one column. No triggers. A primary key on an autoincrementing int column.

I am wondering why this query is using the tempdb and cursors for what seems to be a 100% hit on the clustered index. Why is SQL generating a Cursor Work Table, indexing it and spending most of the query time updating the index in the CWT--when it could scan the actual table index?

Why is my query using cursor that seems to be eating a huge amount of I/O in the tempdb, when I run it in SSMS it does an index scan? Could there be another query, with a cursor, using this plan--which is not shown in the cached plan?

I know I can seek out the source of the query and use index hints, but should I need to? I'm more interested in the circumstances where SQL Server would create a cursor for a straightforward select statement. Also note that I have inherited this table from a legacy system and I am not easily able to modify it.

Any ideas would help. Thanks!

  • Is this a Dynamics query? Or from another app? – Erik Darling May 16 '18 at 15:03
  • @sp_BlitzErik You mean from one of the MS Dynamics applications? No, it's from a custom expense management system. Or did you mean Dynamic SQL? By the way, thanks for your work on the blitz sprocs. That is how I found the plan. – Jacob H May 16 '18 at 15:13
  • 1
    Glad you like'em! But yeah, I meant MS Dyanmics. It uses a lot of cursors. Does your in-house code call them? Keep in mind that different connection drivers may use cursors without really telling you, like JDBC. – Erik Darling May 16 '18 at 15:15
  • @sp_BlitzErik The application, I believe, is written in VB6. So it is definitely possible that we have ADO with a cursor... I have not seen the source code but ADO is used frequently in the older apps here. – Jacob H May 16 '18 at 15:18
  • There you go! I'd start taking a look there. – Erik Darling May 16 '18 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.