Alexander The Great

March 3, 2008

SQL Server Deployment Checklist

Filed under: Science,Software — alexanderthegreatest @ 4:37 pm
Tags: , , , ,

John Hicks (of MSDN Blogs) has posted a very interesting article on SQL Server deployments that raised my eyebrows. It begins with the text “Every once in a while, a process gets too complex for humans to manage consistently.

The article goes into great detail about the steps that should be taken to performance optimize a new node in a SQL Server cluster, or to prepare a new server that’s being migrated to. This is extremely high scale stuff here, probably boring to mom n pop shops that need to store and instantly access less than perhaps 20 million records. (PHP users will find this article endlessly tiresome, because PHP users turn their nose up at any system meant to serve 25 or more concurrent users. Such is the demand of “enterprise level” evil corporate programming, a need of monsterish slave masters to rob the noble developers of the world of their identities and very souls. I’m not sure where this prejudice comes from, but it’s unescapable at any number of web forums.) The paper describes physical tuning only, completely ignoring the logical domain.

Some highlights

  1. Set your disc data allocation units (pages) to 64 KB to match the size of an extent.
  2. Set network packet size to 8 KB to take better advantage of large and few TCP packets. This is known as jumbo framing.
  3. Create a TEMPDB filegroup for each CPU core on the server.
  4. Set MAXDOP to correspond to your disc subsystem hardware. (I don’t understand why – maybe someone can explain this to me in the comment stream. Shouldn’t these settings be independant from one another? Disc bound transactions should allow the threads to block and be preempted by CPU bound transactions, no?)
  5. Use TraceFlags to control lock escalation – DANGER, WILL ROBERTSON!
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: