September 12, 2012 at 1:29 pm
JamesMorrison (9/12/2012)
lianvh (9/11/2012)
I am frustrated, because , a consultancy firm used this tool and now to keep the peace we have to implement this setting.There is nothing wrong with doing this and it is not a negative. Just do it.
There are battles worth fighting over, but adding multiple TempDb files is not one of them. This is a harmless change and will likely result in better performance for servers with a heavy load. For servers with a light load it won't matter at all. It certainly won't hurt anything.
This change is not a silver bullet for anything and any resulting performance improvement will likely be minor.
It's not entirely true that there is nothing wrong with this suggestion. Yes the recommendation is for multiple tempdb files based on # of CPUs. However even the older recommendations do not suggest an exact 1:1 unless you have a small number of CPUs, say 8 or less. After that its more like 1:4 or even 1:8. Everything I have read (and there are a number of good links in earlier replies) say that you should add a smaller rather than greater number of files if you do not have the skill to actually test if this is increase/decreasing your performance. Also just "adding" files isn't all of the suggestion. All of the files have to be the same size. Data files only btw, not log. They all have to have the same growth. And even then it's not going to help if you have less spindles than you do files.
In general if someone (even a good consultant, again see earlier replies) gives you a recommendation you should still think it through, read about the pros and cons, then make your own informed decision. Not to mention test, test and then test before just throwing it into production.
My own 2 cents anyway
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 12, 2012 at 1:53 pm
The relevant point is that the adding of multiple files to Tempdb won't hurt a SQL Server with a light load and it will likely help on a SQL Server with a heavy load. So it is not something that really anyone should resist as a concept.
It is certainly not worth a major fight against management wanting to follow a Microsoft consultant's performance recommendations.
Yeah, we can all argue about different ratios of additional data files to add per CPU. That is certainly subjective and difficult to determine with precision.
But I think 'lianvh' is fighting the wrong battle by being against this. Overall it is net positive to add the additional files.
September 12, 2012 at 3:53 pm
I don't think you can guarantee there won't be a harmful effect -
http://msdn.microsoft.com/en-us/library/ms175527(SQL.105).aspx
http://www.mssqltips.com/sqlservertip/1980/sql-server-tempdb-one-or-multiple-data-files/
and paul randal says only add multiple files *IF* you have latch contention.
not sure this was a microsoft consultant recommending this, (are they infallible?) just some company using BPA or something similar, which would recommend using windows authentication but we all know thats not always possible. And best practice recommendations change.
It may not be a battle worth fighting to the death but its fairly easy to determine if it is actually required and the blanket 1 file per core should be approached with some caution.
thats my 2 cents worth
---------------------------------------------------------------------
September 12, 2012 at 5:46 pm
Now I remember why I avoid this forum most of the time.
If you don't feel comfortable doing it, dont do it.
For those of us with experience, it is a non-issue. Multiple tempdb files are a minor performance improvement most of the time. There are likely that some SQL processes in any application which realize a minor positive result.
The change probably won't result any taking anything from timing out to all of a sudden running in 1 second. It is not a silver bullet for lousy performance in a database.
This is a silly topic to even debate really. With the current SANs on the market, it is a rounding error in terms of helping performance overall on most systems.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply