TempDB Performance Recomendation and Tempdb Growing Out of Control for Alert.
1.Use #temp tables and @table variables
2. The temdb can also be used for internal operations like rebuilding indexes (when the SORT_IN_TEMPDB is ON), queries using UNION, DBCC checks, GROUP BY, ORDER BY. Hash join and Hash aggregate operations.
3.Avoid unnecessary cursors; avoid static cursors and this may be a bottleneck.
4.Try to avoid CTE(common table expressions).
Configure for multiple DATA Files:
- We have recommended to create the multiple data files for Tempdb, use multiple data files is to increase the I/O throughput to tempdb and especially if it’s running on very fast storage.
- Divide the total space by 9, and that’s your size number. Create 8 equally sized data files and one log file.
The number of files depends upon the logical processors.
3. If possible, move tempdb to different disks than the user databases. That will increase the performance.
4. if we can create indexes in user databases. Rebuild or Reorganize will decrease the load of indexes in frequency and reduce the tempdb use.
Consider trace flag 1117 and 1118:
These are two trace flags which are useful to avoid contention in TempDB database. The most common trace flag is 1118 which prevents contention on the SGAM pages by slightly changing the allocation algorithm used. When trace flag 1118 is enabled, the allocation in TempDB are changes from a single page at a time from a mixed extent (8 times) to allocate an extent of 8 pages. So when there are multiple temp tables creation in TempDB database, allocation bitmap contention would be alleviated.
trace flag 1117 changes the auto-grow algorithm in SQL Server. It is always recommended to manually grow the data files. This is because when SQL Server performs auto-grow of data files, it is done one data file at a time in a round robin fashion. When this happens, SQL Server auto-grows the first file, writes to it until it is filled and then auto-grows the next data file. If you observed, the proportional fill is broken now. When trace flag 1117 is enabled, then when SQL Server has to perform auto-grow of a data file, it auto-grows all of the files at the same time.
Tempdb growing outof controll alert configuration:
When TempDb outof control automatically alert triggred through Database mail alert.
SQL Server Management Studio (SSMS), under the SQL Server Agent node, right-click and select New Alert
Next, go to the Response tab on the left-hand side of the window. Select the checkbox Execute job and click on the New Job.
Alert for query growingTempdb :
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST((
SELECT TOP 1 — — 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><BR>
<BR>
To stop the query from running, do the following:<BR>
<BR>
1. Open <b>SQL Server Management Studio</b><BR>
2. <b>Connect to database engine using Windows Authentication</b><BR>
3. Click on <b>”New Query”</b><BR>
4. Type <b>KILL [type session_id number from table below];</b> — It should look something like this: KILL 537; <BR>
5. Hit the <b>F5</b> button to run the query<BR>
<BR>
This should kill the session/query that is growing the large query. It will also kick the individual out of the application.<BR>
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.
<BR>
<BR>
<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’ajay@gmail.com’, — Insert the TO: email Address here
@copy_recipients =’ajay@gmail.com’, — Insert the CC: Address here; If multiple addresses, separate them by a comma (,)
@body = @body,@body_format =’HTML’,
@importance =’High’,
@subject =’alert for Tempd DB’, — Provide a subject for the email
@profile_name = ‘Testprofiler’ — Database Mail profile hereCreate DB mail and ad notification.
