2

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.

  • Note that a procedure or function belongs to exactly one database. If you have a query like SELECT ... FROM db1.dbo.t1 JOIN db2.dbo.t2 and you run it from the context of master or tempdb, what do you expect to have for the one value of dbid in sys.dm_exec_sql_text? – Aaron Bertrand Jul 27 '17 at 16:06
  • does this mean that all queries dont have the database ID in sql text only the database objects like SP's and Functions to? dbid will be what ever is passed into the script, this is for a bunch of scripts executed by powershell in CMDMODE so i'm only looking for database specific queries. – Ollie Jul 28 '17 at 16:12
2

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!

  • hey thanks for the reply, I've used Blitz before, super awesome and useful tool so thanks for contributing to that. Unfortunately in this case I'm looking to write a suite of tailored scripts ground up to be ran from a powershell script for metric collection, for reasons of curiosity and pride mostly :) I did try your script and it does produce similar result to what i meant so up vote for that :) – Ollie Jul 28 '17 at 16:17
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 
  • Hi thanks for this, it is indeed very close to what i want, i'm working on making it more efficient though, it needs to have minimal impact on resources. I see where my Issue could be, if i remove the filter on database ID i get all the queries. So presumably the plan is the only place the database name is consistently stored and sql text only holds stored procs and functions in DBID? – Ollie Jul 28 '17 at 16:23
  • Agree. If you do not need database name, solution you mentioned is optimal. I thought you wanted the dbname also. – SqlWorldWide Jul 28 '17 at 16:29
  • Hi, i dont need it returned, but i do need to filter on it, for metrics i'm collecting are on a per DB basis. I'm about to leave work so i'll have to check this on Monday but its looks like the value column in PA relates to the database ID when attribute is dbid that could save linking to query plan table. – Ollie Jul 28 '17 at 16:35
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.