While researching some performance issues at a Dynamics GP customer, I noticed that the SQL Server tempdb had been setup with four separate files. I hadn't seen that before, so I looked it up, and apparently it's a thing. I would have thought I would have come across this info before, but I'm pretty sure it's been a very, very long time since I looked into tempdb optimization.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
- Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for anyaffinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
- Make each data file the same size; this allows for optimal proportional-fill performance.
- Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
- Put the tempdb database on disks that differ from those that are used by user databases.
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
His points get pretty technical, so I don't follow all of the details. But it seems that multiple tempdb files are generally a good idea, so you may consider monitoring performance before going nuts and creating 8 tempdb files.
And yes, the fact that I'm reading this stuff, with interest, after 8pm on a Thursday evening does concern me as well.
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
Hi Steve,
ReplyDeleteGreat summary of the TempDB setup in SQL.. I think equally important is to determine how big your TempDB must be..
Microsoft has some recommendation on how to calculate your needs :
https://technet.microsoft.com/en-us/library/ms345368%28v=sql.105%29.aspx
I've the feeling that many times this part of the job gets overseen by GP consultant that implement a new system and are not familiar with Capacity Planning on SQL..
There are some other considerations with tempDB. I always recommend managing auto growth. You don't want 8 20gb files growing by 10% each time you have a spill. The second thing you can do is enlarge tempDB enough that you don't have to let it grow automatically. You just set the size ahead of time. I'm working on a post about SQL server configuration and GP since the MS guide is rather dated.
ReplyDeleteThe above is how we have things configured, leads to 8 tempDB files.
ReplyDeleteIf storage is SAN backed then it is generally best to disable the caching on the tempDB storage, but there are few absolutes in SQL server configuration!
Some discussion on this here: https://connect.nimblestorage.com/thread/1756
Beat, the only thing I will say is that MS isn't as helpful when it comes to this error (yet):
ReplyDeleteCould not allocate space for object 'dbo.SORT temporary run storage: 172017949802496' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
From Management Reporter.
Our tempdb is now 630GB. (it's not on autogrow) The total size of our GP databases combined is under 150gb. MR is doing some really wacky stuff into tempdb when it wants to load the data mart. Yes we have AA. And yes MS knows about this.