There are different SQL Server tuning approaches. A common approach is to track down long running transactions and optimise. The limitation with that approach is it excludes short – running but high frequency queries . Likewise, focusing only on short high frequency transactions excludes long running transactions.

The DMV sys.dm_exec_query_stats returns informations about cachedquery plans. When the cache is cleared , such as a restart , the information is cleared.

A colleague of mine uses this query . This query is ordered by sys.dm_exec_query_stats.total_elapsed_time. The total_elapsed_time column represents the Total elapsed time , in microseconds, for successfully completed executions . Regardless of whether it has a high execution count or is a low execution count but long running transaction

When users are complaining about severe problems , this query gives you a quick insight into the major query activity. It may not give you the root cause of an issue – therefore you many need to spend time on a more methodical approach such Waits and Queues analysis .