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 resultset of under 1000 rows as small, and any cursor resultset 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 helps, not hurts performance. [7.0, 2000] Updated 1-28-2005
The fill factor (%) SQL Server configuration 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%), and 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 range from 0 through 100.
The default fill factor only comes into play when you build new 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. [7.0, 2000, 2005] Updated 1-28-2005
*****
The index create memory setting determines how much memory can be used by SQL Server for index creating sorts. The default value of “0” tells SQL Server to automatically determine the ideal value. In almost all cases, SQL Server will configure the amount of memory optimally.
But in unusual cases, especially with very large tables, it is possible for SQL Server to make a mistake, causing large indexes to be created very slowly, or not at all. If you run into this situation, you may want to consider setting the Index Create Memory setting yourself, although you will have to trial and error the setting until you find the optimum one for your situation. Legal settings for this option run from 704 to 2147483647. This number refers to the amount of RAM, in KB, that SQL Server can devote to creating the index.
Keep in mind that if you do change the setting, that this memory will then be allocated for index creation and will not be available for other use. If your server has more than enough RAM, then this will be no problem. But if your server is short on RAM, changing this setting could negatively affect the performance of other aspects of SQL Server. You might consider making this change only when you are creating or rebuilding large indexes, and leave the setting to the default all other times. [7.0, 2000] Updated 1-28-2005
*****
SQL Server 7.0 and 2000, by default, run in what is called “thread mode.” What this means is that SQL Server uses what are called UMS (User Mode Schedulers) threads to run user processes. SQL Server will create one UMS thread per processor, with each one taking turns running the many user processes found on a busy SQL Server. For optimum efficiency, the UMS attempts to balance the number of user processes run by each thread, which in effect tries to evenly balance all of the user processes over all the CPUs in the server.
SQL Server also has an optional mode it can run in, called fiber mode. In this case, SQL Server uses one thread per processor (like thread mode), but the difference is that multiple fibers are run within each thread. Fibers are used to assume the identity of the thread they are executing and are non-preemptive to other SQL Server threads running on the server. Think of a fiber as a “lightweight thread,” which, under certain circumstances, takes less overhead than standard UMS threads to manage. Fiber mode is turned on and off using the “lightweight pooling” SQL Server configuration option. The default value is “0”, which means that fiber mode is turned off.
So what does all this mean? Like everything, there are pros and cons to running in one mode over another. Generally speaking, fiber mode may” be beneficial when all of the following circumstances exist:
- Eight or CPUs are found on the server (the more the CPUs, the larger the benefit).
- All of the CPUS are running near maximum (95-100%) most of the time.
- There is a lot of context switching occurring on the server (as reported by the Performance Monitor System Object: Context Switches/sec. Generally speaking, more than 20,000 context switches per second is considered high.
- The server is making little or no use of distributed queries or extended stored procedures.
If all the above are true, then turning on “lightweight pooling,” option in SQL Server may see a small boost in performance.
But if the four circumstances are all not true, then turning on “lightweight pooling” could actually degrade performance. For example, if your server makes use of many distributed queries or extended stored procedures, then turning on “lightweight pooling” will definitely cause a problem because they cannot make use of fibers, which means that SQL Server will have to switch back-and-forth from fiber mode to thread mode as needed, which hurts SQL Server’s performance.
You will also want to carefully test (before and after) the affect of this setting on your server’s performance. While in test scenarios, some DBAs have noticed a boost in performance, in the real world, the benefit is much harder to quantify and identify.
Generally speaking, this option should only be tried in cases where you have tried every other option to boost performance, and there are no more to try.
This option is available under the SQL Server’s “Properties”, “Processor” tab, in addition to using sp_configure. [7.0, 2000] Updated 8-17-2003