Here are some basic tips for keeping your SQL Server running efficiently.
On many occasions we help clients with SQL performance issues, and since it’s something simple to apply we have created this post to share some valuable information.
The SQL tuning we do usually involves changing some basic server settings in SQL Server, which follow best practices. Please see figure 1 below to see a screenshot. The properties are opened by right clicking on the server instance name and going to Properties.
- Setting the min/max memory (for example, a server with 16GB , a good setting is 8GB min and 12-13GB max, that leaves 3-4GB for the OS). Setting a min prevents other software or VMWare from stealing good SQL Server cache, and setting a Max prevents bumping heads with the OS.
- Setting the OPTIMIZE FOR AD HOC WORKLOADS = TRUE (makes efficient use of memory since SQL only caches queries it sees 2 or more times). For very large systems with tons of memory it’s best to keep it at FALSE.
- Setting the COST THRESHOLD FOR PARALLELISM from the default 5 to 50. This makes SQL Server less aggressive with parallelism. Really helps with locking and CPU wait times.
Figure 1- Server Properties
The other piece of the tuning involves applying our custom index script, which currently contains 305 indexes. This really is the performance booster.
And lastly we review to make sure there’s a maintenance plan in place that rebuilds indexes and stats every night or at least twice a week. This is simple since SQL Server has the wizard for the maintenance plans.
Here is a blog posting by a top SQL Server consulting company that mentions the settings I recommend above:
There are 3 scripts for our indexes:
- ceojuice-custom-indexes-for-eauto-create.sql – this will create the 300+ indexes in your eAuto database. Just point to your database and run the script. It can be run during business hours but to avoid any potential locking it’s best to run it after hours. (see below for link)
- ceojuice-custom-indexes-for-eauto-list.sql – lists the custom ceojuice indexes. To validate that all 300+ indexes were applied to the database. (see below for link)
- ceojuice-custom-indexes-for-eauto-drop.sql – drops the 300+ ceojuice custom indexes. (see below for link)
Note: applying these indexes adds about 10% size to your eAuto database. However the small space increase is well worth the performance boost.
ceojuice-custom-indexes-for-eauto-create
