Home / server / Questions / 87990
Accepted
David Hayes
Asked: 2009-11-25 14:55:52 +0800 CST

SQL Server Tuning Resources

  • 772

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

Dave

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

sql-server

3 Answers

  1. Best Answer

    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:

    • 3

  2. 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.

    • 2

  3. 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.

    • 1