Get top SQL Queries that aren't stored procs/functions/triggers

Question

Afternoon,

I need way to get the query stats for queries that aren't procedures/functions/triggers.

sys.dm_exec_query_stats only appears to contain these, i need to gather similar stats that are in this dmv but for just normal queries submitted by ssms/EF/Web etc.

This is what i used and it returns no results. (not the most efficient but just a quick proof of concept)

SELECT txt.text
   last_execution_time,
   execution_count,
   total_elapsed_time,
   last_elapsed_time,
   min_elapsed_time,
   max_elapsed_time
   FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS txt 
   WHERE
   txt.text NOT LIKE '%CREATE PROC%' 
   AND txt.text NOT LIKE '%CREATE FUNC%'
   AND txt.text NOT LIKE '%CREATE TRIGG%'
   AND txt.dbid = DB_ID('$(DB)')

I know there are several queries submitted regularly from client apps that don't use stored procs and take a long time to execute. I would have thought SQL keeps a record of these somewhere.

Answer 1

There's an easy (and free!) way to do that using sp_BlitzCache (full disclosure, I contribute to this OSS).

You can run it with the @QueryFilter option (which can also get only stored procs, and only functions (but functions are only 2016+).

EXEC master.dbo.sp_BlitzCache @QueryFilter = 'statement'

That will give you your top 10 statements by overall CPU. If you want to order by other metrics, you can use the @SortOrder parameter to go by reads, duration, executions, and more.

Hope this helps!

Answer 2

Aaron hinted the answer. There is a connect item filed about this. dbid being NULL for ad hoc and prepared SQL statements. There is also a workaround mentioned in the same article. It is closed now.

I slightly modified the workaround to get all the columns you need. This will run significantly longer than your query as it is getting the dbid from the plan itself.

SELECT COALESCE(Db_name(Cast(pa.value AS INT)), Replace( 
              Replace(qp.query_plan.value('(//@Database)[1]', 'nvarchar(255)'), 
              '[', 
                                                       ''), ']', ''), 
       Db_name(qp.dbid)) AS [DatabaseName], 
       qt.text, 
       last_execution_time, 
       execution_count, 
       total_elapsed_time, 
       last_elapsed_time, 
       min_elapsed_time, 
       max_elapsed_time 
FROM   sys.dm_exec_query_stats qs WITH(nolock) 
       CROSS apply sys.Dm_exec_plan_attributes(qs.plan_handle) pa 
       CROSS apply sys.Dm_exec_query_plan(qs.plan_handle) qp 
       CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE  pa.attribute = 'dbid' 
       AND qt.text NOT LIKE '%CREATE PROC%' 
       AND qt.text NOT LIKE '%CREATE FUNC%' 
       AND qt.text NOT LIKE '%CREATE TRIGG%' 
ORDER  BY total_elapsed_time DESC 

Answer 3

I finally got the right balance of performance and accuracy. The below will return the top 100 queries based on average total elapsed time.

Its for SQLCMD mode, you'll have to change that if you don't want to use it in that setting.

:SETVAR DB "TargetDB"

     WITH MostExpensive
    AS (SELECT TOP 100
            SQLTXT.text,
            STATS.last_execution_time,
            STATS.execution_count,
            STATS.total_elapsed_time,
            STATS.last_elapsed_time,
            STATS.min_elapsed_time,
            STATS.max_elapsed_time,
            STATS.total_elapsed_time / STATS.execution_count AS avg_elapsed_time,
            DB_NAME(CONVERT(INT, ATTR.value)) DBNAME
        FROM sys.dm_exec_query_stats STATS WITH (NOLOCK)
            CROSS APPLY sys.dm_exec_plan_attributes(STATS.plan_handle) ATTR
            CROSS APPLY sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTXT
        WHERE ATTR.attribute = 'dbid'
              AND SQLTXT.text NOT LIKE '%CREATE PROC%'
              AND SQLTXT.text NOT LIKE '%CREATE FUNC%'
              AND SQLTXT.text NOT LIKE '%CREATE TRIGG%'
              AND ATTR.value = DB_ID('$(DB)')
        ORDER BY avg_elapsed_time DESC
       )
    SELECT ROW_NUMBER() OVER (ORDER BY MostExpensive.avg_elapsed_time DESC) Rnk,
           *
    FROM MostExpensive;

Thanks to @SQLWorldWide who's suggestion about reading the DB from the xml, that added alot of time (3mins) but while i was playing around within dm_exe_query_plan i noticed the value column was equal to the Database ID. it might not be 100% encompassing of all traffic but its fast (1 second) and works for what i need.