June 4, 2013 at 2:55 pm
Hi everyone. I'm going through my index usage and finding some cases where the clustered indexes that I created might be doing more harm than good. The four listed here are the worst performers. I think that that the data is bulk loaded and as a result, I may be impacting performance by sorting the data with clustered indexes. These numbers represent a typical workload start to finish.
Table 1 clustered index: 34 total reads, 472,498 total writes
Table 2 clustered index: 1578 total reads, 862,368 total writes
Table 3 clustered index: 0 total reads, 1,759,849 total writes
Table 4 clustered index: 3,551,723 total reads, 5,467,635 total writes
Is there a chance that converting these back to heaps and selectively creating nonclustered indexes might be a performance boost?
Thanks,
Howard
June 4, 2013 at 3:03 pm
Probably not.
What in those numbers suggests to you there's a problem? Looks just like heavily modified tables.
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
June 4, 2013 at 3:13 pm
Are your clustered indexes ever-increasing, narrow and unique ? If at least one "No", that might be a reson.
June 4, 2013 at 3:15 pm
I assumed if the writes were significantly more than reads than the index is bad. If the clustered index has very few reads then doesn't that mean that the table is not being queried much but has to be sorted each time data is inserted or updated?
June 4, 2013 at 3:21 pm
SQL Guy 1 (6/4/2013)
Are your clustered indexes ever-increasing, narrow and unique ? If at least one "No", that might be a reson.
The one with 0 reads is not unique.
I'm just wondering why sort the table in a particular order with a clustered index if the index is not ever referenced by reads.
June 4, 2013 at 3:27 pm
Try to experiment by choosing different coulmn(s) for that clustered index.
June 4, 2013 at 3:30 pm
I guess now that I think about it, the clustered index might be greatly speeding up the updates so just comparing reads to writes isn't a good way to determine if an index is valuable.
June 4, 2013 at 3:35 pm
PHXHoward (6/4/2013)
I assumed if the writes were significantly more than reads than the index is bad.
For a nonclustered index, yes in general, but the clustered index is the table, hence any change must always be made to the clustered index no matter what column.
If the clustered index has very few reads then doesn't that mean that the table is not being queried much but has to be sorted each time data is inserted or updated?
No. Could mean that read queries are satisfied by nonclustered indexes and hence the cluster doesn't get hit for the reads
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply