April 11, 2013 at 10:00 am
I have a table with the 2 indexes performing as shown below:
Index NameIndex Type Queries Which Read Queries Which Wrote Reads/Write
IX_index2NONCLUSTERED 1283151 2 641575
PK_index1CLUSTERED 516435 439378 1
Looking at the above stats, would I be better changing the Primary Key to index2 and making index1 the non clustered index ?
April 11, 2013 at 10:09 am
No idea. There's no where near enough information to do anything other than guess there.
Firstly, a clustered index will always have more reads than any nonclustered index because the clustered index is the table (hence has all the columns) while a nonclustered index is a subset of the columns in the table. I don't know how you've calculated reads so can't say anything useful.
Second, there are considerations for creating clustered indexes, you shouldn't just stick one on any old set of columns without careful consideration or you can severely impact your write performance, the size of the nonclustered indexes, fragmentation, wasted spaces, etc. See http://www.sqlservercentral.com/articles/Indexing/68563/
Lastly indexing decisions should be based on the queries that run against a table and on the data in the table, neither of which is known.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2013 at 10:14 am
I used the script that Kendra little uses in her optimizing indexes video from http://www.brentozar.com.
I have just added the excel spead sheet as ive noticed the text has got formatted wrong.
I suppose though your answer is still the same - I take on board what you are saying.
Thanks very much:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply