Configurations eating up sql server performance

Question: I’m setting up a best practises document for our SQL Server builds and would like to focus on some configurations which fit in with sql server tuning best practises,

What are some of the SQL Server configurations you emphasise when setting up a server?

Answer: Firstly – I like this approach of focusing on the configurations at the SQL Server Install phase. This is an important aspect of SQL Server performance tuning.

Research should be completed looking at a combination of industry standards, capacity planning , and application specific requirements.

A large chunk of problems can be stopped by setting up a server , benchmarking and monitoring.

Most of the best practises I apply are on the post –SQL Server Install Checklist . Most of these configurations are scripted out either as part of the build or as part of a request which may go another team.

One part of the equation is the SQL Server set up – and often SQL Server can be set up with performance in mind- but an unexpected change in workload may require a review of the configurations.

1. Set autoshrink to off  for all databases
2. Set Max Server Memory based on installed RAM and installation type . One of the keys is to assess the requirements of non – SQL Server applications on the OS such as monitoring software, virus scanners.
3. Wait types which may indicate you may have configuration problems

THREADPOOL - How to reduce THREADPOOL wait type (SQL Server DBA)

RESOURCE SEMAPHORE SQL WAIT TYPE – RESOURCE SEMAPHORE and how to reduce it ...

      4.Some other useful tools for deciding on which configurations to review. There is a load of clues and details using these tools.

Execution Plan Cache

Buffer Pool Cache

Query Workspace Memory (back to our old favourite: Memory Grant)

     5. SQL Server autogrowth best practise . Read more on SQL DATA FILE AUTOGROW performance troubleshooting and SQL LOG FILE AUTOGROW performance troubleshooting