jchapman (5/22/2014)
I've been experimenting... riddle me this...My misbehaving share table is partitioned (with LightSpeed, this is 2005 environment) into 183ish partitions.
Here is what I did I just copied my 92 million row table into a new table, all in a single partition... I cannot make it table scan regardless of the number of columns I add to my query... indexes are the same.
Is it doing an index scan and RID lookup instead?
Does this make sense (is this maybe a sql 2005 partitioning issue?) could it be that when I include a column not in the index (forcing a merge of data from multiple file groups) ... I am forced into a table scan.
My non partitioned table is subsequently faster than the partitioned table.
Here is my theory... Since partitioning is not native to SQL2005... I believe the plan to use the index breaks down when columns in the query are not all in the same filegroup. (The index with my 4 columns is in the default file group, when I include more columns, then I'm bounced into one of the other partition file groups... and sql server decides to not use the index because it has to work from a different file group?) Does this make any sense at all?
Your columns would all be available in all of the files of the filegroups. Your rows would be in different files/filegroups for the partitioning.
Since you are partitioning, the partitions are probably aligned with the columns of that initial query. Adding a new column to the query now causes the QO to have to use a table scan because it is cheaper to execute that way.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events