Here are some basic tips for keeping your SQL Server running efficiently. For e-automate database "locks," these need to be traced down. We suggest installing a trial of Redgate.
SQL tuning usually involves changing some basic server settings in SQL Server Management Studio which follow best practices. Please see the screenshot below. 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.
It is important to make sure there is a maintenance plan in place that rebuilds indexes and statistics at least once 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 recommended above: