September 11, 2012 at 12:57 am
Good Day,
w had an auditing team running Microsoft Assessment on our database servers . This product recommends that the principle of creating ONE datafile per cpu be
deployed on all the SQL Servers regardless of the load on the server . How can Microsoft release a product like this ?
September 11, 2012 at 1:21 am
The reality is, if you don't have the load then you dont need to it's more a best practice. This has been blogged many times by many different people.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 11, 2012 at 2:15 am
lianvh (9/11/2012)
Good Day,w had an auditing team running Microsoft Assessment on our database servers . This product recommends that the principle of creating ONE datafile per cpu be
deployed on all the SQL Servers regardless of the load on the server . How can Microsoft release a product like this ?
such advice had appeared from top consultants who work on very big and heavy farms. They suggested to improve the number of files per cpu only after analysing wait statistics and contention GAM and SGAM pages(can't recall exact terminology). We should be careful with what we choose. Read some whitepapers from SQLCAT team, PSSQL team, blogs from Paul Randal,Brent Ozar and analyse what they suggest before we make this implementation in our production servers.
Thanks
Chandan
September 11, 2012 at 2:28 am
The recommendation is per cpu core and it is also a microsoft recommendation.
http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
I'm not entirely sure what you mean by "How can Microsoft release a product like this"... Do you find the recommendation bad in some way? Do you have an objection to creating multiple data files?
September 11, 2012 at 2:45 am
<sigh> were the auditors dbas?
thats a rather blanket recommendation, and there are lots of other recommendations than one file per core.
The recommendation should be that if you are not using multiple files for tempdb you investigate whether your system would benefit from doing so, as there is a chance it could do. Are you having any performance problems? A system that is not a heavy user of tempdb is unlikely to require it.
---------------------------------------------------------------------
September 11, 2012 at 2:49 am
I am frustrated, because , a consultancy firm used this tool and now to keep the peace we have to implement this setting.
September 11, 2012 at 3:11 am
read the blog from paul randal anthony posted and check out the scripts it links to which determine if you have tempdb contention. If you have no latch contention on tempdb allocation pages that is evidence you can present that multiple files will not be required.
If you are forced to do this it may not do any harm but point out it could be unnecessary and the server should be monitored after applying the changes.
---------------------------------------------------------------------
September 11, 2012 at 4:28 am
The later recommendation is actually files equal to 1\4 - 1\2 the number of CPU cores
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 12, 2012 at 4:26 am
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.
May I recommend that you find a consultancy that is a) capable of independent thinking and which is up to date on current SQL Server "best practices" and more importantly b) recommends things that are necessary for YOUR ENVIRONMENT to function optimally/correctly and which shows you WHY they make such recommendations using metrics from YOUR system(s)?!?
I happen to know of just such a consultancy, btw. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 12, 2012 at 4:57 am
TheSQLGuru (9/12/2012)
I happen to know of just such a consultancy, btw. 😎
😉 thanks for recommending me Kevin 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 12, 2012 at 5:12 am
I'll moonlight and undercut these guys by 10% 🙂
edit: cash only
---------------------------------------------------------------------
September 12, 2012 at 5:21 am
george sibbald (9/12/2012)
I'll moonlight and undercut these guys by 10% 🙂edit: cash only
:hehe:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 12, 2012 at 7:56 am
Scammers and Charlatans the lot of you!! 😛
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 12, 2012 at 12:54 pm
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.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply