New Born DBA (5/7/2015)
New Born DBA (5/6/2015)
My suggestion, if most of your SELECT queries read on column C6, is to change the primary key from a CLUSTERED index to a NONCLUSTERED index and then make the index on C6 your CLUSTERED index.
Well, I Created non clustered index on C6, and query ran much better. It used 70% less CPU, even logical reads were down to 50,000 instead of 175,000 and it ran in 13 seconds instead of 40 seconds, but this is what I was told, "that it looks promising, however, The index on C1 will ALWAYS be used to create a new record in the table. It is a system field. We can’t delete that index"
Sorry, but I actually spit soda out my nose on this comment:
"that it looks promising, however, The index on C1 will ALWAYS be used to create a new record in the table. It is a system field. We can’t delete that index"
It isn't a system field, the column c1is a user defined column on the table, and the index can be dropped and recreated as a nonclustered index and a clustered index created on the column c6.