Trace TEMPDB/Temp Object Creation

  • 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.

  • 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!

  • 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/

  • 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]

  • 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