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 ?

 

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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?

Answer 8

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

Answer 9

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.

Answer 10

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

Answer 11

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.

Answer 12

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

Answer 13

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?

Answer 14

 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

Answer 15

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.

Answer 16

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/