Recovery Interval (min)
If you have a very active OLTP server application with many INSERTS, UPDATES, and DELETES, it is possible that the default “recovery interval” of 0 (which means that SQL Server automatically determines the appropriate recovery interval) may not be appropriate. If you are watching the performance of your server with the Performance Monitor and notice that you have regular periods of 100% disk-write activity (occurring during the checkpoint process), you may want to set the “recovery interval” to a higher number, such as 5 or 10. This figure refers to the maximum number of minutes it will take SQL Server to perform a recovery after it is restarted. The default figure of 0, in effect, works out to be about a maximum recovery period of 1 minute.
Another potential reason to use this “recovery interval” option is if the server is devoted to OLAP or a data warehouse. In these instances, these mostly read-only databases don’t generally benefit from a short recovery interval.
If your server does not match any of the above suggestions, then leaving the default value it generally the best choice.
By extending the checkpoint time, you reduce the number of times SQL Server performs a checkpoint, and if effect, reduce some of SQL Server’s overhead. You may need to experiment with this figure in order to find the ideal compromise between performance and the time it takes for SQL Server to perform a recovery.
Ideally, you want to keep this number as small as possible in order to reduce the amount of time it takes to restart the mssqlserver service the next time it happens. This is because each time the mssqlserver service starts, it goes through an automatic recovery process, and the larger the “recovery interval” is set, the longer the recover process can take. You must decide what is the best compromise in performance and recovery time that best fits your needs.
As a part of your audit, you will want to evaluate the current setting for “recovery interval” in regards to its potential use. For busy OLTP servers, you will want to do a lot of research before you decide to increase the “recover interval” to see if it will help or not. Testing is important. But if your server is a dedicated OLAP or data warehouse server, increasing the “recovery interval” is an easy decision to make.
Scan for Startup Procs
SQL Server has the ability, if properly configured, to look for stored procedures to run automatically when the mssqlserver service starts. This can be handy if you want a particular action to occur on startup, such as the loading of a specific stored procedure into cache so that it is already there when users begin accessing the server.
By default, the “scan for startup procs” is set to “0”, which means that a scan for stored procedures is not done at startup. If you don’t have any startup stored procedures, then this is the obvious setting. There is no point spending resources looking for stored procedures that don’t exist.
But if you do have one or more stored procedures you want to execute on server startup, then this option has to be set to “1”, which turns on the startup scan.
If you find in your audit that this is set to “1”, check to see if there are any start-up stored procedures. If not, then return this option back to the default setting.
Set Working Set Size
The “set working set size” option is used when you want to fix the minimum and maximum sizes of the amount of memory that is to be used by SQL Server when it starts. This option also helps prevents any page swapping.
By default, this setting is set to “0”, which means that this option is not used. To turn on this option, it must be set to “1”, plus, the minimum server memory size and the maximum memory sizes must be set to the same value. This is the value used to reserve the working set size.
As with most options, this one should not generally be necessary. The only time you might want to consider it is if the server is dedicated to SQL Server, has a very heavy load, and has sufficient memory available. Even then, any performance boost gained will be minimal, and you risk the potential of not leaving enough memory to the operating system. Testing is key to the successful use of this option.
If this option is set to a value other than the default, check also to see if the minimum server memory and the maximum server memory settings are set to the same value, otherwise this option will not work correctly. If the conditions above exit, and if thorough testing has been done, then consider leaving this setting. Otherwise, change it back to the default (don’t forget to change back all three related settings).
User Connections
By default, SQL Server only allocates as many user connections as it needs. This allows those who need to connect to connect, while at the same time minimizing the amount of memory used. When the “user connections” setting is set to its default value of “0”, user connections are dynamically set. Under virtually all circumstances, this is the ideal setting.
If you change the default value for “user connections,” what you are telling SQL Server to do is to allocate only the number of user connections you have specified, no more or no less. Also, it will allocate memory for every user connection specified, whether or not it is being used. Because of these problems, and because SQL Server can perform this task automatically and efficiently, there is no reason to change this setting from the default.
If your audit shows a value other than “0”, change it back to zero. Don’t even both asking why.
Now What?
Your goal should be to perform this part of the performance audit, described on this page, for each of your SQL Servers, and then use this information to make changes as appropriate, assuming you can.
Once you have completed this part of the performance audit, you are now ready to audit your SQL Server database configurations.
View the Next Article in the Series
Return to Previous Article in the Series
]]>