SQL Server Performance

Which user DB is utilizing my TempDB more?

Discussion in 'SQL Server 2005 General DBA Questions' started by Balasundaram, Jun 1, 2007.

  1. Balasundaram New Member

    Hi,

    I have 4-5 databases that include the production db on the production box. Intermittently my tempdb grows to larger size.

    I am sure my production db is not utilizing tempdb much.

    I want to find out which db/application using tempdb largely.

    I think I can run SQL profiler to capture queries that is run from each db.

    I would like to here suggestions from experts to find out the easy way to identify the culprit db. Any suggestions please.

    Thanks,
    Bala
  2. Raulie New Member

    You can track space utilization with sys.dm_db_task_space_usage, refer to BOL for details.


    use the new DMVs to analyze which Transact-SQL statements are the top consumers of tempdb space as described in Monitoring space in this paper. For example you can use the following query that joins the sys.dm_db_task_space_usage and sys.dm_exec_requests DMVs to find the currently active requests, their associated TSQL statement, and the corresponding query plan that is allocating most space resources in tempdb. You may be able to reduce tempdb space usage by rewriting the queries and/or the stored procedures, or by creating useful indexes.

    SELECT t1.session_id, t1.request_id, t1.task_alloc,
    t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
    t2.statement_end_offset, t2.plan_handle
    FROM (Select session_id, request_id,
    SUM(internal_objects_alloc_page_count) AS task_alloc,
    SUM (internal_objects_dealloc_page_count) AS task_dealloc
    FROM sys.dm_db_task_space_usage
    GROUP BY session_id, request_id) AS t1,
    sys.dm_exec_requests AS t2
    WHERE t1.session_id = t2.session_id
    AND (t1.request_id = t2.request_id)
    ORDER BY t1.task_alloc DESC

    Full Articlehttp://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx


    Raulie



Share This Page