Snippet Name: envbatch
Tags: helpful,scriptgeneration
Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:23AM
–desc: Batch Statement Response BaseLine
Dependencies: none-standalone code
This snippet get some statistics about batch process is the case and executions were in a given time slice so for example you can identify how many take how many batches took more than 1 second 10 seconds 100 seconds at a high level it tells you how efficient queries are on the server and whether you have a lot of queries that performed poorly and need to be reviewed.
Sample Output:
--desc: Batch Statement Response BaseLine
;WITH
ServerRestart
AS
(
SELECT CONVERT(varchar(30),dbz.create_date,120) AS ServerRestartedDate from sys.databases dbz where name='tempdb'
)
,MyCTE
AS
(
SELECT *
FROM sys.dm_os_performance_counters
CROSS APPLY (SELECT
CASE
WHEN counter_name LIKE '%& <%'
THEN TRY_CONVERT(INT,SUBSTRING(counter_name,CHARINDEX('& <',counter_name) + 3,6))
ELSE TRY_CONVERT(INT,SUBSTRING(counter_name,11,6)) + 1
END AS ElapsedMS
) fn
WHERE counter_name LIKE '%batch%' AND instance_name LIKE 'Elapsed Time%'
)
,AggregateData
AS
(
SELECT
SUM(CASE WHEN instance_name = 'Elapsed Time:Total(ms)' THEN cntr_value ELSE 0 END) AS TotalMilliSeconds,
SUM(CASE WHEN instance_name = 'Elapsed Time:Requests' THEN cntr_value ELSE 0 END) AS TotalExecutions
FROM MyCTE
)
SELECT
res.ServerRestartedDate,
T1.counter_name,
CONVERT(decimal(19,2),(T1.cntr_value * 1.0) / NULLIF(T2.cntr_value,0)) AS AverageRunTimeMS,
CONVERT(decimal(19,2),((T1.cntr_value * 1.0) / NULLIF(T2.cntr_value,0)) / 1000) AS AverageRunTimeSeconds,
T2.cntr_value AS StatementCount,
CONVERT(decimal(19,2),((T1.cntr_value * 1.0) / NULLIF(agg.TotalMilliSeconds,0)) * 100) As TimePercent,
CONVERT(decimal(19,2),((T2.cntr_value * 1.0) / NULLIF(agg.TotalExecutions,0)) * 100) As CountPercent
FROM MyCTE T1
INNER JOIN MyCTE T2
ON T1.counter_name = T2.counter_name
AND T1.instance_name = 'Elapsed Time:Total(ms)'
AND t2.instance_name = 'Elapsed Time:Requests'
CROSS JOIN AggregateData agg
CROSS JOIN ServerRestart res
--desc: Batch Statement Response BaseLine
;WITH
ServerRestart
AS
(
SELECT CONVERT(varchar(30),dbz.create_date,120) AS ServerRestartedDate from sys.databases dbz where name='tempdb'
)
,MyCTE
AS
(
SELECT *
FROM sys.dm_os_performance_counters
CROSS APPLY (SELECT
CASE
WHEN counter_name LIKE '%& <%'
THEN TRY_CONVERT(INT,SUBSTRING(counter_name,CHARINDEX('& <',counter_name) + 3,6))
ELSE TRY_CONVERT(INT,SUBSTRING(counter_name,11,6)) + 1
END AS ElapsedMS
) fn
WHERE counter_name LIKE '%batch%' AND instance_name LIKE 'Elapsed Time%'
)
,AggregateData
AS
(
SELECT
SUM(CASE WHEN instance_name = 'Elapsed Time:Total(ms)' THEN cntr_value ELSE 0 END) AS TotalMilliSeconds,
SUM(CASE WHEN instance_name = 'Elapsed Time:Requests' THEN cntr_value ELSE 0 END) AS TotalExecutions
FROM MyCTE
)
SELECT
res.ServerRestartedDate,
SUM(T2.cntr_value) AS TotalBatchesExecuted,
(DATEDIFF(dd,res.ServerRestartedDate,GETDATE()))UptimeInDays,
(DATEDIFF(minute,res.ServerRestartedDate,GETDATE()))UptimeInMinutes,
SUM(T2.cntr_value) / (DATEDIFF(minute,res.ServerRestartedDate,GETDATE())) TransactionsPerMinute
FROM MyCTE T1
INNER JOIN MyCTE T2
ON T1.counter_name = T2.counter_name
AND T1.instance_name = 'Elapsed Time:Total(ms)'
AND t2.instance_name = 'Elapsed Time:Requests'
CROSS JOIN AggregateData agg
CROSS JOIN ServerRestart res
GROUP BY res.ServerRestartedDate