August 10, 2012 at 1:44 pm
Wait for a moment!
Look at the plan.
Here is what good plan says:
@PayrollID value = (345)
Here is what bad plan says:
Compiled param value = (334329)
Run param value = (345)
Seems there is smth wrong about testing. What about if you re-create and execute your proc with 345 value?
August 10, 2012 at 1:50 pm
sqlnyc (8/10/2012)
I was able to create all of the indexes you suggested except for one on the CheckTaxes table.
Will look at the plan in the morning. Just one thing... I didn't say create new indexes, just widen existing ones. If you created new indexes please drop them and just modify the existing ones.
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 10, 2012 at 1:55 pm
Those are the two different values that I've been testing with. The 345 is for a single Employee, returning 48 rows. The other value brings back a group of Employees, and returns ~6,000 rows.
Sorry if I made a mistake. I've included a new plan that has the same compiled and run time values, using MAXDOP(1)
SQLNYC
August 10, 2012 at 1:56 pm
Gail - please forgive my terminology. I did indeed modify existing indexes with INCLUDE.
Best wishes,
SQLNYC
August 10, 2012 at 2:42 pm
sqlnyc (8/10/2012)
Gail - please forgive my terminology. I did indeed modify existing indexes with INCLUDE.Best wishes,
SQLNYC
Thank's for the plans! It is now much more clear!
Well, I think, I figured out query problem.
As I supposed, there is accumulative estimate mistake. Take a look at the top right-most operator index seek of [PayBatches].PayBatchID.
The predicate is [dbo].[PayBatches].DivisionPayrollID = SacOp ([@PayrollID]).
What about estimates?
"Good" plan is 1 row.
"Bad plan" is 58 rows.
Actual is 12 rows. Good plan is closer.
After that multiplying goes on!
Multiplying 1 row in nested loops according to stats estimate gives - 805 row (very close to 540 actual).
Multiplying 58 rows in nested loops according to stats (using density or historamm) gives - 42760 rows!!! Not bad!
After that all the plan goes wrong.
So, after that all we have to figure out is why the stats for
PayBathes (and probably PayEntries) is so wrong, and so missleading.
That is the poin where we need more info abot stats histogram, as a supposed earlier.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply