Server Config Best Practice
-
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?
-
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 -
Ali Tailor (9/18/2010)
Hinzrdb6
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply