Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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:

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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