August 14, 2008 at 12:38 pm
I used this query below to find the missing indexes and create the top 10 most costly indexes accordingly. Right after I created them, the CPU jumped to 100%. I was able to nail down on one index I created that screwed up the server. The query thats has always been the most CPU intensive now uses 10 times even more CPU because it is now using one of the ten indexes I added. By comparing the execution plan, looks like the new index seems to have helped by reducing some table scans. But I am not sure how CPU could go so high. Is the query below not quite right, or am i missing something?
SELECT TOP 100[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact -- Query cost would reduce by this amount, on average.
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROMsys.dm_db_missing_index_groups g
INNER JOINsys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOINsys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
August 14, 2008 at 12:44 pm
Missing indexes is a suggestion, nothing more. The entries in there are created by the query optimiser as it optimises a single query. They may be correct, they may be completely wrong. The logic that goes into creating those entries is less that the Database tuning advisor has even. It's possible that the optimiser will 'suggest' an index based on one query that would mass up a different one.
While it's good to look at its suggestions, it is, as you've noticed, a bad idea to just create the indexes it suggests without some testing and some sanity checking.
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
August 14, 2008 at 12:58 pm
Thanks, Gail. I am a little confused though, shouldn't the optimizer know that the previous execution plan is less costly than the new one and use the old one instead?
August 14, 2008 at 1:13 pm
KATHLEEN Y ZHANG (8/14/2008)
Thanks, Gail. I am a little confused though, shouldn't the optimizer know that the previous execution plan is less costly than the new one and use the old one instead?
Changing the indexes forces all plans based on that table to recompile, hence there is no previous execution plan to look at.
It's probably one of those strange cases where the cost looks low to the optimiser, but in reality isn't. Without seeing the query and some details on the table, I can't can't give any suggestions on why that might happen.
I've had cases before when adding a covering index made a query run slower. Much slower. There was a reason, albeit a strange one, but it does happen.
The other problem that I have had before after adding indexes was that having more options available lead to the optimiser picking a plan that was only appropriate for certain parameters - a parameter sniffing problem.
It's why I don't like taking any index suggestions (missing indexes DMVs, Database tuning advisor) and applying them without testing
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
August 14, 2008 at 4:19 pm
Hmm, this is very interesting, can you post the query that is CPU bound?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 15, 2008 at 9:41 am
I would love to, but the query includes the central production tables. I am not sure from business perspective, I am suppposed to post it. But from what I can tell, the new plan using the new index 'seems' better because it replaces a couple of table scan and index scan with index seek plus RID lookup. But somehow, the CPU goes much higher. So I am wondering when optimizer tries to figure out an execution plan, does it just look for a perfect looking execution plan, not considering how much CPU costs?
Also, maybe its a coincidence, the new index I added is a covering index. Probably its like what Gail has seen before?
Thank you!
August 15, 2008 at 10:01 am
Mangle the table names. We don't care if the tables are Table1, Table2, etc
The SQL optimiser is a cost-based optimiser. It generates plans and compares them on the basis of the cost. The cost is estimated based on the amount of IO reads that are expected, the amount of memory expected, the expected CPU utilisation and a whole bunch of other factors. The important thing is, it's an estimate and sometimes it is wrong.
The optimiser also only has a certain amount of time it's allowed to work. When that time is up, it picks the best plan of the ones it has found and uses that. It probably won't be the perfect plan, but usually it is good enough.
There are however a number of query constructs that really confuse the optimiser. That's why it's necessary to see the structure of the query to say anything for sure.
Ok, and RID lookups are not good. First they indicate that the table doesn't have a clustered index, second they are very expensive operations. If there's a cardinality inaccuracy (expected number of rows very different from actual) then those will really stuff the query up. Check the plan, check the index seek operators and make sure that the expected rows and the actual rows are very similar.
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
August 15, 2008 at 11:05 am
Can you just give us the before and after *.SQLPlan files?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 15, 2008 at 11:20 am
rbarryyoung (8/15/2008)
Can you just give us the before and after *.SQLPlan files?
Problem is those also have the query included inside them.
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
August 15, 2008 at 11:37 am
Exactly. Let me think whats the better way to get these to you.
For those tables that use index seek plus RID lookup, in the execution plan I captured while the new index was on, it does not show the actually number of rows, only the estimated number of rows.
And also I am not sure why the execution plan shows under 'performance statistics' event instead of 'show plan xml' or 'show plan text'.
One more thing, in development, I could not re-generate the execution plan, with new the index added. The plan does not use the new index.
August 15, 2008 at 11:43 am
Those are the estimated execution plans you have.
Is there any chance of you running the query in management studio with the "Include actual execution plan" option on?
Dev could have different amounts of data. different hardware, different usage. Not unusual to have prod and dev generating different plans if they have a different amount of data (which is usually the case)
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
August 15, 2008 at 2:52 pm
I was just able to re-create the execution plan on dev (by recreating all the ten indexes, not just the one index that was used by the execution plan).
The part that uses the new index does show a big difference between esitmated row count (45.4622) and actual row
count (310126). So why the difference then?
August 15, 2008 at 3:21 pm
There's the cause of the bad plan straight off. For the optimiser, doing a RID lookup of 45 rows in a table with more than 300000 is very cheap and is a good option. When that estimate proves wrong and there are actually a lot more rows, the 'efficient' plan is now a nightmare.
Unfortunatly, without seeing part or all of the query it's hard to say why. It could be related to the predicates of the query (if there's a function on the columns), it could be related to a table variable, a table-valued UDF, a remote operation (openquery, openrowset, openXML). It could be out of date stats, but I doubt it as you've just built those indexes.
Is there anyone you can ask about posting the query? Alternately, if you can't post it publicly, could you PM me (and rbarryyoung if he's interested) with the query?
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
August 19, 2008 at 2:59 pm
I ended up running update statistics on the table with full scan, then the new index no longer gets used by that query, therefore no bad execution plan.
Thanks Gail and rbarryyoung. Its a lesson learned. I will definitely run a good test before pushing any changes like this in the future.
August 19, 2008 at 3:19 pm
Glad to hear you came right.
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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply