I want to learn more about tuning SQL Server instances (2005/2008), does anyone have any good resources they can point me at?


NB: I'm talking about the hardware/instance configuration side of things rather than tuning SQL queries


Very open ended question!

Microsoft as always is a great resource. Check out technet's SQL Server best Practices. Here are some things that are top of mind for me, that you'd want to explore:


Check out Brent Ozar's collection of information. Brent now works for Quest, which own SQLServerPedia, and there's more practical information there. You might also check the 24 hours of PASS sessions, like Andy Kelly's, which use wait stats to help you pinpoint where the issue might be.


Some of the basics include using domain service accounts to run the instances to allow for replication and certain local policies to apply.

These tips apply to a dedicated server. Consider the impact of these on a server running other services

In Local Security, grant the service accounts that run the instances "Lock Pages in Memory" and "Perform Volume Maintenance."

Lock Pages in Memory - This will allow SQL Server to maintain data in RAM instead of paging it if there is contention with another process.

Perform Volume Maint - This will allow SQL to write on the fly without having to pre-allocate the space by writing zeros. This can increase write speed.

Put your logs and databases on separate physical spindles if possible.

Autogrowth should not be relied on for growing databases, it will cause fragmentation if left over time. If possible, databases should be grown by hand and autogrowth should only be relied on as a fail safe.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.