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
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.Sign up to join this community
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:
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.