SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC — CPU time
cpu
How to measure Query response time in SQL Server ?
USE [Test_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[ResponseTime]
as
;with RT as(
SELECT TOP 100
[Average seconds] = qs.total_elapsed_time / qs.execution_count / 1000000.0,
[Total seconds] = qs.total_elapsed_time / 1000000.0,
[Execution count] = qs.execution_count,
[Time] = qs.last_execution_time ,
[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2),
[Parent Query] = qt.text,
qt.objectid as ‘Object_ID’,
DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where qt.dbid = DB_ID(‘Test_DB’)
ORDER BY [Average seconds] DESC)
select P.Name as [Procedure Name],RT.[Average seconds],RT.[Total seconds],RT.[Execution count],RT.[Time],RT.[Individual Query], RT.DatabaseName
from RT left join sys.procedures P on RT.Object_ID=P.object_id
Exec [ResponseTime]
selectstarfromyogi 