Just wondering how do you manage TEMPDB?

  • i1888

    SSCommitted

    Points: 1806

    Hi Guys,

    Just wondering how do you manage TEMPDB? basically I want to find out who consumes tempdb when it is over x GB and giving me alert, below is what I thought

    Normal way

    1. Script to find out what are consuming tempdb

    SELECT TOP 10

    su.Session_ID ,

    ss.Login_Name ,

    rq.Command ,

    su.Task_Alloc ,

    su.Task_Dealloc ,

    --Find Offending Query Text:

    (SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1,

    (CASE WHEN statement_end_offset = -1

    THEN LEN(CONVERT(nvarchar(max),text)) * 2

    ELSE statement_end_offset

    END - rq.statement_start_offset)/2)

    FROM sys.dm_exec_sql_text(sql_handle)) AS 'SQLText'

    FROM

    (SELECT su.session_id, su.request_id,

    SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc,

    SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc

    FROM sys.dm_db_task_space_usage AS su

    GROUP BY session_id, request_id) AS su,

    sys.dm_exec_sessions AS ss,

    sys.dm_exec_requests AS rq

    WHERE su.session_id = rq.session_id

    AND(su.request_id = rq.request_id)

    AND (ss.session_id = su.session_id)

    AND su.session_id > 50 --sessions 50 and below are system sessions and should not be killed

    AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results

    ORDER BY su.task_alloc DESC

    --2. Implement the script in job and and triggered by the tempdb file size exceed 50GB size

    USE [msdb]

    GO

    /****** Object: Job [DBA - tempdb over 50GB] Script Date: 9/04/2015 3:59:18 PM ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 9/04/2015 3:59:18 PM ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - tempdb over 50GB',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=0,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'OdysseyAdmin', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Capture quiries when tempdb size over 50GB] Script Date: 9/04/2015 3:59:18 PM ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Capture quiries when tempdb size over 50GB',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

    SET @xml =CAST((

    SELECT TOP 5 --Change number accordingly

    su.Session_ID AS ''td'','''',

    ss.Login_Name AS ''td'','''',

    rq.Command AS ''td'','''',

    su.Task_Alloc AS ''td'','''',

    su.Task_Dealloc AS ''td'','''',

    --Find Offending Query Text:

    (SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1,

    (CASE WHEN statement_end_offset = -1

    THEN LEN(CONVERT(nvarchar(max),text)) * 2

    ELSE statement_end_offset

    END - rq.statement_start_offset)/2)

    FROM sys.dm_exec_sql_text(sql_handle)) AS ''td''

    FROM

    (SELECT su.session_id, su.request_id,

    SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc,

    SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc

    FROM sys.dm_db_task_space_usage AS su

    GROUP BY session_id, request_id) AS su,

    sys.dm_exec_sessions AS ss,

    sys.dm_exec_requests AS rq

    WHERE su.session_id = rq.session_id

    AND(su.request_id = rq.request_id)

    AND (ss.session_id = su.session_id)

    AND su.session_id > 50 --sessions 50 and below are system sessions and should not be killed

    AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results

    ORDER BY su.task_alloc DESC --The largest "Task Allocation/Deallocation" is probably the query that is causing the db growth

    FOR XML PATH (''tr''), ELEMENTS ) AS NVARCHAR(MAX))

    --BODY OF EMAIL - Edit for your environment

    SET @body =''<html><H1>Tempdb Large Query</H1>

    <body bgcolor=white>The query below with the <u>highest task allocation

    and high task deallocation</u> is most likely growing the tempdb. NOTE: Please <b>do not kill system tasks</b>

    that may be showing up in the table below.

    <U>Only kill the query that is being run by a user and has the highest task allocation/deallocation.</U>

    To stop the query from running, do the following:

    1. Open <b>SQL Server Management Studio</b>

    2. <b>Connect to database engine using Windows Authentication</b>

    3. Click on <b>"New Query"</b>

    4. Type <b>KILL [type session_id number from table below];</b> - It should look something like this: KILL 537;

    5. Hit the <b>F5</b> button to run the query

    This should kill the session/query that is growing the large query. It will also kick the individual out of the application.

    You have just stopped the growth of the tempdb, without having to restart SQL Services, and have the large-running query available for your review.

    <table border = 2><tr><th>Session_ID</th><th>Login_Name</th><th>Command</th><th>Task_Alloc</th><th>Task_Dealloc</th><th>Query_Text</th></tr>''

    SET @body = @body + @xml +''</table></body></html>''

    --Send email to recipients:

    EXEC msdb.dbo.sp_send_dbmail

    @recipients =N''albert.zhao@wisetechglobal.com'', --Insert the TO: email Address here

    --@copy_recipients =''dba_Manager@domain.com'', --Insert the CC: Address here; If multiple addresses, separate them by a comma (,)

    @body = @body,@body_format =''HTML'',

    @importance =''High'',

    @subject =''TempDB warning'', --Provide a subject for the email

    --@profile_name = ''DatabaseMailProfile'' --Database Mail profile here',

    @database_name=N'tempdb',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

    --3. Create kill high tempdb usage session stored procedure

    USE [master]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create procedure [dbo].[Kill_High_TempDB_Usage_Session]

    @var_tempdb_usage_sizeMB smallint = 40

    as

    /*

    Summary: Find out spid which consumes more than @var_tempdb_usage_sizeMB tempdb size and kill it

    Description: kill session which consumes more than @var_tempdb_usage_sizeMB tempdb size and kill it, then shrink TempDB to 2GB (log size 512MB)

    ChangeLog:

    Date Coder Description

    23/04/2015 Albert Zhao created

    *******************13/05/2015*********************/

    SET NOCOUNT ON

    begin

    declare @kill_command varchar(max)

    set @kill_command = ''

    select @kill_command = @kill_command + 'kill ' + cast(tbl.session_id as varchar(5)) from

    (SELECT dbsu.session_id

    from sys.dm_db_session_space_usage dbsu

    join sys.sysprocesses sp on sp.spid = dbsu.session_id

    where sp.cmd <> 'KILLED/ROLLBACK' and dbsu.database_id = 2 and dbsu.session_id > 50 and ((dbsu.user_objects_alloc_page_count + dbsu.internal_objects_alloc_page_count) * 8.0)/1024 >=@var_tempdb_usage_sizeMB

    ) as tbl

    end

    --4. Create alert and kill the sessions

    USE [msdb]

    GO

    /****** Object: Alert [SQL Alert - tempdb size over 50GB] Script Date: 12/05/2015 2:14:46 PM ******/

    EXEC msdb.dbo.sp_add_alert @name=N'SQL Alert - tempdb size over 50GB',

    @message_id=0,

    @severity=0,

    @enabled=1,

    @delay_between_responses=0,

    @include_event_description_in=0,

    @category_name=N'[Uncategorized]',

    @performance_condition=N'Databases|Data File(s) Size (KB)|tempdb|>|52428800',

    @job_name=N'DBA - tempdb over 50GB'

    GO

    SCOM Way

    1. same query as the last one.

    2. also Check tempdb size.

    either 1 or 2 exceeds a certain value, SCOM alerts.

    Because we will move all monitoring to SCOM, so I will go for SCOM eventually, alert is not just enough, I will add stored procedure to kill highest tempdb use session or manual check, a automated way is more preferred, does anyway have any idea/job done? e.g. safely and efficiently manage tempdb usage.

  • Jeff Moden

    SSC Guru

    Points: 996645

    One thing that I can tell you is that I never automatically kill a spid. If you have to do that, then you have much bigger problems such as privs being out of control or not having the proper tools available for your users.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Kris Gruttemeyer

    Ten Centuries

    Points: 1185

    My primary question would be 'why?'

    Automatically killing SPIDs is never a good idea. What if these are legitimate queries that simply need to use tempdb? Maybe there's a lot of join or sorting that has to be done.

    If you're worried about autogrowths, then why not just size your tempdb files larger initially? Storage is pretty inexpensive so I'm not sure what the end-game of you doing this would be.

  • Jeff Moden

    SSC Guru

    Points: 996645

    To add to what Kris stated, we have several databases that are a bit over a 3rd of a Tera-Byte on a single instance. That instance has 8 - 2GB MDF/NDF files and 1 - 2GB LDF file. We do some seriously heavy lifting in all of those databases in the form of batch files on top of one of them being the primary OLTP database. We've never needed more than 2GB of TempDB on any given run.

    My point about having larger problems before is that you REALLY need to check code that causes TempDB growth over that for accidental Cross Joins in the form of many-to-many joins and, perhaps, use some "Divide'n'Conquer" methods to take control over large monolithic queries that have more than 10 joins. And it's not just for the sake of TempDB... it's for the sake of performance and the overall reduction of what is usually gross unnecessary use of resources such as memory, TempDB, CPU, and I/O.

    If, as a DBA, you're just killing long running or high resource usage SPIDs, then you're not really doing your job as a DBA. Yes, I know it's because someone hasn't done THEIR job as an intelligent/skilled developer but, quite literally, "forgive them for they know not what they do" and then start teaching them a better way. Simply killing SPIDs will drive an even bigger wedge between you and the people who don't know what they're doing. [font="Arial Black"]HELP THEM[/font] instead of just sitting in an ivory tower killing SPIDs as they crawl by. It'll be good for you, good for them, and good for the company that's paying all of you to work smarter.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jayanth_Kurup

    SSC-Insane

    Points: 22967

    Imagine killing a SPI for an insert statement that was inserting a million rows the time to rollback would be huge. The only time it would be safe to kill a spid would be if its a select and even then its not a good idea and should be more of a last resort after understanding the impact.

    Tempdb growing on its own it not an issue if it has freespace in it.

    e.g

    my tempdb started out at 20 GB and then grew to 100GB

    once the transaction committed and the session disconnected all the space allocated (80GB ) is freed up back to SQL ( not the OS). and now SQL can use this space for any new Temp objects being created.

    If I ever want that space back i can simply shrink the database.

    Jayanth Kurup[/url]

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply