Update: Installed CU6 but it did not fix the problem.

That worked!

Thank you.

I tried what you suggest and it does force the "bad" plan when I use 40 and a "good" plan when I use 17. Thanks.

Is there any way to "prove" that the issue is a data skew problem? This is a production system so I can't just play with trying to force a bad...

I always thought that parameter sniffing problems had more to do with ranges than with direct seeks.

If it was a parameter sniffing problem then shouldn't I be able to see...

I understand that using the first index (the clustered one) allows SQL Server to retrieve any column in the record once it has found the record it wants (using the...

So, you are saying that SQL Server determined --

the cost of finding a few records (using second index) + lookup for other fields

was greater than ---

the cost of searching...

I understand what's been said.

But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for...

Execution plans from our production for both types of select statements

Got it

Thanks alot for all your replies

Let me make sure I have this.

If I use ALTER TABLE to change the varchar size (e.g. varchar(20) to varchar(50)), SQL Server will not duplicate the table but instead just...

Thanks for all the replies.

You are confirming what I already thought was true - adding a column in the middle of the table would end up duplicating the table....

There are no triggers on the table.

I did not time the actual deletes but when 10 single deletes finish quickly and a single delete for 10 rows does not, I...

Sorry, should have left IX_tblDocument_DocumentID off the list. I created it after I tried the 2 different kinds of deletes. I was thinking that maybe the clustered index was the...

Is this what you are looking for? If not, how do I get it?

Index Name/avg_fragmentation_in_percent

IX_tblDocument_Box 18.7529747739172

IX_tblDocument_Category_Number 8.07453416149068

IX_tblDocument_Category_State 34.336917562724

IX_tblDocument_CreatedDate_DocumentID 26.4388489208633

IX_tblDocument_DocumentID 7.15277777777778

IX_tblDocument_DocumentName 41.025641025641

IX_tblDocument_PurgedDate 26.8115942028986

IX_tblDocument_RetainFlag 14.6437346437346

PK_tblDocument 60.941960038059