Here are nine “secret” tips that you can use to ensure your SQL Server environment is performing in the most efficient manner possible.
1. If your SQL Server has a lot of memory available and there are many queries running concurrently on the server, you can increase the ‘min memory per query’ option to improve the performance of queries that use hashing or sorting operations.
The SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default ‘min memory per query’ option is equal to 1024 Kb.
2. You can increase the ‘max async IO’ option if your SQL Server works on a high performance server with high-speed intelligent disk subsystem (such as hardware-based RAID with more than 10 disks).
This option specifies the maximum number of outstanding asynchronous disk I/O requests that the entire server can issue against a file. By the way, the ‘max async IO’ SQL Server option is no longer supported in SQL Server 2000.
3. You can change the ‘network packet size’ option to the appropriate value.
This option can improve performance on networks whose base topology supports larger packets than TCP/IP’s default of 4096 bytes. For example, if a client sends or receives large amounts of data, a larger packet size can improve performance, because it results in fewer network reads and writes. The default value for the ‘network packet size’ option is 4096 bytes. Microsoft does not recommend changing this option, because for most applications, the default packet size of 4096 bytes is best.
4. You can change the ‘fill factor’ option to the appropriate value.
The ‘fill factor’ option specifies how full SQL Server will make each index page. When there is no free space to insert new rows on the index page, SQL Server will create a new index page and transfer some rows from the previous page to the new one. This operation is called page splitting. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page.
The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty.
The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created.
If the table contains read-only data (or data that is very rarely changed), you can set the ‘fill factor’ option to 100. When the table’s data is modified often, you can decrease the ‘fill factor’ option to 70 percent, for example.
5. You can increase the ‘recovery interval’ value.
The ‘recovery interval’ option specifies the maximum number of minutes per database that SQL Server needs to complete its recovery procedures. The default value of this option is 0. It means that SQL Server will automatically configure this option.
SQL Server issues a checkpoint using the ‘recovery interval’ option. Microsoft does not recommend changing this option in general cases, but sometimes you can improve performance by changing this option. You can monitor disk-write activity on the data files, and if you see periodic spikes that send disk utilization to 100 percent, you can increase the recovery interval. In this case, Microsoft suggests setting the ‘recovery interval’ option to 5 and continuing monitoring.
6. You can set the ‘priority boost’ SQL Server options to 1.
You can set this option to 1, if you want SQL Server to work with a higher priority than other processes on the same computer. The default value is 0. Setting ‘priority boost’ to 1 can degrade the performance of other applications running on the same computer with SQL Server. So, you should set the ‘priority boost’ SQL Server options to 1 only if you have dedicated a server to SQL Server. In other cases, do not change this option.
7. Set the ‘max worker threads’ options to the maximum number of user connections to your SQL Server box.
The default setting for the ‘max worker threads’ option is 255. If the number of user connections will be less than the ‘max worker threads’ value, a separate operating system thread will be created for each client connection, but if the number of user connections will exceed this value the thread pooling will be used.
For example, if the maximum number of user connections to your SQL Server box is equal to 50, you can set the ‘max worker threads’ options to 50. This frees up resources for SQL Server to use elsewhere. If the maximum number of the user connections to your SQL Server box is equal to 500, you can set the ‘max worker threads’ options to 500, which can improve SQL Server performance because thread pooling will not be used.
8. You can specify the ‘min server memory’ and ‘max server memory’ options.
These options can be used to specify the fixed amount of memory to allocate to SQL Server. In this case, you should set the ‘min server memory’ and ‘max server memory’ to the same value (equal to the maximum amount of physical memory that SQL Server will use), and set the ‘set working set size’ SQL Server option to 1. This can improve performance because SQL Server will not dynamically allocate memory.
You can also change these options when SQL Server works on the same computer with other applications. In this case, the ‘min server memory’ option is used to allow SQL Server to work when other applications pretend to use all available memory and the ‘max server memory’ option is used to allow other applications to work when SQL Server tries to use all available resources.
9. You can specify the ‘set working set size’ SQL Server option to reserve the amount of physical memory space for SQL Server.
Unlike SQL Server 6.5, SQL Server 7.0/2000 can automatically allocate memory (meaning SQL Server can take more memory if needed, or it can give memory back to operation system). This is one of the main advantages in comparison with previous versions, but dynamic memory allocation takes some time. If you know the maximum amount of physical memory that SQL Server will use, you can specify this amount by setting ‘min server memory’ and ‘max server memory’ to the same value (equal to the maximum amount of physical memory that SQL Server will use) and set the ‘set working set size’ option to 1.
