Easy Tuning Options for SQL Server OLTP

Even though I have specialised in business intelligence applications, I still help customers with OLTP performance issues. Here are a couple of very simple option that can make quite a difference without changing any code.

1. Turn on asynchronous statistics collection. This can be a great help where there are locking (and/or deadlocking) because you want to reduce the duration of transactions. The probability of deadlock tends to be exponentially proportional to the transaction duration. Asynchronous statistics collection will enable your transactions to continue to execute without having to wait for the statistics gathering. By the way, if you really want to manage statistics in a critically high tp system, you might want to disable autostats and run them on a schedule off peak. This option is set for the server on the advanced properties tab.


2. Disable query decomposition. In an OLTP system, you really don't want one query to break up into many parallel tasks consuming more resources. The property is "Max Degree of Parallelism" and is set by database.

Of course there are a million things that you can do differently in your application to make it perform better, but I would need to write a book to cover the essentials. Also, hardware is always an option to improve performance, but it will only improve performance if it is hardware that is a critical resource.

For real time BI and data mining demonstrations see http://RichardLees.com.au/Sites/Demonstrations