August 9, 2011 at 12:00 pm
If a plan has seek operators, does it mean that it is good enough? My plan has so many scalar operators too. Not sure what to make out from the plan.
:w00t::w00t::w00t:
August 9, 2011 at 12:02 pm
chandan_jha18 (8/9/2011)
If a plan has seek operators, does it mean that it is good enough? My plan has so many scalar operators too. Not sure what to make out from the plan.:w00t::w00t::w00t:
From the first quick skim it looked decent. Plenty of small improvements, but nothing like an elephant in the room.
My guess is that this can run constantly sub-second but it's going to take a few tries if update stats is not enough.
August 9, 2011 at 12:03 pm
Ninja's_RGR'us (8/9/2011)
Yes, stats seem stale / wrong.The row estimate is 1 and the actual count is over 100K iirc. That's why we're all pounding on this one.
As per the advice from all 3 of you, i have ran the command to update with full scan for these tables. But it is taking a while to run. For one table it has been running for last 10 min.:w00t:
I will post the plan again after updating stats for all 3 tables. Thanks guys.
August 9, 2011 at 12:04 pm
chandan_jha18 (8/9/2011)
If a plan has seek operators, does it mean that it is good enough? My plan has so many scalar operators too. Not sure what to make out from the plan.:w00t::w00t::w00t:
I can easily produce a plan that has seek operators that each actually read the entire table. Really not hard.
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 9, 2011 at 12:05 pm
chandan_jha18 (8/9/2011)
Ninja's_RGR'us (8/9/2011)
Yes, stats seem stale / wrong.The row estimate is 1 and the actual count is over 100K iirc. That's why we're all pounding on this one.
As per the advice from all 3 of you, i have ran the command to update with full scan for these tables. But it is taking a while to run. For one table it has been running for last 10 min.:w00t:
I will post the plan again after updating stats for all 3 tables. Thanks guys.
That's why I wanted to go there as my 2nd option.
My FIRST option is still to see the whole proc. I think the elephant is hidden there. I understood that you do multiple pass of a similar query. The best tuning here would be to do only 1 pass if at all possible. But I need to see the whole sp to confirm that option.
August 9, 2011 at 12:14 pm
GilaMonster (8/9/2011)
chandan_jha18 (8/9/2011)
If a plan has seek operators, does it mean that it is good enough? My plan has so many scalar operators too. Not sure what to make out from the plan.:w00t::w00t::w00t:
I can easily produce a plan that has seek operators that each actually read the entire table. Really not hard.
Point in case while we're waiting =>
BEGIN TRAN
CREATE TABLE #a
(
dt DATETIME PRIMARY KEY CLUSTERED
)
INSERT INTO #a (dt)
SELECT TOP 1000 DATEADD(ms, CHECKSUM(NEWID()), GETDATE()) FROM sys.columns
SELECT * FROM #a WHERE dt < '9999-12-31'
--clustered index seek
SELECT * FROM #a
--clustered index scan
ROLLBACK
August 9, 2011 at 12:15 pm
Ninja's_RGR'us (8/9/2011)
chandan_jha18 (8/9/2011)
Ninja's_RGR'us (8/9/2011)
Yes, stats seem stale / wrong.The row estimate is 1 and the actual count is over 100K iirc. That's why we're all pounding on this one.
As per the advice from all 3 of you, i have ran the command to update with full scan for these tables. But it is taking a while to run. For one table it has been running for last 10 min.:w00t:
I will post the plan again after updating stats for all 3 tables. Thanks guys.
That's why I wanted to go there as my 2nd option.
My FIRST option is still to see the whole proc. I think the elephant is hidden there. I understood that you do multiple pass of a similar query. The best tuning here would be to do only 1 pass if at all possible. But I need to see the whole sp to confirm that option.
Please find the file sent to me by developer. It can be ran directly as he put the parameters inside it.
August 9, 2011 at 12:17 pm
GilaMonster (8/9/2011)
chandan_jha18 (8/9/2011)
If a plan has seek operators, does it mean that it is good enough? My plan has so many scalar operators too. Not sure what to make out from the plan.:w00t::w00t::w00t:
I can easily produce a plan that has seek operators that each actually read the entire table. Really not hard.
I know.Monsters can do crazy things. But how to find that such an index seek is false.
August 9, 2011 at 12:19 pm
Just to be 100% clear.
This is NOT a sp with input parameters?
If I'm wrong here I'll need to full sp, not just this 99% sample.
P.S. This runs in 5 secs? That must be a hell of a server you got there (assuming this is under load).
August 9, 2011 at 12:20 pm
chandan_jha18 (8/9/2011)
GilaMonster (8/9/2011)
chandan_jha18 (8/9/2011)
If a plan has seek operators, does it mean that it is good enough? My plan has so many scalar operators too. Not sure what to make out from the plan.:w00t::w00t::w00t:
I can easily produce a plan that has seek operators that each actually read the entire table. Really not hard.
I know.Monsters can do crazy things. But how to find that such an index seek is false.
See my sample code. Basically find a filter that based on the actual data has not shot at filtering anything (or very, very little data), but could with the right value.
August 9, 2011 at 12:21 pm
See Ninja's example.
It's not false, it's an index seek. It is validly and properly an index seek (uses the upper-levels of the b-tree to locate the start of a range of rows)
Index seek does not mean 'fetch one row'. Index seek does not necessarily mean fast.
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
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 9, 2011 at 12:27 pm
ELEPHANT.
The first 4 inserts could be merge into a single one as far as I can see.
The FROM / where / group by seem 99% identical.
A couple well placed case statements should do the trick.
PS. Distinct + group by in the same query is useless. Distinct = group by Select.*
Also I still think you don't need distinct here but I can't speak with the dev. Can you run the same query once with distinct and once withtout to see if they return the same thing?
Moreoever why the group bys? I don't see any aggregates being calculated?
This seems like it could run under 0.1 s after correct tuning!
August 9, 2011 at 12:33 pm
P.S. I'm done skimming this and there seem to be a lot of things that could be merged.
There's no way we can refactor this over a forum thread. We can help with basic tuning but this is just way to huge.
I would probably put 40 hours aside to redo this, if not more.
August 9, 2011 at 1:05 pm
Ninja's_RGR'us (8/9/2011)
ELEPHANT.The first 4 inserts could be merge into a single one as far as I can see.
The FROM / where / group by seem 99% identical.
A couple well placed case statements should do the trick.
PS. Distinct + group by in the same query is useless. Distinct = group by Select.*
Also I still think you don't need distinct here but I can't speak with the dev. Can you run the same query once with distinct and once withtout to see if they return the same thing?
Moreoever why the group bys? I don't see any aggregates being calculated?
I too noticed that this proc creates a temp table and imports data into it with select statement using groupby clauses. Why he can't simply insert without a group by and then in the end when he is reading from the temptables to display the result, he can use group by if required.
This seems like it could run under 0.1 s after correct tuning!
I too noticed that this proc creates a temp table and imports data into it with select statement using groupby clauses. Why he can't simply insert without a group by and then in the end when he is reading from the temptables to display the result, he can use group by if required.
August 9, 2011 at 1:08 pm
I stopped asking why many moons ago.. just give you headaches ;-).
Do you have the new execution plan? At least we can see how we can help there.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply