Problem
Properly sizing the TempDB database has recently surfaced with some new customers and has also been a thread posted in the MSSQLTips forums. Here is the general scenario: On all of my SQL Server instances (development, test and production) the TempDB database is configured for auto growth for the database and transaction log. During a recent performance tuning engagement I learned a lot about how TempDB is being used in my environment. It was brought to my attention how much the TempDB database is being used by our applications. I had no idea our core business applications are using the TempDB database so heavily. In addition, I did not know how large the TempDB database and transaction log have grown. As such, I know I cannot change the applications in short order so I need to get a handle on how to resolve these issues and size the TempDB database correct. How should I do so?
Solution
On many SQL Server instances the TempDB database is set to default configurations which I believe are an 8 MB database and a 1 MB transaction log with unrestricted growth for both the database and the log file. As compared to other environments where TempDB probably started with default configurations and is now tens of gigabytes in size unknown to the IT team. TempDB is a unique database for a number of reasons which include:
- The database only stores transient data such as
- Temporary tables (#temptable or ##temptale)
- Table variables
- Cursors
- Work tables
- Row versioning which previously occurred in the user defined database transaction log or to support new features such as Multiple Active Result Sets (MARS)
- Create or rebuild indexes sorted in TempDB
With TempDB potentially playing such a large role it is necessary to really understand when the TempDB database grows and then start to dig into the processing during that time to see how it can be tuned in order to improve the process. As such, below outlines a process (table, stored procedure and SQL Server Job) to capture the TempDB utilization in order to have the information necessary to start the investigation. So let’s jump into code that can capture the needed data:
Table – Historical TempDB Database and Log File Records
CREATE TABLE [dbo].[TempDBFileSize] ( [TFSID] [int] IDENTITY (1, 1) NOT NULL , [FileID] [int] NULL , [File_Logical_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [State_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Type_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Default_File_SizeMB] [int] NULL , [ActualKB] [int] NULL , [ActualMB] [int] NULL , [File_MaxSize] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [File_Growth] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Growth_Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Physical_File_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DateCaptured] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[TempDBFileSize] WITH NOCHECK ADD CONSTRAINT [PK_TempDBFileSize] PRIMARY KEY CLUSTERED ( [TFSID] ) ON [PRIMARY] GO
Stored Procedure – Capture the TempDB Database and Log File Space Used
CREATE PROCEDURE dbo.spTempdbFileSize AS /* ------------------------------------------------------ -- Object Name: dbo.spTempdbFileSize -- Project: SQL Server TempDB Sizing -- Business Process: SQL Server Capacity Planning -- Purpose: Capture the TempDB growth -- Detailed Description: Capture the TempDB size, name, file, etc for the TempDB files -- Database: TempDB -- Dependent Objects: dbo.TempDBFileSize -- Called By: Admin - TempDB Sizing -- Upstream Systems: None -- Downstream Systems: None -- ------------------------------------------------------ -- Rev | CMR | Date Modified | Developer | Change Summary ------------------------------------------------------ -- 001 | N\A | 12.02.2007 | Edgewood | Original code -- 002 | | 03.30.2009 | Penberth | Modified the code to get the actual size -- of the tempdb alongside the the default sizes. -- Added the ActualKB and ActualMB columns and -- renamed the [File_SizeMB] to [Default_File_SizeMB] -- */ SET NOCOUNT ON INSERT INTO dbo.TempDBFileSize (FileID, File_Logical_Name, State_Desc, Type_Desc, [Default_File_SizeMB], ActualKB, ActualMB, File_MaxSize, File_Growth, Growth_Type, Physical_File_Name, DateCaptured) SELECT File_ID, MasterTbl.[Name], MasterTbl.State_Desc, MasterTbl.Type_Desc, (MasterTbl.[Size] * 8)/1024 AS 'File_SizeMB', (TempTbl.[size]*8) AS ActualKB, (TempTbl.[size]*8)/1024 as ActualMB, File_MaxSize = CASE WHEN MasterTbl.[Max_Size] = 0 THEN 'NoGrowth' WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth' WHEN MasterTbl.[Max_Size] = -1 THEN 'UnlimitedGrowth' WHEN MasterTbl.[Max_Size] = 268435456 THEN 'TLogMax' ELSE CAST((MasterTbl.[Max_Size] * 8)/1024 AS varchar(10)) END, File_Growth = CASE WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth' WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 0 THEN CAST((MasterTbl.[Growth]* 8)/1024 AS varchar(10)) WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 1 THEN CAST(MasterTbl.[Growth] AS varchar(10)) ELSE 'Unknown' END, Growth_Type = CASE WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth' WHEN MasterTbl.[is_percent_growth] = 0 THEN 'MegaBytes' WHEN MasterTbl.[is_percent_growth] = 1 THEN 'Percentage' ELSE 'Unknown' END, MasterTbl.[Physical_Name], GETDATE() AS 'DateCaptured' FROM Master.sys.master_files MasterTbl (NOLOCK) LEFT OUTER JOIN TEMPDB.SYS.SYSFILES TempTbl (NOLOCK) ON MasterTbl.[Physical_Name] = TempTbl.[filename] WHERE Database_ID = 2 SET NOCOUNT OFF GO
SQL Server Job – 6 Hour Interval to Collect the TempDB Database and Log File Space Used
BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'Admin - TempDB Sizing') > 0 PRINT N'The job "Admin - TempDB Sizing" already exists so will not be replaced.' ELSE BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Admin - TempDB Sizing', @owner_login_name = N'sa', @description = N'06.16.2007 - ES - Capture the TempDB growth to determine the needed size for the TempDB database', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'EXEC dbo.spTempdbFileSize', @command = N'EXEC dbo.spTempdbFileSize', @database_name = N'TestTest', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Daily Every 6 Hours', @enabled = 1, @freq_type = 4, @active_start_date = 20070524, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 6, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
Sample Query
SELECT * FROM [dbo].[TempDBFileSize]; GO
Next Steps
- When you are faced with determining the sizing for your TempDB database, consider these scripts as a means to capture the needed storage requirements. This information should serve as a guide for determining the storage for the TempDB database. However, you need to consider your local knowledge of your applications, business processes and future growth to determine the final configurations.
- Depending on the application and maintenance related tasks, TempDB can be used heavily so sizing the database properly could become mission critical based on how the database is used.
- It is also worth your time to review the code running during the time periods when TempDB is growing rapidly. If this code can be tuned, perhaps the storage and IO requirements could be minimized.
- For information on how to size just the database transaction logs, please reference – Monitoring transaction log space.
- If you suspect IO problems related to TempDB, consider this tip as a point of reference to configure your disk drives – Hard Drive Configurations for SQL Server.
