Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adhoc queries and DB id Expand / Collapse
Author
Message
Posted Sunday, September 22, 2013 12:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

I want to get last executed queries on a DB. Using dm_exec_query_plan , dm_exec_query_stats ,dm_exec_sql_text gives this information. But it shows null values for adhoc queries.

How to get DB id for adhoc queries?

Regards

Post #1497188
Posted Sunday, September 22, 2013 5:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 40,672, Visits: 37,134
Short of parsing the ad-hoc statement, figuring out which tables are there and linking them to a DB, you don't.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1497199
Posted Sunday, September 22, 2013 5:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Hi Gail

Sorry did not understand your reply.

Regards
Post #1497201
Posted Sunday, September 22, 2013 7:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 40,672, Visits: 37,134
There is no database ID for ad-hoc queries. You could try parsing the statement and figuring out which tables it uses and which DB those are in.

But then, what database should this be associated with?

USE master
SELECT * FROM db1.dbo.table1

Or this one
Use TempDB
SELECT *
Into NotTemp
FROM db1.dbo.table1 t1 inner join db2.dbo.table2 t2 on T1.SomeID = T2.SomeID

Edit: Or you might be able to parse it out of the query plan, if you're familiar with XML.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1497203
Posted Sunday, September 22, 2013 9:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Hi Gail

Thanks. My requirement is to get the last few queries which were ran on a particular DB.
How to find it. Note : these quereies could be form application/procedures/SSMS

Regards
Post #1497260
Posted Monday, September 23, 2013 12:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 40,672, Visits: 37,134
Try a server-side trace.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1497271
Posted Thursday, September 26, 2013 5:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Hi All

declare @id int
set @id = 100
select * from xxx where id =@id

if we run this through SSMS , is it considered as adhoc query?

Regrads
Post #1498766
Posted Thursday, September 26, 2013 5:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 40,672, Visits: 37,134
Yes.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498777
Posted Thursday, September 26, 2013 5:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Hi


so any query which is not part of procedure or function is considered as adhoc?
Dose this means if we have written such queries in our front end applications these are also considerd as adoc?

Regards
Post #1498779
Posted Thursday, September 26, 2013 7:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 40,672, Visits: 37,134
Yes.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse