February 13, 2014 at 5:32 am
Hi
I have just ran sp_blitz from BrentOzar.com. From the results it said that one of the top resource intensive queries may be improved with an index.
I ran the stored proc in question with:
SET STATISTICS IO ON
SET STATISTICS TIME ON
and found that cpu time was 265ms and logical reads on one of the tables was 71538
After adding the index cpu time dropped to 16ms and logical reads down to 3.
Result you might think. However when I ran a query to see how the new index was being used I saw that after about 30 mins of it being created that it was getting Total queries which read = 2, and Total queries which Wrote = 51, giving me a ratio of reads/write of 0.0.
So even though I have reduced cpu time and logical reads it appears the index is being written to more than it is being read.
Should I remove it ??????
February 13, 2014 at 7:29 am
Not quite sure if i am reading your question properly but...you would see the difference in read/writes because 1) the table the non-clustered index was created on is being written to (i.e. records are being inserted). 2) the index will only show a seek/scan if it's actually used to fulfill a query (i.e. if you ran a query that needed the index 2 times, you'd see either 2 seeks/scans).
Index "suggestions" are always only suggestions...and running them as-is is more of an "it depends" mindset. There are times when changing index keycolumns can have adverse affects on other queries/execution plans (as you might be altering their selectivity). You can most often benefit from comparing them to existing indexes and perhaps merging them, etc.
I wouldn't remove the index if it has helped reduce IO and CPU resources, unless you already have a lot of non-clustered indexes on that table and have what the sp_blitz refers to as "index hoarding"
Does this answer your question?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 13, 2014 at 9:19 am
Thanks for the reply.
Yes I understand the reasons for the difference in the read/write ratios and understand that the index helps certain queries that choose to use it (ie statements with a some kind of filtering such as a where clause that include that column) and that the Inserts, Updates and Deletes will increase the 'write' stat that I see for the new index.
I think I am in agreement with you that I should probably keep it in light of the fact I have reduced logical reads quite significantly and also the fact that this table is not bloated with indexes ( it only has 3 indexes).
I was just recalling a performance tuning video from Brent Ozar whereby he suggested if this ratio of reads to writes is 0 then to drop the index (checking of course that this index isn't used in some way by the likes of the CEO for some kind of 'run once in a blue moon massive report' :o) )
Thanks for you reply 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply