Solved

find out which query is maxing the temp db

Posted on 2012-04-10
1
226 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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 31
ms sql + get number in list out of total 7 44
How to keep a record with the highest value 3 38
SQL Select Query help 1 36
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

763 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