locked
TempDB usage question RRS feed

  • Question

  • Hi all

    I am reading the following article in order to try troubleshoot why our TempDB exploded last night.

    http://msdn.microsoft.com/en-us/library/ms176029.aspx

    My question is around two methods in the article which are the Monitoring space use by queries and Monitoring spaced used by Temp tables and variables.

    Basically it gives different DMVs to check for this, however I am confused. Surely a temp table or variable would be called by the query in the first place and hence all the information you require would be captured in the section on monitoring the queries?

    Thanks
    Thursday, September 29, 2011 12:46 PM

Answers

  • Ok, I am wrong in my last response.  You can track internal objects and user objects with both DMVs.  One is scoped to the task executing currently, whereas the other is scoped to the session.  You can use the task DMV as shown in the original link you posted to poll the tempdb usage at intervals and get the queries causing excessive tempdb usage.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Thomas Rhea Thursday, September 29, 2011 2:09 PM
    Thursday, September 29, 2011 1:46 PM

All replies

  • The temp and table variables are tracked in the session space usage DMV only.  The task space usage DMV has information about your worktables for query execution, for example to support a hash spill or sort operation through tempdb, for intermediate results, etc.  These objects are only accessible by the execution engine, but can take a significant portion of space in tempdb.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Thursday, September 29, 2011 1:02 PM
  • Hi Jon thanks for the reply, however I have still not quite got it.

    In the article it pulls out information from the task_space_usage DMV for internal objects only and joins it to exec_requests to get the sql_handle and plan_handle.

    If I just expand on this select to include the user object columns in task_space_usage then wouldn't I also get the handles for the user objects using tempDB also?  My reasoning is that for the document about the task_space_usage it describes what a user object is, and this includes table variables etc

    http://msdn.microsoft.com/en-us/library/ms190288.aspx

    The looking at the larger numbers of allocation I would be able to work out what is causing my TempDB growths.  

    Am I missing a point here?



    • Edited by Thomas Rhea Thursday, September 29, 2011 1:38 PM
    Thursday, September 29, 2011 1:33 PM
  • Ok, I am wrong in my last response.  You can track internal objects and user objects with both DMVs.  One is scoped to the task executing currently, whereas the other is scoped to the session.  You can use the task DMV as shown in the original link you posted to poll the tempdb usage at intervals and get the queries causing excessive tempdb usage.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Thomas Rhea Thursday, September 29, 2011 2:09 PM
    Thursday, September 29, 2011 1:46 PM