?
Solved

find out which query is maxing the temp db

Posted on 2012-04-10
1
Medium Priority
?
244 Views
Last Modified: 2012-04-27
at 5pm, we find that tempdb is going to 45gb max.. we want to find the exact statement that causes it.. how would you go about finding it?
0
Comment
Question by:25112
1 Comment
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 2000 total points
ID: 37829256
Looking at the microsoft MSDN - http://msdn.microsoft.com/en-us/library/ms176029.aspx down at 'Method 2: Query Level Information'  (I've taken out the second view, just so it'll display immediately for you.  Be mindful: this does create an extra view in your current database.)

 CREATE VIEW all_request_usage
AS
  SELECT session_id, request_id,
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count
  FROM sys.dm_db_task_space_usage
  GROUP BY session_id, request_id;
GO
  SELECT R1.session_id, R1.request_id,
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;

The third and fourth columns should give you an idea which query is standing out.  The sql_handle and plan_handles can be plugged into:
SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

to output the text of the query that's filling your tempdb.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question