February 5, 2013 at 3:52 am
Hi,
We have a query with a bad performance issue. (28 hours to run).
I have been looking at the estimated query plan and noticed something strange.
Conceptually we are joining a .5 million row table (SE) which contains a non-overlapping from/to field to a 9 million row table (SFI) like this:
UPDATE leica.States_for_Interval
SET
Smooth_Time_Key = COALESCE(SE.SmoothTimeKey,SFI.Time_Key),
Smooth_FailureEvent = CASE WHEN SE.FirstFailure = 'Y' AND SFI.RowNum = SE.RowNum_Start THEN 'Y' ELSE 'N' END
FROM
leica.States_for_Interval SFI
INNER JOIN
tfm.stg_Time_SmoothEvents SE
ON SFI.RowNum BETWEEN SE.RowNum_Start AND SE.RowNum_End
AND SFI.SRC_System = SE.SRC_System
AND SE.SRC_Application = 'LEICA'
I've attached the query plan, and table creation DDL
I know that there is a 1:n relationship as the ranges do not overlap. So every row in the large table should match only one row in the range table.
Therefore the output of this query should be the number of rows in T (9 million)
HOWEVER when I look at the query plan I see that
The 'Estimated Number of rows' on the smaller table is the FULL row count
The estimated rows and the larger table is the the full row count
The estimated output from the Nested Loop join looks like the cross product of this.
From looking at other query plans with tables with 1:n relationships, I see this:
The 'Estimated Number of rows' on the smaller table is ONE (not all the rows)
The estimated rows on the larger table is the full row count
The estimated output from the Nested Loop join is the the full row count
In other words it looks to me like SQL Server thinks there is a cross join going on. But I know from the data that there isn't (I am yet to actually test this but I'll get back to you)
So I have two questions:
1. Am I correct in the way the nested loops operator should work under 1:n circumstances - the smaller input should have a estimated row count of ONE
2. HOW can I give SQL Server the clue that the ranges are mutually exclusive? There are no constraints that I can put on the table to indicate this.
February 5, 2013 at 4:02 am
Can you post the execution plan please?
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
February 5, 2013 at 4:31 am
OK I've attached a text query plan.
I'm not at work right now so I have limited access but I very much appreciate you having a look for me.
I've also edited the post with the sanitised query rather than an example
February 5, 2013 at 4:52 am
Don't suppose you could post the graphical plan? There's not enough info in that text plan.
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
February 5, 2013 at 5:20 am
Sure, I'll do it first thing tomorrow at work, thats in about 11 hours, thanks for your interest!
February 5, 2013 at 10:56 am
nick.mcdermaid (2/5/2013)
2. HOW can I give SQL Server the clue that the ranges are mutually exclusive? There are no constraints that I can put on the table to indicate this.
You should try uniquely clustering the SE table by (RowNum_Start, RowNum_End).
Just to review, what are the current indexes and their usages on the SE table?
Also, review the indexes and usages on SFI, particularly the clustering index (as always!): but it will almost certainly be much more involved to determine the correct clustered index on SFI.
Edit: Changed the understated "Have you tried uniquely clustering" to "You should try ...".
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2013 at 4:39 pm
OK, I have reposted an unsanitised query, the sqlplan file, the DDL, and a picture of part of the query plan.
I can try clustering SE on RowNum_Start and RowNum_End but this still won't tell it those ranges are mutually exclusive.
February 5, 2013 at 4:47 pm
nick.mcdermaid (2/5/2013)
OK, I have reposted an unsanitised query, the sqlplan file, the DDL, and a picture of part of the query plan.I can try clustering SE on RowNum_Start and RowNum_End but this still won't tell it those ranges are mutually exclusive.
If they are uniquely clustered it will ... right!?
Just noticed the "SE.SRC_System" and "SE.SRC_Application = 'LEICA'", so those will need to be in the clus index first (perhaps, if the system and application values are not all the same).
But pls first post the indexing stats on both tables (from sys.dm_db_index_usage_stats).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2013 at 5:07 pm
nick.mcdermaid (2/5/2013)
OK, I have reposted an unsanitised query, the sqlplan file, the DDL, and a picture of part of the query plan.
The .sqlplan you posted is the estimated plan, not the actual. Can the query not complete?
The key on stg_Time_SmoothEvents is fine, but that should probably be a non-clustered PK. At the least you want an index built to (and in this order):
SRC_Application, SRC_System, RowNum. If it's non-clustered use an include and bring over SmoothTimeKey
The reason Rownum is last is because it's a range join (non-equijoin) and the index will stop using columns deeper in the index from there. That should clean up the major scan.
From there, if we can see the actual, that'll help tremendously.
EDIT: Additionally, remove the implicit conversion. SRC_Application is NVARCHAR. Use AND SRC_Application = N'LEICA' instead of SRC_Application = 'LEICA'. The N'' will indicate varchar, removing one step of the process.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 5, 2013 at 6:37 pm
Thanks for your interest and suggestions everyone.
I have uploaded usage stats.
The query has not completed for a couple of days, and we have rebooted the server since then so I think that Actual plan is gone until we can let it complete. Do you expect any variance between the actual and estimated plan if I generate the estimated plan while the actual query is running (i.e. stats and record counts are identical). I do appreciate that need to remove the uncertainty and analyse the actual plan.
I can see that there is a lot of scope for altering indexes. Unfortunately I think I need to let it complete and get at least one actual plan before we can try anything.
With regards to the data, in this particular case, SRC_Application, SRC_System currently have only one unique value anyway.
My real concern at this stage is why does the loop join operator say that the estimated number of rows (which I assume is the number of rows it processes or outputs) going to produce a cross join of records from the inputs when I know the data is n:1
On a side note, about the exclusive ranges thing: What I'm getting at is that if FROM/TO ranges are overlapping and you join a transactional table into it.... you'll get repeated transactional records. i.e. if you have these two records:
Record FROM TO
A 1 10
A 1 20
Yes the combination of FROM/TO is unique, but if you join into this table using between you get an overlapping range from 1-10
So a unique constraint on both columns does not guarantee that there is not an overlapping range. Anyway that's besides the point. Perhaps I will start a new thread on that topic.
February 6, 2013 at 12:01 am
nick.mcdermaid (2/5/2013)
Do you expect any variance between the actual and estimated plan if I generate the estimated plan while the actual query is running (i.e. stats and record counts are identical). I do appreciate that need to remove the uncertainty and analyse the actual plan.
Potentially, yes. The estimated row counts are based on the optimiser's estimates, based on statistics and heuristics. Makes no difference when the estimated plan is generated, it's still just estimated row counts
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
February 6, 2013 at 2:07 am
OK I extracted what I think is the actual plan from sys.dm_exec_query_plan and it was identical to the estimated one that I uploaded, so we can assume the uploaded query plan is what was used.
We are running a few more tests on it now. It appears that under some circumstances it runs a lot faster.
February 6, 2013 at 2:27 am
nick.mcdermaid (2/6/2013)
OK I extracted what I think is the actual plan from sys.dm_exec_query_plan and it was identical to the estimated one that I uploaded, so we can assume the uploaded query plan is what was used.
Not the point...
The difference between an estimated plan and an actual plan has nothing to do with the form of the plan. They will be identical (SQL doesn't do less work when you ask for an estimated plan, it does the same compilation as when you run a query, or fetches the plan from cache if one exists)
The difference between an estimated plan and an actual plan is that the estimated plan has estimated row counts and execution counts only. The actual plan has estimated and actual row counts and executions.
The estimated row counts are estimations from compile time based on statistics and heuristics. The actual row counts are the real actual number of rows that the query operated on.
When the estimated and actual row counts differ, that will almost certainly cause bad performance (SQL generated a plan for a certain number of rows and got a very different one). That's why we're asking for the actual plan. Nothing to do with the form of the plan.
p.s. The plan that comes from sys.dm_exec_query_plan is the estimated plan, because it has no actual row counts.
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
February 6, 2013 at 2:37 am
nick.mcdermaid (2/6/2013)
OK I extracted what I think is the actual plan from sys.dm_exec_query_plan and it was identical to the estimated one that I uploaded, so we can assume the uploaded query plan is what was used.We are running a few more tests on it now. It appears that under some circumstances it runs a lot faster.
Simplify your model by taking out the update part:
SELECT
SFI.RowNum,
SFI.Time_Key,
SE.RowNum_Start,
SE.SmoothTimeKey,
SE.FirstFailure
--INTO #SFI
FROM leica.States_for_Interval SFI
INNER JOIN tfm.stg_Time_SmoothEvents SE
ON SFI.RowNum BETWEEN SE.RowNum_Start AND SE.RowNum_End
AND SFI.SRC_System = SE.SRC_System
AND SE.SRC_Application = 'LEICA'
If this generates many more rows than the half million or so which get updated then there's scope for improvement. Can you post the actual plan for this please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 6, 2013 at 2:40 am
p.s. The plan that comes from sys.dm_exec_query_plan is the estimated plan, because it has no actual row counts.
Are you saying that actual plans are not in that table? or are you saying the one I uploaded is not an actual plan because it doesn't have 'actuals?'
If the actual plan is not in that table, can you tell me where to find it?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply