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
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!