July 4, 2013 at 5:26 am
Hi ,
Someone can give me a script that return the top 10 sql queries based on elapsed time for the past 7 days.
Please ?
July 4, 2013 at 10:30 am
I'm not exactly sure what you are looking for. I suspect if it's for any specific database that you will required a tool. Check out the link.
What are you trying to achieve from this result? That may help with responses.
July 4, 2013 at 10:38 am
Hi , it's for sqlserver
I want to see every friday the top 10 query of the week that take more time
July 4, 2013 at 10:41 am
Did you check out the link?
July 4, 2013 at 10:56 am
Unless you store all the queries that run on your server (or at least the more expensive), there's no way you can obtain them from a script.
However, you can use something like this to start a query that will fit your needs. Remember that this will only return queries that are still on cache and it might not bring queries for several days depending on many things.
SELECTt.text,
s.*
FROM (SELECT TOP 10 *
FROM sys.dm_exec_query_stats
ORDER BY max_elapsed_time DESC) s
CROSS APPLY sys.dm_exec_sql_text(sql_handle ) t
Remember that this is just a start.
July 4, 2013 at 1:53 pm
There is no quick answer to this one. Luis gave you a query, but it has nothing to do with the last seven days. It just gives you a dump of the plan cache, and returns the queries with the max elapsed time in the cache right now.
Now, max elapsed time is not necessarily any of your top 10 queries. If there is an evil one-off query than ran for an hour, or a query that runs for two minutes every time, and has runs 10000 times, which query do you want to see?
Since the query is a snapshot of the cache, this means that you may see queries that have been in the cache for months. Or you may not see all queries that have executed last week, because plans have been flushed or recompiled.
If you want something like this, you need to snapshot the cache to table regularly, and then you can start analysing. This is not a trivial job, and you need to have good understanding of what you are looking for.
Or you go out and buy a third-party too.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 4, 2013 at 5:01 pm
Thank you for completing the information Erland. I just gave a starting point but as you've said, there's a long way to go from there (unless the OP takes the easy path and gets a third party tool).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply