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.
- Set your disc data allocation units (pages) to 64 KB to match the size of an extent.
- Set network packet size to 8 KB to take better advantage of large and few TCP packets. This is known as jumbo framing.
- Create a TEMPDB filegroup for each CPU core on the server.
- 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?)
- Use TraceFlags to control lock escalation – DANGER, WILL ROBERTSON!