June 4, 2014 at 6:47 am
I have queried the plan cache and it returned a missing index for one the stored procs. I ran the proc before I created the new index with using the set statistics io on and set statistics time on.
It returned over 2700 logical reads and after adding the index this figure was reduced to just 19.
However the reads/write ratio of the index is currently 0 - Queries which read = 17, Queries which wrote =26
This is after a short time period (only 15 mins) so I may have to wait a little longer for a true reflection, but given past examples in my environment if the numbers start off like this after adding an index they generally stay that way.
So should I keep the index??? as I have dramatically reduced logical reads from 2700 to 19 - which can only be a good thing - but the index seems to be getting hit more with queries that are touching it with deletes, updates, or inserts ?
June 4, 2014 at 7:45 am
What I would do in a situation like this is to compare the cumulative times and IO of selects vs insert/update/delete with and without the index over a large enough time frame to get good sampling of workload.
Then decide whether the savings of the index for selects outweigh the penalty for the modifications.
June 4, 2014 at 10:46 am
How often is the read query called? Did that cause blocking or contention with other resources while running? yes, there are always added writes when creating an index, but how much were they slowed down? You've just shown a small amount of performance increase, but not enough data to determine if the increase the savings off set each other.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2014 at 11:12 am
I'm running a query to test my indexes that shows me the 'total number of queries which read' and 'total number of queries which wrote', together with reads/write ratio. All I can gleam from this info is that the new index is getting written too more (bad for performance) than it is being read to help queries. So I'm not sure of any figures on how often the queries are hitting the index.
Is reducing the logical reads down from over 2700 down to single figures not a massive performance hit?
I always understood it that if an index is getting written too more than it's being read, giving you reads/write ratio of 0, then the advice was to drop the index?
June 4, 2014 at 11:24 am
And your reads/writes ratio is based on pages or just the number of times referenced? If the latter, I'm not sure I'd care. If the former... again, still not sure I'd care. Yes, I/O is a vital measure for SQL Server, but so is execution time. How much faster is the query now vs. how much slower are the inserts. Then, multiply that by the number of times called to get additional references as to whether an index is worth it or not.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2014 at 2:39 pm
I'm not sure raw query counts can be used to determine an index's viability.
I too have seen statements to the effect that indexes with more writes than reads should be discarded, but I too am not convinced of that just by that one number.
For example, say we have a very large table, 600M rows. An added index prevents some queries from having to scan the entire table. One could see justifying that index even if it had significantly more writes than reads.
Also, indexes should be reviewed as a group even when looking at "missing index" recommendations from SQL Server (which should never be followed without a thorough review first). It's possible an existing index might need only a small column added to it for it to become a covering index for the "missing index" query.
Most vital for overall performance is to insure that you have the best clustered index on the table. Until you do that, other "tuning" is Sisyphus-ian.
Edit: Added the quotes around tuning in the last sentence.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 5, 2014 at 5:50 am
I would only ever immediately consider dropping indexes with 0 reads, and even then never if the index enforces uniqueness.
I would have to have a very good reason to spend hours researching the viability of an index with less reads than writes, I think the law of diminishing returns comes into play here.
Is there a performance issue with this stored proc, is someone complaining? Maybe its not called enough to warrant the attention.
---------------------------------------------------------------------
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply