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.
- The OPTIMIZE FOR AD HOC WORKLOADS should be set to FALSE. For SQL Servers with a high workload and not enough memory (8GB or less) I recommend setting this to TRUE (makes efficient use of memory since SQL only caches queries it sees 2 or more times).
- Setting the COST THRESHOLD FOR PARALLELISM from the default 5 to 25. This makes SQL Server less aggressive with parallelism. Really helps with locking and CPU wait times. A best practice is to set this to a 25-50 range, start with 25 and if you still have locking issues move toward 50.
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 e-auto database. However the small space increase is well worth the performance boost.
Note: Users of e-automate 16.1 no longer need to apply these indexes as they have been integrated into the default install of e-automate and applying them will just produce duplicate indexes. Also, in an upgrade to 16.1 our custom indexes are removed and replaced by the equivalent e-auto indexes automatically. This means that if you are on 8.7 or earlier, the indexes are fine to apply and they will automatically be removed by the time you get to 16.1.