May 24, 2015 at 6:22 pm
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:
DateCoderDescription
23/04/2015Albert Zhaocreated
*******************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.
May 25, 2015 at 9:52 am
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
Change is inevitable... Change for the better is not.
May 26, 2015 at 9:39 am
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.
July 3, 2015 at 7:23 pm
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
Change is inevitable... Change for the better is not.
July 6, 2015 at 12:50 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply