Adhoc queries and DB id

Question

Adhoc queries and DB id

Krishna1

Hall of Fame

Points: 3666
More actions
September 22, 2013 at 12:42 am

#298130

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, MVP, M.Sc (Comp Sci)
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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 2

Krishna1

Hall of Fame

Points: 3666

September 22, 2013 at 5:53 am

#1652188

Hi Gail

Sorry did not understand your reply.

Regards

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Try a server-side trace.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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