Setting up TempDB
-
I'm building a new server (my first time doing complete setup) and have a question about creating the tempdb files. I'm not clear on how to handle the ldf file.
Windows Server 2003 SP2
SQL Server 2005 SP3
64 bit
2 quad-cores for 8 CPUs
The current server has a tempdb of about 4G right now (been checking it for a couple weeks). We have lots of small-to-medium databases, mostly for purchased applications.
According to everything I've read, I should set up TempDB with 8 equal-sized files (one for each CPU). TempDB's got it's own spindle with lots of room.
So if I wanted to have a base of 8G tempdb space, would that be one mdf file, one ldf file, and 6 ndf files of 1G each? Or is it one mdf file and 7 ndf files for the 8GB, and in that case, how do you handle the log?
Thanks much for any advice.
-
7 data and one log. that should work here.
:crazy: :alien:
Umar Iqbal
-
The recommendation applies to the data files, not the log.
So 1 mdf, 7 ndf's and 1 ldf in your case.
-
1mdf, 7ndfs, 1 log. Size the mdf and 6ndf to not grow, and the 8th ndf to autogrow. However, make sure though that they are sized large enough so there is no need to grow. The round-robin gam/sgam allocation gets hacked up if the file sizes are not the same, so this is very important when sizing and maintaining.
-
ab5sr (3/19/2009)
and the 8th ndf to autogrowThis is the first I've seen of having one of the files set to autogrow. Where'd you come across that recommendation?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter -
Hey Jason:
Actually, that's a "best practice" that we used when I was at www.sqlrx.com, and it seemed to work well. It does make sense - if tempdb, in the unlikely event, is not sized properly and becomes full, you're hosed. The last file is an out, so to speak. SQLRx by the way does the same type of SQL perf tuning stuff that companies like Edgewood, Scalability, and the Microsoft RAP folks do, and they do it well. Most of their services are performed on the big guys (ES 7000-class servers with thousands of users, up in the hundreds of GB RAM, et. al.) so they have some experience in the with some big shops.
-
Whups, I thought I'd already posted this...
So would the ldf file be the same size as the eight data files?
My current plan is to set the tempdb to autogrow until I've been able to track it for a while (since I haven't been doing that). Once I'm confident that I've got the size right and the monitoring setup working, then I can turn it off.
I'm still working on the how and what for monitoring, though.
Thanks for all the advice!
-
Here what i used to increase the tempdb...and was someone else post...
Please try on a test server to make sure you understand this first.
To improve query performance when you've found tempdb to be a performance bottleneck, you can create multiple data files of equal size. It is recommended that you add one file per CPU (per core, not per socket). The below script will do this for you. It is written as a stored procedure as I like to keep all of my scripts in an admin-type database. You will probably only run this once per instance though.
To determine how big each file should be, look at the size of your tempdb's data file which by default is named tempdb.mdf. You should check the file size after your system has been running for a while after a reboot or a restart of the SQL Server service. For instance, we typically reboot our database servers once a month due to the monthly Microsoft security patches. In my environment, I would check the file size about 30 days after the reboot so that I know it is at its typical maximum value.
As an example, if the file size is 8 GB and you've got 8 CPUs, then I would suggest passing a minimum of 1024 to the stored procedure as the inputs accept megabytes.
You should run the stored procedure after a reboot or a restart of the SQL Server service so that tempdb is at its smallest, which should avoid any errors.
If you've already set the initial size of tempdb to be greater than the value passed to the stored procedure, it will error as you can not reduce the setting with ALTER DATABASE. You must first set the initial size to a lower value. I have only seen one way to do this, which can be found in this thread. Check out rklime's post on 10-23-2007 at 10:05 AM.
This tempdb architecture is described in SQL Server Books Online. Here is the online reference for the article.
Although this TechNet article is long, it is a must read for database administrators as it goes into great detail about tempdb.
Here is the stored procedure:
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_AddTempdbFiles
--
-- AUTHOR : Tara Kizer
--
-- DATE : May 13, 2008
--
-- INPUTS : @size - size of the tempdb files in megabytes
-- @growth - growth increment of the tempdb files in megabytes
--
-- OUTPUTS : None
--
-- RETURN CODES : None
--
-- DESCRIPTION : Adds one tempdb file per CPU
--
-- EXAMPLE : EXEC isp_AddTempdbFiles @size = 1024, @growth = 200
----------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[isp_AddTempdbFiles]
(@size int, @growth int)
AS
SET NOCOUNT ON
DECLARE
@regOutput varchar(20), @numProcs int, @sql nvarchar(4000), @tempdbDataFileCount tinyint, @tempdbDataFileSize int,
@tempdbFirstLogicalName sysname, @tempdbDataFileId tinyint, @i tinyint, @j-2 tinyint,
@tempdbMissingDataFileCount tinyint, @tempdbLogicalName sysname, @tempdbFirstPhysicalPath nvarchar(260)
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
@value_name = 'NUMBER_OF_PROCESSORS',
@value = @regOutput OUTPUT
SET @numProcs = @regOutput
SELECT @i = 2, @j-2 = 2, @tempdbDataFileCount = COUNT(*), @tempdbDataFileId = MIN(file_id)
FROM tempdb.sys.database_files
WHERE type = 0
SET @tempdbMissingDataFileCount = @numProcs - @tempdbDataFileCount
IF @tempdbDataFileCount <> @numProcs
BEGIN
SELECT
@tempdbFirstLogicalName = name,
@tempdbFirstPhysicalPath =
SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name )) + 1)
FROM tempdb.sys.database_files
WHERE file_id = @tempdbDataFileId
SET @tempdbDataFileId = 0
-- file exists, expand it, if necessary. to make sure they will all be the same
WHILE @tempdbDataFileCount <> 0
BEGIN
SELECT @tempdbDataFileId = MIN(file_id)
FROM tempdb.sys.database_files
WHERE type = 0 AND file_id > @tempdbDataFileId
SELECT @tempdbLogicalName = name, @tempdbDataFileSize = size
FROM tempdb.sys.database_files
WHERE file_id = @tempdbDataFileId
IF @size*1024/8 > @tempdbDataFileSize
BEGIN
SET @sql = 'ALTER DATABASE tempdb MODIFY FILE (NAME = N''' + @tempdbLogicalName + ''', SIZE = '
SET @sql = @sql + CONVERT(varchar(10), @size) + 'MB, FILEGROWTH = ' + CONVERT(varchar(10), @growth) + 'MB)'
--PRINT @sql
EXEC (@sql)
END
SET @tempdbDataFileCount = @tempdbDataFileCount - 1
END
-- missing files
WHILE @tempdbMissingDataFileCount <> 0
BEGIN
WHILE 1 = 1
BEGIN
IF EXISTS
(
SELECT *
FROM tempdb.sys.database_files
WHERE name = @tempdbFirstLogicalName + CONVERT(varchar(3), @i)
)
SET @i = @i + 1
ELSE
BREAK
END
WHILE 1 = 1
BEGIN
IF EXISTS
(
SELECT *
FROM tempdb.sys.database_files
WHERE physical_name = @tempdbFirstPhysicalPath + @tempdbFirstLogicalName + CONVERT(varchar(3), @j-2) + '.ndf'
)
ELSE
BREAK
END
SET @sql = 'ALTER DATABASE tempdb ADD FILE (NAME = N''' + @tempdbFirstLogicalName + CONVERT(varchar(3), @i)
SET @sql = @sql + ''', FILENAME = N''' + @tempdbFirstPhysicalPath + @tempdbFirstLogicalName + CONVERT(varchar(3), @j-2)
SET @sql = @sql + '.ndf' + ''', SIZE = ' + CONVERT(varchar(10), @size) + 'MB, FILEGROWTH = '
SET @sql = @sql + CONVERT(varchar(10), @growth) + 'MB)'
--PRINT @sql
EXEC (@sql)
SET @tempdbMissingDataFileCount = @tempdbMissingDataFileCount - 1
END
END
-
No I don't think anywhere near that full size. Try 15% of total and you should be ok. If it grows, extend out a bit. Leave it on auto grow. I've never had issues sizing tempdb ldfs. The exercise is intended for increased throughput via leveraging objects already created in tempdb data files, rather than reallocation and recreation of those objects.
-
The suggestion to have 8 equal size files with 7 set to fixed size and 1 allowed to autogrow is good but does have its problems.
The biggest problem is if an object assigned to the file that can autogrow wants more space than is in the file, then that file will autogrow, even if there is lots of space in the other files. After the file has grown and the offending object has been discarded, then this file will have more free space than the other files and will get more objects assigned to it than the other files, causing it to grow further.
A better approach is to have 9 files. 8 of these are equal size and set to zero growth. File 9 is the 'spill file'. It has an initial size of 1MB but has a large growth increment. The spill file will not get any objects assigned to it until all the other files have 1MB or less free space. Then the spill file will get an object assigned to it and it will grow.
The technique of using a spill file gives all the advantages of round-robin allocation to the big tempdb files, but gives you a safety net if you make tempdb too small for your workload.
The same principle of multiple large fixed size files plus a 1MB spill file with autogrow can be used for other large databases.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Somehow my post got duplicated.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I put up a blog post[/url] a while back on tempdb that had some of the info from Tara's script.
It may or may not be helpful to someone.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter -
All very good points, but you have missed the important question. Have you found your TempDB to be a performance bottleneck?
The current size of TempDB is only 4gig. that's pretty small.
So, do you really want to go through all this administrative overhead to spread your TempDB (btw, the recommendation is for each TempDB file to be on it's own disk) for a 4gig TempDB Database?
The "Best Practices" and TempDB BOL recommendations are not for EVERY situation.
If it's working fine now, leave it alone.
Tim White
-
If there is not a performance bottlenec in tempdb - i.e. it is always experiencing allocation/deallocation waits, them multiple tempdb data files are not needed. If you do have this issue then you also need to research trace flag 1118 as well.
My experience has shown that very, very few SQL Servers actually need multiple tempdb data files.
Just try and size tempdb to avoid 'default' autogrowth in the data and log portions and you should be fine.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thanks very much for all the advice, guys. I've got the tempdb resized appropriately on the new server and am working on an automated tracking setup to avoid any problems.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply