May 11, 2009 at 6:34 pm
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.
May 11, 2009 at 10:08 pm
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
May 11, 2009 at 10:31 pm
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/
May 11, 2009 at 10:51 pm
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 | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 11, 2009 at 10:54 pm
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
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply