none
How to filter system query from DMV ?

    Question

  • Hi:

    I want find many information about T-SQL " User Query " from DMV, but it also return SQL Server System Query.

    How Can I remove the System Query ?  This is my sample code

    SELECT TOP 50
        TextData           = qt.text
        ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
        ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
        ,Executions         = qs.execution_count
        ,TotalCPUTime       = qs.total_worker_time
        ,AverageCPUTime     = qs.total_worker_time/qs.execution_count
        ,DiskWaitAndCPUTime = qs.total_elapsed_time
        ,MemoryWrites       = qs.max_logical_writes
        ,DateCached         = qs.creation_time
        ,DatabaseName       = DB_Name(qt.dbid)
        ,LastExecutionTime  = qs.last_execution_time
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
     ORDER BY qs.total_worker_time DESC


    my407sw

    Tuesday, February 25, 2014 7:37 AM

Answers

All replies

  • What do you mean by system query? Is it you want to have a database specific query?

    How do you know if a use is executing a query contains system tables?

    If you want to get the queries getting executed NOT on a system databases, then below would help you.

    SELECT TOP 50
        TextData           = qt.text
        ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
        ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
        ,Executions         = qs.execution_count
        ,TotalCPUTime       = qs.total_worker_time
        ,AverageCPUTime     = qs.total_worker_time/qs.execution_count
        ,DiskWaitAndCPUTime = qs.total_elapsed_time
        ,MemoryWrites       = qs.max_logical_writes
        ,DateCached         = qs.creation_time
        ,DatabaseName       = DB_Name(qt.dbid)
        ,LastExecutionTime  = qs.last_execution_time,*
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
     where DB_Name(qt.dbid) not in ('msdb','master','tempdb','model','resource') Or DB_Name(qt.dbid) is null
     ORDER BY qs.total_worker_time DESC

    Tuesday, February 25, 2014 8:06 AM
  • Thanks. I know how to filter !

    Another question, When I execute a T-SQL like select something from user database, how long can I get the information from DMV ? 

    I try select something, and query DMV immediately, but I can't find my T-SQL statement.


    my407sw

    Tuesday, February 25, 2014 8:45 AM
  • Thanks. I know how to filter !

    Another question, When I execute a T-SQL like select something from user database, how long can I get the information from DMV ? 

    I try select something, and query DMV immediately, but I can't find my T-SQL statement.


    my407sw

    That depends on which DMV that you used. If you are using sys.dm_exec_requests , then it will not show the query once the execution is completed. May be before you execute the DMV query, actual query would have been completed. Returns information about each request that is executing within SQL Server. 
    Tuesday, February 25, 2014 8:54 AM
  •  select DB_NAME(qt.dbid) from sys.dm_exec_query_stats as qs
     cross apply sys.dm_exec_sql_text(sql_handle) as qt;

    The code can show Database Name belong the SQL Query, but Can find user database, like ' Norwthind ', ' Pubs '

    even I execute SQL from them .


    my407sw

    Tuesday, February 25, 2014 8:58 AM
  • I do not get your question. You can filter as provided in the first reply. If you are looking for somethingelse, please let us know.
    Tuesday, February 25, 2014 9:24 AM
  •  select DB_NAME(qt.dbid) as DBName,qt.dbid as DBId, qt.text as 'SQLCommand' from sys.dm_exec_query_stats as qs
     cross apply sys.dm_exec_sql_text(sql_handle) as qt
     where DB_NAME(qt.dbid) is  null;

    I can't get correct DB_ID for DB_Name ! I need DB_Name to filter system query command, just only need user query ! 

    And how often when I execute SQL from user database like select something, the DMV can receive it ?


    my407sw


    • Edited by my407sw Tuesday, February 25, 2014 9:37 AM
    Tuesday, February 25, 2014 9:36 AM
  • You are getting DB_ID as NULL because those are adhoc queries. This is by design

    There is connect item for the same.Please find the connect item as below:

    CONNECT ITEM

    Tuesday, February 25, 2014 10:41 AM