SQL Server Configuration Performance Checklist
Cost Threshold for Parallelism
Using parallelism to execute a SQL Server query has its costs. This is because it takes a little additional overhead to run a query in parallel than to run it serially. But if the benefits of running a query using parallelism is higher than the costs, then using parallelism is a good thing.
As a rule of thumb, if a query can run serially very fast, there is no point in even considering parallelism for the query, as the extra time required to evaluate it for possible parallelism might be longer than the time it takes to run the query serially.
By default, if the Query Optimizer determines that a query will take less than 5 seconds to execute, parallelism is not considered by SQL Server. This 5 second figure can be modified using the “cost threshold for parallelism” SQL Server option. You can change this value anywhere from 0 to 32767 seconds. So if you set this value to 10, this means that the Query Optimizer won’t consider parallelism for any query that it thinks will take less than 10 seconds to run.
In most cases, you should not change this setting. But if you find that your SQL Server runs many queries with parallelism, and if the CPU rate is very high, raising this setting from 5 to a higher figure (you will have to experiment to find the ideal figure for your situation), will reduce the number of queries using parallelism, also reducing the overall usage of your server’s CPUs, which may help the overall performance of your server.
Another option to consider is to reduce the value from 5 seconds to a smaller number, although this could hurt, rather than help performance in many cases. One area where a smaller value might be useful is in cases where SQL Server is acting as a data warehouse and many very complex queries are being run. A lower value will allow the Query Optimizer to use parallelism more often, which can help in some situations.
You will want to test changes to the default value thoroughly before implementing it on your production servers.
If SQL Server only has access to a single CPU (either because there is only one CPU in the server, or because of an “affinity mask” setting, parallelism is not considered for a query.
If you find in your audit that the cost threshold for parallelism is being used, find out why. If you can’t get an answer, move it back to the default value.
If your SQL Server does not use cursors, or uses them very little, then this setting should never be changed from its default value of “-1”.
A “cursor threshold” of “-1” tells SQL Server to execute all cursors synchronously, which is the ideal setting if the result sets of cursors executed on your server are not large. But if many, or all of the cursors running on your SQL Server produce very large result sets, then executing cursors synchronously is not the most efficient way to execute a cursor.
The “cursor threshold” setting has two other options (besides the default) for running large cursors. A setting of “0” tells SQL Server to run all cursors asynchronously, which is more efficient if most or all of the cursor’s result sets are large.
What if some of the cursor result sets are small and some are large, then what do you do? In this case, you can decide what large and small is, and then use this number as the cutoff point for SQL Server. For example, let’s say that we consider any cursor result set of under 1000 rows as small, and any cursor result set of over 1000 rows as large. If this is the case, we can set the “cursor threshold” to 1000.
When the “cursor threshold” is set to 1000, what happens is that if the Query Optimizer predicts that the result set will be less than 1000, then the cursor will be run synchronously. And if the Query Optimizer predicts that the result set will be more than 1000, then the cursor will be run asynchronously.
In many ways, this option provides the best of both worlds. The only problem is what is the ideal “cursor threshold”. To determine this, you will need to test. But as you might expect, the default value if often the best, and you should only change this option if you know for sure that your application uses very large cursors and that you have tested this option and know for sure that by changing it, it has helped, not hurt performance.
As a part of your audit, you may also want to investigate how often cursors are used, and how large the result sets are. Only by knowing this will you know what the best setting is for your server. Of course, you could always try to eliminate the use of cursors on the server. This way, the setting can remain at the default value, and you don’t have to worry about the overhead of cursors.
Fill Factor (%)
This option allows you to change the default fill factor for indexes when they are built. By default, the fill factor setting is set to “0”. A setting of “0” is somewhat confusing, as what it means is that leaf index pages are filled 100% (not 0%), but that intermediate index pages (non-leaf pages) have some space left in them (they are not filled up 100%). Legal settings for the fill factor setting range from 0 through 100.
The default fill factor only comes into play when you build indexes without specifying a specific fill factor. If you do specify a fill factor when you create a new index, that value is used, not the default fill factor.
In most cases, it is best to leave the default fill factor alone, and if you want a value other than the default fill factor, then specify it when you create an index.
As a part of your audit, note if the fill factor is some figure other than the the default value of “0”. If it is, try to find out why. And if you can’t find out why the default value was changed, or there is not a good reason, switch it back to the default value. Also, if the value has been changed, keep in mind that any indexes created after the default value was changed may be using this default fill factor value. If so, you may need to reevaluate these indexes to see if the fill factor used for creating them is appropriate.