SQL Server Configuration Tuning: Improve Performance And Prevent Deadlocks

Suggested URL: sql-server-configuration-tuning-improve-performance-prevent-deadlocks

Optimizing your database is an ongoing task, and one which can be approached in a number of different ways.

If your aim is to improve performance and to stop deadlocks from occurring, then there are a few tuning tactics to put into practice.

Let’s take a look at the options you have for ensuring your SQL Server deployment runs like clockwork, and the benefits each type of tuning will deliver.

How to detect and deal with deadlocks

From time to time you will need to fix a deadlock in SQL Server, since this is an event that can hamper performance if it occurs too regularly.

Deadlocks are similar to blocking, but take things to the next level in terms of the disruptiveness of the outcome. In short, when two processes with exclusive locks on server resources clash, one will need to be terminated.

This happens automatically, and in isolation it is not a major setback. However, if deadlocking happens frequently enough, it will need your attention.

The simplest way to detect deadlocks is to make use of a third party monitoring tool which is made to track SQL Server performance parameters and identify snafus like this.

Dealing with deadlocks will depend on the nature of the deadlock itself. There are three main types; order of operations deadlocks, lookup deadlocks, and parallelism deadlocks.

Extensive documentation on coping with each is available online, and other DBAs will doubtlessly be happy to help you out in this scenario.

Preventing deadlocks in the first place is of course desirable first and foremost. Following best practices for query construction and not over-using exclusive locks should stop snarl-ups from rearing their ugly heads, for example.

How to tune the Buffer Pool Memory

Memory management is an important part of database optimization, since if your server is monopolizing too much of this vital resource, performance will suffer.

The chief conundrum in this context involves memory not being released by processes after they have completed, meaning that the amount available for other operations will be limited even if the instance is not under heavy load.

Getting to grips with the Buffer Pool is necessary in this case. This refers to the section of memory allocated to storing pages from the database when they are needed for a rear or write operation.

When pages join the Buffer Pool, they can persist there as an efficiency measure, since this means that other processes do not need to delve back into the main storage repository each and every time a particular table entry is requested.

The temptation to flush all buffered pages from this pool is tempting if memory usage spikes and remains high, but this is not always a good thing, as suggested by the performance implications of always having to pull pages from slower disk-based storage when queries come calling.

SQL Server has its own integrated buffer-flushing tool in the form of checkpoint, which deals with ‘dirty’ pages, meaning those that have been changed by a process and thus differ from the original on-disk copy. Likewise, ‘clean’ pages are also purged automatically from time to time, so for everyday use it should work well.

But what if pages aren’t being flushed? Well, you can use the following query to pull up an overview of the dirty and clean pages in the Buffer Pool:

SELECT (CASE

        WHEN ( [database_id] = 32767 ) THEN 'Resource Database'

        ELSE Db_name (database_id)

      END )  AS 'Database Name',

    Sum(CASE

             WHEN ( [is_modified] = 1 ) THEN 0

             ELSE 1

        END) AS 'Clean Page Count',

            Sum(CASE

             WHEN ( [is_modified] = 1 ) THEN 1

             ELSE 0

        END) AS 'Dirty Page Count'

FROM   sys.dm_os_buffer_descriptors

GROUP  BY database_id

ORDER  BY DB_NAME(database_id);

With this, you can then see which components of the database are hogging the Buffer Pool, and take action to deal with apparent issues. You can also turn to PerfMon and the Buffer Manager object within it to check metrics on the fly.

How to tune the SQL Server IO Subsystem For Optimal Performance

Storage bottlenecks are another performance-compromising problem in an SQL Server context, so if you find that IO levels are excessive during specific periods of use, some tinkering is necessary.

Reviewing wait statistics will show you which threads are being held up and will let you scrutinize IO performance, among other things. You can get an overview with the help of this query:

SELECT *

FROM sys.dm_os_wait_stats dows

ORDER BY dows.wait_time_ms DESC;

IO troubleshooting will be needed if you see wait types labeled PAGEIOLATCH, as this signifies that a delay occurred.

Looking into latency issues is also an option, and this is a handy query if this is on your to-do list:

SELECT *

FROM sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks2014'), NULL) divfs

ORDER BY divfs.io_stall DESC;

In terms of actually fixing problems with IO, optimization may come down to a few things. With the aforementioned PAGEIOLATCH, memory tweaking may be needed, especially if it seems like your buffer pool is being flushed too often.

Index optimization could also be key to this, as fragmentation will lead to delays that can be avoided through regular index maintenance. And if you want to store data safely, IO matters must be a priority.

How To Tune Resource Governor Settings For SQL

Resource Governor gives you greater levels of control over how your database’s hardware resources are allocated.

For example, you might allow an app access to a specific proportion of the memory, IO capacity and CPU cores in order to achieve the desired performance without hurting performance elsewhere.

Creating workload pools according to the priority you would like to give them over server hardware makes sense, and is generally the best way to achieve optimal performance.

However, you should aim to monitor Resource Governor and ensure that certain pools are not pegged at full capacity, since it is possible for them to exceed their maximum allocated resource usage, depending on the total load that a database is dealing with.

Final thoughts

It may take a while to get up to speed with SQL Server tuning duties, but it is worth it in the long run. All sorts of performance problems can be fixed if you follow tried and tested methods, and use monitoring tools to help out.