September 17, 2010 at 11:53 am
I'm looking for a simple matrix detailing when you should tweak the various sp_configure options and when you should leave them alone. I understand SQL Server runs ok outa the box but I'm trying to document a list of recommended exceptions. Anyone got such a list or some URLs?
September 18, 2010 at 12:46 am
Hi
nzrdb6
This link may use full for you, Please try it
http://www.sql-server-performance.com/articles/per/performance_audit_part4_p1.aspx
Ali
MCTS SQL Server2k8
September 18, 2010 at 6:57 pm
Ali Tailor (9/18/2010)
nzrdb6
This link may use full for you, Please try it
http://www.sql-server-performance.com/articles/per/performance_audit_part4_p1.aspx%5B/quote%5D
Nice link, Ali. Thanks.
As for me... the only things I may change are...
AWE (depending on machine),
Cost Threshold for Parallelism (I'll usually set this to 20 to prevent unnecessary parallelism),
Fill Factor gets set to 90 (in most cases) and I'll force 100 on indexes only on static or mostly static tables,
Max Degree of Parallelism gets set to something other than 0 (usually 2, sometimes 4 depending on the machine),
Max Server Memory (MB) gets changed to 1 or 2 GB less than total memory (sometimes more than 4GB less depending on the machine and if other apps are present),
Priority Boost is usually turned on (set to 1) for me just out of old habit and I should break that habit. Read Brad's writeup to make sure it's going to be right for your machine. Be advised as in WARNING though. [font="Arial Black"]If your machine screws up and you need to get into it while SQL Server is running, this setting will make it take ages to "get in" to fix things.[/font]
I leave everything else pretty much alone. Of course, all of these recommendations are subject to the "It Depends" factor. 😉
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply