July 5, 2016 at 5:49 am
Hi,
I need a scrip to find top 10 expensive queries in the past 10 minutes for a specific application in SQL server.
Thanks in advance,
Yuji
July 5, 2016 at 5:53 am
Create an extended event session, run it for the 10 minutes that you want to analyse, query the resulting file.
You can use the DMVs, specifically sys.dm_exec_query_stats, but they won't necessarily contain everything as a query has to still have a plan in cache to return data in that DMV.
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
July 5, 2016 at 8:41 am
Download a copy of Glenn Berry's SQL Server Diagnostic Scripts for your version of SQL Server. An AMAZING collection of free stuff there!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 6, 2016 at 12:10 am
Thanks a lot!
July 8, 2016 at 7:17 am
TheSQLGuru (7/5/2016)
Download a copy of Glenn Berry's SQL Server Diagnostic Scripts for your version of SQL Server. An AMAZING collection of free stuff there!
From Glenn's script, I can find the Query 36 as below, but if I would like to know the top queries in the last 24 hours, how can I change the script?
-- Get top total worker time queries for entire instance (Query 36) (Top Worker Time Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text],
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time],
qs.min_elapsed_time AS [Min Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.max_elapsed_time AS [Max Elapsed Time],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],
qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
-- ,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
------
July 8, 2016 at 7:31 am
Unless you upgrade to SQL Server 2016 and use the Query Store, you're not going to be able to get what you're looking for with total accuracy. You can refine the results of the query you posted, though, with the qs.creation_time or last_execution_time column.
John
July 8, 2016 at 8:15 am
yujinagata72 (7/8/2016)
TheSQLGuru (7/5/2016)
Download a copy of Glenn Berry's SQL Server Diagnostic Scripts for your version of SQL Server. An AMAZING collection of free stuff there!From Glenn's script, I can find the Query 36 as below, but if I would like to know the top queries in the last 24 hours, how can I change the script?
No way to get that. SQL does not keep that information. If you want query details across a period of time, you will need to create an extended event session and record the workload for the time you're interested in.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply