Trace TEMPDB/Temp Object Creation

Question

Trace TEMPDB/Temp Object Creation

mnDBA

SSCrazy

Points: 2032
More actions
May 11, 2009 at 6:34 pm

#134217

I want to use Profiler to trace all tempdb activity. To me, that means if I create a temp table, I want to see it. How can I do this with Profiler?
If I do:
use tempdb
go
create table #temp1(value1 int)
I can set the trace to filter based on activity in tempdb. However, if I am in another database and create the temporary table, the database id of the db I am in is returned. In this scenario I have created a temporary object, but was not in tempdb when I did it.
How can I monitor temporary object creation and therefore tempdb utilization. I do not want to see all of the other activity a given app is doing in their db, just the temp work.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i created a view to see the data in my DML trace..only thing obvious i saw was to simply do a SELECT * from myDMLView where TextData like '%#%' or DatabaseId=2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

I would set the filter on TextData column to the value - %create%table%#%

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Adi Cohn (5/11/2009)
I would set the filter on TextData column to the value - %create%table%#%
Adi

Don't forget that you can also have SELECT * INTO #TempTable and Worktables created by the query optimizer as well, so this method won't necessarily trap all tempdb operations.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Kendal,

Your best bet will likely be to capture the information from the DMV's as listed in the following Whitepaper:

http://technet.microsoft.com/en-us/library/cc966545.aspx#EEAA