Comments posted to this topic are about the item Find Top 5 executed queries ordered by execution count
Very interesting! I really should spend some time exploring DMVs, I used to love rummaging around in system tables in older versions of SQL Server.
Having said that, I'm getting zero rows returned. I copied and ran two queries from Books Online under sys.dm_exec_query_stats , and they ran fine. I wonder if there's something slightly different under SQL 2014? My server isn't in production right now, but my development project is providing enough traffic to give me information with these two views. Unfortunately I don't know enough about DMVs right now to try to find the issue in your script.
Here's the code that I ran from BOL:
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE qs.statement_end_offset end -
) AS query_text,
qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,
qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text like '%SELECT%'
ORDER BY qs.execution_count DESC;
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 2 posts - 1 through 1 (of 1 total)