none
Recent executed queries for a specific database

    Question

  • A google search shown me the following.

    SELECT top 10 *
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC
    

     

    But it  deosnt tell anything on the Database and the Dbid is something else and do not have any relations with the database id.

    I want to filter only the records from a particular Database. How do I do it? Can I get the hostname or username too ?

     

    Friday, January 22, 2010 10:20 AM

Answers

  • Much confusion in this thread results from incorrect documentation of sys.dm_exec_sql_text.dbid. It's only non-null for stored procedures.

    You need sys.dm_exec_plan_attributes for ad-hoc batches and stored procedures.  eg

    select qt.dbid, pa.value dbid_a, qt.text, qs.*
    from sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
    cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
    where pa.attribute = 'dbid'
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, November 20, 2013 8:56 PM

All replies

  • SELECT top 10 *
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    where dbid = (select database_id from sys.databases where name = 'your database name')
    ORDER BY deqs.last_execution_time DESC
    Friday, January 22, 2010 10:27 AM
  • USE db_name(dbid) and filter based on required database

    SELECT

     

    top 10 db_name(dbid),*

    FROM

     

    sys.dm_exec_query_stats AS deqs

    CROSS

     

    APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    where

     

    dbid = db_id('databasename')

    ORDER

     

    BY deqs.last_execution_time DESC


    Ranjith | My Blog
    Friday, January 22, 2010 10:28 AM

  • The complete query requested which gets the host name and login name as well.

    SELECT top 10 db_name(dbid),ses.host_name, ses.login_name,*

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    JOIN sys.dm_exec_requests req ON deqs.sql_handle = req.sql_handle

    JOIN sys.dm_exec_sessions ses ON ses.session_id = req.session_id

    where dbid = db_id('databasename')

    ORDER BY deqs.last_execution_time DESC

     


    Ranjith | My Blog
    Friday, January 22, 2010 10:41 AM

  • Hi Ramireddy & Ranjith,

    It seems that both of you have not noticed what I said in the first post. The dbid from sys.dm_exec_sql_text is not the database id or atleast in my case. Here is the result of the followin query

    SELECT top 100 dbid,text
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC




    NULL Select * from ptg_revisit..LOAD_STATUS
    NULL SELECT top 10 db_name(dbid),ses.host_name, ses.log
    NULL Select * from LOAD_STATUS
    NULL   SELECT top 100 dbid,left(text,50)  FROM sys.dm_e
    NULL SELECT top 100 dbid,text  FROM sys.dm_exec_query_s
    32767 create procedure sys.sp_helpfile  @filename sysnam
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    NULL update #spdbdesc          set dbsize = (select str
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_helpdb  -- 1995/12/20 15:3
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767   create procedure sys.sp_sproc_columns   (      @
    NULL   SELECT top 10 db_name(dbid),ses.host_name, ses.l
    NULL   SELECT top 10 db_name(dbid),ses.host_name, ses.l
    NULL   SELECT top 100 dbid,text  FROM sys.dm_exec_query
    NULL SELECT  distinct dbid  FROM sys.dm_exec_query_stat
    NULL   SELECT  distinct top 100 dbid  FROM sys.dm_exec_
    NULL Select top 10 * from sys.dm_exec_sql_text(0x020000
    NULL SELECT top 10 *  FROM sys.dm_exec_query_stats AS d
    NULL Select top 10 * from sys.dm_exec_query_stats
    NULL SELECT top 100 *  FROM sys.dm_exec_query_stats AS 
    NULL SELECT top 100 dbid,*  FROM sys.dm_exec_query_stat
    NULL SELECT top 10 dbid,*  FROM sys.dm_exec_query_stats
    NULL SELECT top 10 dbid,*  FROM sys.dm_exec_query_stats
    NULL SELECT top 10 *  FROM sys.dm_exec_query_stats AS d
    NULL   SELECT top 10 *  FROM sys.dm_exec_query_stats AS
    NULL SELECT er.log_id AS [LogID], er.event_type AS [Eve
    1 Create Proc sp_sessions(@dbname sysname='') AS  Be
    NULL   SET nocount off    SELECT               SPID    
    1 Create Proc sp_sessions(@dbname sysname='') AS  Be
    32767 create procedure sys.sp_who2  --- 1995/11/03 10:16
    32767 create procedure sys.sp_who2  --- 1995/11/03 10:16
    NULL Select * from ptg_revisit..tmp_stat  
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpconstraint      @objna
    32767 create procedure sys.sp_helpindex   @objname nvarc
    32767 create procedure sys.sp_helpindex   @objname nvarc
    32767 create procedure sys.sp_helpindex   @objname nvarc
    32767 create procedure sys.sp_helpindex   @objname nvarc
    32767 create procedure sys.sp_helpindex   @objname nvarc
    32767 create procedure sys.sp_helpindex   @objname nvarc
    32767 create procedure sys.sp_objectfilegroup --- 1996/0
    32767 create procedure sys.sp_objectfilegroup --- 1996/0
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767 create procedure sys.sp_help   @objname nvarchar(7
    32767   create procedure sys.sp_sproc_columns   (      @
    NULL SELECT top 10 *  FROM sys.dm_exec_query_stats AS d
    NULL Select * from ptg_revisit..LOAD_STATUS  
    NULL   SET nocount off    SELECT               SPID    
    NULL   SELECT       Count(1)    FROM     tbl_ptg_var_pe
    NULL SELECT top 100 deqs.*, dest.*  FROM sys.dm_exec_qu
    NULL SELECT dtb.name AS [Name] FROM master.dbo.sysdatab
    1 Create Proc sp_sessions(@dbname sysname='') AS  Be
    32767 create procedure sys.sp_helptext  @objname nvarcha
    32767 create procedure sys.sp_helptext  @objname nvarcha
    32767 create procedure sys.sp_helptext  @objname nvarcha
    32767 create procedure sys.sp_helptext  @objname nvarcha
    32767 create procedure sys.sp_helptext  @objname nvarcha
    32767 create procedure sys.sp_helptext  @objname nvarcha
    32767 create procedure sys.sp_helptext  @objname nvarcha
    NULL SELECT u.name AS [Name] FROM sys.database_principa
    NULL SELECT CAST(cast(g.name as varbinary(256)) AS sysn
    NULL    SELECT dtb.is_ansi_null_default_on AS [AnsiNull
    NULL (@1 tinyint,@2 varchar(8000),@3 numeric(4,0))SELEC
    NULL use [ptg_revisit] SELECT is_member(N'db_accessadmi
    NULL SELECT dtb.name AS [Name], (select count(*) from m
    NULL SELECT dtb.collation_name AS [Collation], dtb.name
    NULL  Select Count(1)    FROM        tbl_ptg_var_per vp
    NULL Select * from ptg_revisit..LoadStatus
    NULL select  Mortgage_Term_Amount,  count(Mortgage_Term
    NULL   SELECT top 100 deqs.*, dest.*  FROM sys.dm_exec_
    NULL   SELECT top 100 deqs.*, dest.*  FROM sys.dm_exec_
    NULL SELECT top 100 deqs.*, dest.*  FROM sys.dm_exec_qu
    NULL   SELECT top 100 deqs.*, dest.*  FROM sys.dm_exec_
    NULL Select * from sys.tables where name='LOAD_STATUS'
    NULL SELECT top 100 deqs.*, dest.*  FROM sys.dm_exec_qu
    NULL SELECT 'Server[@Name=' + quotename(CAST(serverprop
    NULL (@1 nvarchar(4000))SELECT CONVERT([bit],has_dbacce


    You can see that most of the dbid is null and the not null values are like 32767 etc.. and those are not  valid dbid s.
    All the dbids in my sys.databases are below 20.

    So when I run both of your queries, I get no result.

    Friday, January 22, 2010 10:58 AM
  • And when I run your query without the WHERE clause it always gives me only one record that is the text of the same query!

    SELECT top 10 db_name(dbid),ses.host_name, ses.login_name,text
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    JOIN sys.dm_exec_requests req ON deqs.sql_handle = req.sql_handle
    JOIN sys.dm_exec_sessions ses ON ses.session_id = req.session_id
    ORDER BY deqs.last_execution_time DESC

     

    NULL MSSLAP89 INDIA\user10463     SELECT top 10 db_name(dbid),ses.host_name, ses.login_name,text  FROM sys.dm_exec_query_stats AS deqs  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest  JOIN sys.dm_exec_requests req ON deqs.sql_handle = req.sql_handle  JOIN sys.dm_exec_sessions ses ON ses.session_id = req.session_id  --where dbid = db_id('ptg_revisit')  ORDER BY deqs.last_execution_time DESC

    Friday, January 22, 2010 11:14 AM
  • The dbid column is the actual database id in the server but there are two special cases NULL and 32767 for system usage. 32767 is the 'Resource' database which SQL server internally uses and is not controlled by user and for NULL see the books online http://technet.microsoft.com/en-us/library/ms181929.aspx . The NULL in dbid indicates that those queries are Ad Hoc queries.

    For your case to get the user queries in user databases use the condition like dbid IS NOT NULL AND dbid <> 32767 



    Ranjith | My Blog
    Friday, January 22, 2010 11:21 AM
  • Then it doesnt show much records only four five records with dbid 1,4 (If it is actually the database ids, then for master and msdb).

    Why it shows all the dbid as nulls?. You can see  from my previous post that the veryfirst record in the buffer is " Select * from ptg_revisit..LOAD_STATUS"   which was a query I just ran on against a database ptg_revisit which has a dbid 12. But in the results the dbid is shown as NULL. That is why I suspected the dbid is something other than the database id.

    Any work around?
    Friday, January 22, 2010 11:36 AM

  • For sure DBID is there to represent the database id but for certain queries SQL Server is logging it as a NULL i.e.  As per the BOL link i gave above for all the Adhoc and prepared queries it logs the dbid as NULL. I am not sure why ? why cant proper dbid?.

    And i did a couple of AdHoc Selects and Updates and it logged all of them with dbid NULL. But I executed a SP then it logged it with proper ID. I used the left join with exec_requests and sessions because i see not all are logged there.

    SELECT top 10 host_name,

                login_name,

                CASE WHEN dbid IS NULL THEN 'Ad Hoc Query'

                     WHEN dbid = 32767 THEN 'Resource Database'

                  ELSE DB_NAME(dbid)

                END AS [Database],

                text As query,

                *

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    LEFT JOIN sys.dm_exec_requests req ON deqs.sql_handle = req.sql_handle

    LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = req.session_id

    ORDER BY deqs.last_execution_time DESC


    Do not see any work around immediately. I will see if time available. 
    Ranjith | My Blog
    Friday, January 22, 2010 12:56 PM
  • Thanks Ranjith for the reply.
     But the solution you posted will not help me becuase most of the queries that I want to trace are adhoc only and not SPs. The Database name,hostname,login name(All the fields relevent to me) are NULL and hence  I need to seek other ways to acheive it. Reading from the buffer doesnt seems to be helpful.


    Thanks.
    Wednesday, January 27, 2010 11:28 AM

  • For sure DBID is there to represent the database id but for certain queries SQL Server is logging it as a NULL i.e.  As per the BOL link i gave above for all the Adhoc and prepared queries it logs the dbid as NULL. I am not sure why ? why cant proper dbid?.


    A stored procedure belongs to a specific database hence the dbid is known. 

    An ad-hoc query can touch 5 different databases or not a hava a database reference at all (execute in current db context).

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server 2012



    Monday, February 01, 2010 6:38 AM
    Moderator

  • A stored procedure belongs to a specific database hence the dbid is known. 

    An ad-hoc query can touch 5 different databases or not a hava a database reference at all (execute in current db context).
    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com

    But it should have a session pointed to a database even if the query touches other databases too and of course a login name(windows or sql) and a hostname in which the query is being executed(as eveident from sp_who2). All those are blank.
    Monday, February 01, 2010 6:46 AM
  • In theory yes. There is a known current database context. But that may not what you want. Example:

    USE AdventureWorks;
    -- Ad-hoc query
    SELECT * FROM AdventureWorks2008.Production.Product



    You can get the information you are looking for by running a trace (server-side trace or SQL Server Profiler trace).


    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Monday, February 01, 2010 6:49 AM
    Moderator
  • Thanks SQLUSA.

       Let me to explain my problem briefly.  I need this information periodically say once in a day and will insert these info to a pemenant table as SQL cache info will be lost by aging or by server re-starting. I think it is an in-expensive way of achieving what I want in a Production server.

    But when I run the profiler, (I beleive so) I can only cactch those info only during the period when the Profiler runs. ie, I cannot retrive the past information ny running the profiler.  Running the profiler on a 24 hours  basis is not a good idea on a production server.


    Do you have any different thought?

    Monday, February 01, 2010 7:01 AM
  •  Running the profiler on a 24 hours  basis is not a good idea on a production server.


    Do you have any different thought?

    Agreed. You would use the profiler for a targeted period only.

    You can ask the development team to change the ad-hoc queries to use 3-part naming like AdventureWorks2008.Production.Product.

    Then you can parse out the database name from the query text.

    Server-side tracing:

    http://www.sqlusa.com/bestpractices/createtrace/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server 2012



    Monday, February 01, 2010 7:38 AM
    Moderator
  • How about this?

    SELECT host_name,login_name, CASE WHEN a.dbid IS NULL THEN database_id WHEN a.dbid = 32767 THEN 'Resource Database' ELSE a.dbid END dbid, text

    FROM sys.sysprocesses sp

    LEFT JOIN sys.dm_exec_sessions es ON sp.spid = es.session_id

    LEFT JOIN (SELECT dbid, session_id, text FROM sys.dm_exec_requests er (NOLOCK)

       CROSS APPLY sys.dm_exec_sql_text(sql_handle) t ) a ON es.session_id = a.session_id

    Bear in mind this is for SQL 2008 - for SQL 2012 sys.dm_exec_requests now has the database id in the table.

    Wednesday, November 20, 2013 8:38 PM
  • Much confusion in this thread results from incorrect documentation of sys.dm_exec_sql_text.dbid. It's only non-null for stored procedures.

    You need sys.dm_exec_plan_attributes for ad-hoc batches and stored procedures.  eg

    select qt.dbid, pa.value dbid_a, qt.text, qs.*
    from sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
    cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
    where pa.attribute = 'dbid'
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, November 20, 2013 8:56 PM