Managing TempDB in SQL Server: TempDB Configuration

Managing TempDB in SQL Server: TempDB Configuration | Coded Style

Managing TempDB in SQL Server: TempDB Configuration | Coded Style — Mon, 05 Jan 2009 03:06:30 GMT

PingBack from http://www.codedstyle.com/managing-tempdb-in-sql-server-tempdb-configuration/

SQL Server Transactions and TEMPDB - DMV a key relationship to optimize the performance

SQL Server Security, Performance & Tuning (SSQA.net) — Fri, 13 Feb 2009 14:28:52 GMT

How many times in a day you observe the TEMPDB in your SQL Server enviornment? How many times in a week

re: Managing TempDB in SQL Server: TempDB Configuration

WhiteXMas — Tue, 01 Dec 2009 17:18:06 GMT

Hi,

Very useful article. Can you please provide some more explanation:

1. "Never AutoGrow": Let assume I have a 4 CPUs Server, using SQL Server 2000. I split tempdb files (MDF + 3 extra Files) in equal file size. The current max tempdb size is 10 GB, I allocate 5 GB to each file so that their combined capacity is 20 GB, double of the current tempdb workload.

If I set autogrowth option to zero to apply the "Never Autogrow" rule. What would happen if ever the SQL Server runs into a situation where tempdb will need more than 20 GB?

What is the autogrowth value do you recommend?

2. Can the templog.LDF be left at the default setting? (1 file, autogrowth 10%, unlimited max size)?

Thanks you very much in advance for any advice.

re: Managing TempDB in SQL Server: TempDB Configuration

Sunil Agarwal — Tue, 01 Dec 2009 18:34:02 GMT

When I said 'never autogrow', I meant don’t let the TempDB grow to its steady state size through auto-grow. You should only use auto-grow as a last resort but not as a strategy. So you must enable auto-grow for emergencies but set your TempDB size appropriately for steady state

For LDF file, similar to what we have said for data files, you will need to know what is the steady state size of the log file and then configure your TempDB to support that. Yes, just like data file, you should set this to autogrow.

re: Managing TempDB in SQL Server: TempDB Configuration

WhiteXMas — Tue, 01 Dec 2009 23:07:31 GMT

Hi,

Thank you very much for your help.

Understood for Q1.

As for Q2. I am sorry I was not clear. I meant is there any need to split the tempdb LDF. (create one extra file for LDF per CPU, the same way than what you recommended for MDF).

Thanks.

re: Managing TempDB in SQL Server: TempDB Configuration

Sunil Agarwal — Wed, 02 Dec 2009 00:34:16 GMT

Creating multiple log files does not help allocation contention issue becauase the log is append only which is dfferent from regular data. If you log file does not fit on avaikabel space on the drive, you may create multiple files to meet the capacity

thanks

re: Managing TempDB in SQL Server: TempDB Configuration

Sunil Agarwal — Wed, 02 Dec 2009 00:34:21 GMT

thanks