Cursor Threshold
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.
]]>