July 24, 2012 at 7:52 am
Good Day.
We are running SQL Server 2008R2 Enterprise edition 64 Bit and I would like to know if there is a correct size for Tempdb . What should one use as a guideline ? Also how many datafiles shuld be given to Tempdb ? I configured one of my servers with one data file per processor , but I found no evidence that the additional were ever used . Any ideas ?
July 24, 2012 at 7:59 am
No correct size other than the size that your applications require.
Files per core, lots of contrary information. I like to start at either 1/4 or 1/2 the number of cores that SQL uses and increase if necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2012 at 8:04 am
Size depends on envrionment, my TempDB is 45GB, 1MDF 3NDF's at 10GB and the LDF at 5GB, never go above 10% utilisation but there have been times which the TempDB did use around 95% of the space. While it may be overkill to keep it at that size all the time, it saves a lot of time when SQL needs to size the file.
As for the 1 data file per CPU, thats a common misconception. Usually between 1/4 or 1/2 the number of files per CPU's. So 12 CPU's between 3 and 6 TempDB files.
The link below details of a check list, and also has a link to Paul Randal's blog on the subject as well.
http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/
This code will tell you how much of a file in a database is used.
SELECT
DatabaseName = DB_NAME(),
FILEID,
[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(size / 128.000, 2)),
[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(NAME, 'SpaceUsed') / 128.000, 2)),
[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((size - fileproperty(NAME,'SpaceUsed')) / 128.000, 2)),
NAME,
FILENAME
FROM
dbo.sysfiles
Or you could click the link Gail has provided to the same link in Brent's blog post to Paul's blog.
July 24, 2012 at 8:38 am
and another important consideration would be if you plan to (or already) use the SORT_IN_TEMPDB option when rebuilding indexes. This is because using this option could result in significant (if not exponential) growth.
Please take a look at the following article on MSDN for more information:
July 24, 2012 at 3:39 pm
In order to increase the tempdb data files number, at first you should make sure there is a need for that.You should check the tempdb data file in peek load times to see if there is any process waiting for using PFS,GAM,SGAM pages on the tempdb data file.You can use sys.dm_os_waiting_tasks DMW and look for any record with the wait_description column in these valus:
PFS pages: ('2:1:1','2:1:8089',... for PFS pages it repeats evey 8088 pages )
GAM pages ('2:1:2','2:1:511234',... for GAM it repeats every 511232 pages for every 4 GB of tempdb data file)
SGAM pages ('2:1:3','2:1:511235',... for GAM also it repeats every 511232 pages for every 4 GB of tempdb data file)
in case of need for that 1/4 to 1/2 of CPU cores seems ok.
Pooyan
July 24, 2012 at 3:54 pm
pooyan_pdm (7/24/2012)
PFS pages: ('2:1:1','2:1:8089',... for PFS pages it repeats evey 8088 pages )GAM pages ('2:1:2','2:1:511234',... for GAM it repeats every 511232 pages for every 4 GB of tempdb data file)
SGAM pages ('2:1:3','2:1:511235',... for GAM also it repeats every 511232 pages for every 4 GB of tempdb data file)
Your page IDs are off.
The second PFS is page 8088 and then it repeats every 8088 pages
The second GAM is page 511232 and then it repeats every 511232 pages
The second SGAM is page 511233 and then it repeats every 511232 pages
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2012 at 4:13 pm
I checked that with DBCC PAGE you were right thanks for your correction
Pooyan
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply