June 1, 2011 at 1:26 pm
I have a query that runs about 3 times a day in about 200-500 seconds.
Several months ago it ran about 100 - 200 sec. so I thought I would investigate.
There are four tables and the smaller three are 31, 11000 and 22,00 rows.
The large one is 11 miliion rows.The actual execution plan shows that 99% of the cost is a clustered index seek on the big table where the estimated # of rows is 1, but the actual is 2.2 million.The clustered index is defined as the row_id column (identity column).
If I hint the query with OPTION (HASH JOIN) it runs in 30-40 seconds and the biggest part of the execution plan is 84% of the cost for a clustered index scan.
I did a dbcc show_statistics on the big table and clustered index.
It shows a 100% sample with a date of 5/29 (so very recent) and this data has not changed for over a year.
However, the show_statistics does show skewed data in the histogram section:
Name Updated Rows Rows Sampled Steps Density Average key length String Index
-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------
PK__CEC_SUBSIDIARY_A__6F8B3B3C May 29 2011 10:04AM 11620970 11620970 5 1 4 NO
(1 row(s) affected)
All density Average Length Columns
------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8.605134E-08 4 ROW_ID
(1 row(s) affected)
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
1 0 1 0 1
11206856 1.120685E+07 1 11206854 1
12067664 65535 1 65535 1
12416314 348576 1 348576 1
12416315 0 1 0 1
(5 row(s) affected)
Why does it not use the hash joins by default and is there anything I can do to make the optimizer use it without the use of hints?
execution plans for default and hint executions will be attached.
Steve
June 1, 2011 at 1:28 pm
The default plan with no hints and
plan with hash join hints attached.
June 1, 2011 at 2:12 pm
It seems like the statistics are out of date. If you compare estimated and actual number of rows, there's a big difference.
The other reason for the first query taking that long is the KeyLookup. That indicates idx_MOD_CD_EXPEND_CODE is not covering the query. Maybe you could add the missing columns to the INCLUDE() part of the index. This will increase the index size significantly (based on the number of columns needed) but it might help overall performance.
The second query actually performs a table scan to get the data from [CPTS].[dbo].[CEC_SUBSIDIARY_ACCUM_2009].
When the execution plan is viewed using SS2K8, the index I mentioned above is actually recommended.
June 2, 2011 at 7:26 am
I don't see stats being outdated as the data has not changed for a year (it's 2009 data).
idx_MOD_CD_EXPEND_CODE_ID is defined as MODCD, EXPENDITURE_CODE_ID with include (TOTL).
The problem is that the query sums on 14 columns so to include all of them would cause a huge index.
I can try a update statistics with fullscan on the big CEC_SUBSIDIARY_ACCUM_2009 table, but it seems the show_statistics already has the 100% sample.
June 3, 2011 at 5:29 am
This one has two possible solutions.
First, put the necessary join/output columns from the T4 and T5 tables into a temporary TABLE (not table variable). Then join to that for the big query and remove T4 and T5. If that gets you the right plan and good estimates, you are done.
Secondly create explicit statistics on each referenced T1 column that doesn't already have statistics. ORs are TERRIBLE for the optimization process and this could help it get a better estimate. You might want to do this step anyway to be honest with you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2011 at 9:52 am
I understand breaking down this query is a good idea and have told the user.
I have statistics with all the referenced columns as the leading column in the statistic definition.
It still goes to nested loop by default.
I think this is just an imperfection of the optimizer or it assumes one access method is faster than another based on some basic set of assumptions.
I've seen this in Oracle where its optimizer assigns a certain cost to a how fast a fast full scan takes and underestimates how fast it really is in my environment: with a very fast SAN holding all the data in the database, a full table scan starts a bit slow, but then the data flies from disk to Oracle's memory. Because of this, an index is sometime chosen by the optimizer (it thinks the cost is lowest), but runs slower than some full table scans.
I still do not understand why expected # of rows (1) is so far off the actual # (2215729) for the step that is 98% of the cost.
June 7, 2011 at 12:39 pm
sgambale (6/6/2011)
I understand breaking down this query is a good idea and have told the user.I have statistics with all the referenced columns as the leading column in the statistic definition.
It still goes to nested loop by default.
I think this is just an imperfection of the optimizer or it assumes one access method is faster than another based on some basic set of assumptions.
I've seen this in Oracle where its optimizer assigns a certain cost to a how fast a fast full scan takes and underestimates how fast it really is in my environment: with a very fast SAN holding all the data in the database, a full table scan starts a bit slow, but then the data flies from disk to Oracle's memory. Because of this, an index is sometime chosen by the optimizer (it thinks the cost is lowest), but runs slower than some full table scans.
I still do not understand why expected # of rows (1) is so far off the actual # (2215729) for the step that is 98% of the cost.
The problem isn't that the optimizer thought that a table scan would take longer than it does, it's the under estimation of the Key Lookups - hence the nested loop joins. If the statistics really are up to date, then it's kind of a mystery to me at this point. I'd have to actually play with the data to see if I could find why the optimizer so under estimated on the Key Lookups.
From looking at the query, however, I'm with Kevin on this. Divide and conquer (creating intermediate temp tables) would probably give you much better performance.
Also, you might want to shift the table joins from the WHERE clause to the FROM clause. Makes for easier reading.
June 8, 2011 at 8:32 am
Let me continue later today.
June 9, 2011 at 8:06 am
data value distribution skew can ABSOLUTELY cause the optimizer to estimate rowcounts that will lead to a plan that mathematically is most efficient based on the estimate but which due to actual rows hit is horrible. And in some cases you need to resort to trickery such as putting key values into a temp table (NOT table variable) and then joining to that to avoid the really bad plans. You rob Peter a bit in each query to avoid getting seriously smacked down by Paul on a bad plan.
Note that you will also likely need OPTION (RECOMPILE) on the statement to avoid plan caching issues.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 13, 2011 at 8:14 am
I actually pulled this query over a year ago that users ran several times. The idea is to build a list of commonly run queries that I gathered over the course of a few weeks, then I run that list of queries three times a day, Mon-Fri, just as a way of monitoring performance, in general, of the sql server. This is a data warehouse application. I keep the historical average of each query and report on current run and historical run times, so I can see how it's doing vs. its history. I wound up with a list of 11 commonly used queries. On my big Oracle DW, we came up with 19 queries.
If I did the same exercise today, I probably would come up with a somewhat different set of queries and certainly they would be using 2011 tables for those tables that are broken by year, more than 2010 or 2009. This stuff is not an exact science, it's just a way of gathering queries that run often and we want to arrive at a set of queries that are not similar and use different tables so we have a decent representation.
The fact is, the big table in the query, the one that the execution plan does a poor job with (bad estimates using key lookup index seek and slow index seek) is static because it is 2009 data, and the other tables in the query do not change much from day to day.
In the last week, performance has gotten down to under 200 sec, so it's behaving like it used to - better.
I still don't like the idea the optimizer chooses a poorer plan because hash join is faster, but everything I tried does not change the plan.
option (recompile) still yields same plan with nested loop.
My hope in posting this issue was to see if I was missing something in my approach. I have more experience in Oracle with these types of plan issues than in SQl Server, although I can tell you Oracle is also not perfect with their costing and row estimates and their plans.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply