August 12, 2013 at 11:29 am
Attached both plans.
Both
- Same Hardware (different machines)
- Same data in/out of query.
- Nearly identical instance configurations
Differences I note
- On the two db's, there are some changes in indexes. I get near identical results on the dev machine on a near exact copy of the db and an earlier version with about 20 fewer indices.
- On the two instances, Prod is capped at a bit over 100gb of memory and doesn't use it all. Dev is not capped and does not use it all.
- Prod has 1 larger db, but it's inactive. It also has about 10 smaller dbs that are mostly inactive.
- Indexes and stats on prod (the slower of the two) are very up to date. On dev, they're the same in one db and a few months out of date in another. Still dev is doing a better job with the plan.
It's odd, it just looks like the plan chosen on Prod is slightly different and then gets slammed on CPU
With the plans there are slight differences in duration all around, but Statement3 shows a duration of 51,167 vs 4,310. A CPU of 47,861 vs 3760.
I'm using SQL Sentry Plan Explorer as well as SSMS to compare the plans. Both show high durations/CPU overall, but don't have a whole lot of that information within the statement of the plan itself.
Both Statement3 plans (attached as image) look identical. But Production took 10x longer and 10x more cpu.
Leads me to believe that they both made the same query optimization choices, but it wasn't a good plan for production because it handles something differently with CPU.
What else would explain this sort of difference in how the CPU is utilized?
I'm not even sure if looking at this in SQL is a deadend if it's an actual windows level difference between the two servers.
August 13, 2013 at 11:41 am
Okay, I noticed the issue by comparing the databases.
The prod db had an index on a table. I'm curious as to why when this one little index is added, SQL decides to use it and it's a horrible choice.
Just taking the index off brought my one report down from 50 seconds to 5 seconds.
It's also used pretty much everywhere in the system (a very core table calendar table fed into some indexed views that everything uses), so removing it immediately cut reads and blocking across the whole application.
I have no idea how I would have traced everything back to this index if I didn't just happen to test against a copy of the database without it. Because it's down so low in an indexed view, I'm not even sure it's apparent that the query plan ever see's this index.
I think this is part of the problem people talk about with nested views/functions
August 14, 2013 at 9:43 am
ShawnTherrien (8/13/2013)
Okay, I noticed the issue by comparing the databases.The prod db had an index on a table. I'm curious as to why when this one little index is added, SQL decides to use it and it's a horrible choice.
Just taking the index off brought my one report down from 50 seconds to 5 seconds.
It's also used pretty much everywhere in the system (a very core table calendar table fed into some indexed views that everything uses), so removing it immediately cut reads and blocking across the whole application.
I have no idea how I would have traced everything back to this index if I didn't just happen to test against a copy of the database without it. Because it's down so low in an indexed view, I'm not even sure it's apparent that the query plan ever see's this index.
I think this is part of the problem people talk about with nested views/functions
I tried to look at your execution plans, but the files I got when I downloaded them were execution plans for a simple insert statement that was optimized at the TRIVIAL level, which means there could be only one plan - are these the correct .sqlplan files?
An indexed view is a materialized, persisted data structure - when you create the unique clustered index on the view, SQL Server allocates pages on the disk drive to it and stores the data and index information there. Any references to the indexed view resolve to this clustered index (or to a non-clustered index on the view, if one exists and the optimizer chooses to use it). Indexed views are not expanded at execution time like regular views, so a query of an indexed view will not use the tables referenced in the view definition at all.
All that is to say that removing the index on a table as you described has no impact on the performance of queries of indexed views, as you speculated. The index must have been used somewhere else in the query plan. If removing it caused a performance improvement, it means the optimizer made a bad choice to use the index. Out-of-date or no statistics for the index are common culprits here, but the optimizer is not perfect, either - it's looking for a good-enough plan, which may not be the best plan.
Jason Wolfkill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply