September 4, 2008 at 10:26 am
Hi All,
Have a SQL 2005 server that I am going to move the tempdb from default location to a different set of disks.
I am also planning to add datafiles to follow the best practice of 1 datafile per physical processor.
Question I have is how large can the files be? Can they be too large? I have a ~64GB SAN drive, and I am planning on following the advice of SSC Eights! in the TEMPDB CPU thread of creating 1 file per processor with autogrowth off (filegrowth = 0Mb). Since autogrowth is off, I need to ensure the files are large enough that the SQL doesn't run out of workspace, so I am thinking of 8 6GB datafiles plus the log file.
Is this too much? I've read that you can adversely impact performance by too many datafiles, by giving SQL too much to manage.
Thanks for your advice!
Ed
September 4, 2008 at 11:24 am
Depends. Does you app need/use 48 GB of TempDB? If yes, then it's not too much.
48GB isn't large for a DB.
You can start out with just 4 files and then add more if it appears that the system's encountering contention on tempDB. If you're seeing page latch contention on low-numbered pages in tempDB (eg 2:1:3) then add more files, if you're seeing IO bottlenecks then you can move some of the files onto separate physical drives.
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
September 4, 2008 at 11:58 am
Hi Gail,
This probably isn't much of an answer, but the DB hosted on the server supports an online class server. It's the largest and most active database on our campus; loads of data is read from & written to the database.
I do know that the app uses a lot of complex queries, lots of joins, and there are number of indexing maintenance jobs that run hourly, daily, and weekly.
Thanks for your help!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply