May 14, 2009 at 3:26 pm
Please help teach an old dog a new trick :hehe:
For example I want top 10 usecounts by objtyp for the below query
SELECT top 10 usecounts, cacheobjtype, objtype, plan_handle
FROM sys.dm_exec_cached_plans
ORDER BY USECOUNTS DESC
John Zacharkan
May 14, 2009 at 5:19 pm
This what you are looking for?
select
objtype,
usecounts,
cacheobjtype,
plan_handle
from
(
Select s.objtype,
s.cacheobjtype,
s.plan_handle,
s.usecounts,
rank() over (partition by s.objtype order by (s.usecounts) desc) as UCRank
from sys.dm_exec_cached_plans s
)
UCRanks
where UCRanks.UCRank <= 10
A.J.
DBA with an attitude
May 14, 2009 at 10:59 pm
Or maybe...
;with distinctObjTypes as
(SELECT DISTINCT objtype FROM sys.dm_exec_cached_plans)
--
SELECT d.objtype,ca.*
FROM distinctObjTypes d
CROSS APPLY
(SELECT top 10 usecounts, cacheobjtype, plan_handle
FROM sys.dm_exec_cached_plans
WHERE ca.objtype = d.objtype
ORDER BY USECOUNTS DESC) ca
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 14, 2009 at 11:05 pm
Bob Hovious (5/14/2009)
Or maybe...
[font="Verdana"]I thought of that approach too (I have introduced my team to the joys of CROSS APPLY, thank you Bob!) But I suspect the straight ranking approach will be better, as the CROSS APPLY sort of does like a sub-query per row.[/font]
May 14, 2009 at 11:48 pm
Thanks All !
I like the rank approach and have already passed it on to the staff.
John Zacharkan
May 15, 2009 at 2:09 am
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
Failing to plan is Planning to fail
May 15, 2009 at 1:25 pm
Bruce, how does the RANK approach differ from ROWNUMBER()?
Also, I don't believe that CROSS APPLY will always do a subquery per row. The optimizer may surprise you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 17, 2009 at 12:14 pm
Hi Bob
Bob Hovious (5/15/2009)
Bruce, how does the RANK approach differ from ROWNUMBER()?
I would say, it depends on the data and the requirements.
ROW_NUMBER() is always incremental. RANK() and DENSE_RANK() increment by the rank of the ORDER criteria with different handling of the next number.
Also, I don't believe that CROSS APPLY will always do a subquery per row. The optimizer may surprise you.
I have to confirm Bruce. I had situations where CROSS APPLY became slower than a CTE with a PARTITION ROW_NUMBER.
Greets
Flo
May 18, 2009 at 2:34 pm
Arrrgg! Flo, I just realized I never proofed your article!
I probably shouldn't have thrown the CROSS_APPLY suggestion in. Row_Number() with partitioning is quite quick for pulling the top N of each group. I just wondered how it performed compared to Rank() when looking for the TOP N rows.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 18, 2009 at 2:50 pm
Bob Hovious (5/18/2009)
Arrrgg! Flo, I just realized I never proofed your article!
No problem :-). Take the time you need or give me a short hint if you don't have time.
May 18, 2009 at 3:08 pm
Florian Reischl (5/18/2009)
Bob Hovious (5/18/2009)
Arrrgg! Flo, I just realized I never proofed your article!No problem :-). Take the time you need or give me a short hint if you don't have time.
Flo, I have made one pass through your article. I am now on pass two.
May 18, 2009 at 3:14 pm
Lynn Pettis (5/18/2009)
Florian Reischl (5/18/2009)
Bob Hovious (5/18/2009)
Arrrgg! Flo, I just realized I never proofed your article!No problem :-). Take the time you need or give me a short hint if you don't have time.
Flo, I have made one pass through your article. I am now on pass two.
Lynn, thanks a lot for all your effort!! Take all the time you need. If you have any questions, if I can do anything to help; let me know!
Greets
Flo
May 18, 2009 at 3:19 pm
Bob Hovious (5/15/2009)
Bruce, how does the RANK approach differ from ROWNUMBER()?Also, I don't believe that CROSS APPLY will always do a subquery per row. The optimizer may surprise you.
[font="Verdana"]Agreed on your latter point. I did say "sort of" -- I will use ranking over cross apply because then the optimiser won't surprise me in a bad way.
ROW_NUMBER() is a ranking function, isn't it? I did say ranking, not RANK().
[/font]
May 18, 2009 at 4:09 pm
Mr. Wilbur said RANK()
rank() over (partition by s.objtype order by (s.usecounts) desc) as UCRank
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 18, 2009 at 4:17 pm
Bob Hovious (5/18/2009)
Mr. Wilbur said RANK()rank() over (partition by s.objtype order by (s.usecounts) desc) as UCRank
[font="Verdana"]Erk. He did too!
Okay, key difference is this:
SQL Server Books Online
If two or more rows tie for a rank, each tied rows receives the same rank.
Whereas ROW_NUMBER() will give a unique number to each row within the same partition.
You will have to ask Mr. Wilbur why he used RANK() and not ROW_NUMBER() though. The way he's written it, if you have two objects in the top 10 that have the same usage count, it could actually return more than ten for that object type. If you always want 10 and only 10, then ROW_NUMBER() would probably be better.
[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply