Get average execution time of each command

Owned by Paul Redmond (Unlicensed)
Last updated: May 18, 2018 by Conor McCann

When diagnosing issues with long scan times, the following SQL can be useful in determining if there is a particular command that is taking a long time to execute. The output lists all commands executed and the average execution time


SELECT pa.Name as ProductAdapter, s.Name  as Strategy ,
label as CommandLabel, CommandText, avg(durationmilliseconds)/1000 as [Avg time (sec)] 
from [history].[t_CommandHistory] CH
join config.t_Strategy s on s.StrategyID=ch.StrategyID
join config.t_ProductAdapterConfiguration pa on pa.ProductAdapterConfigurationID=s.ProductAdapterConfigurationID
group by pa.Name , s.Name , label , CommandText
order by avg(durationmilliseconds) desc