Query Governor Cost Limit
The “query governor cost limit” option allows you to limit the maximum length of time a query can run. For example, let’s say that some of the users of your server like to run very long-running queries that really hurt the performance of your server. By setting this option, you could prevent them from running any queries that exceeded, say 300 seconds (or whatever number you pick). The default value for this setting is “0”, which means that there are no limits to how long a query can run.
The value you set for this option is approximate, and is based on how long the Query Optimizer estimates the query will run. If the estimate is more than the time you have specified, the query won’t run at all, producing an error instead. This can save a lot of valuable server resources.
On the other hand, users can get real unhappy with you if they can’t run the queries they have to run in order to do their job. What you might consider doing is helping those users to write more efficient queries. That way, everyone will be happy.
Unlike most of my other suggestions, if your audit turns up a value here other than “0”, great. As long as users aren’t complaining, this is a good deal. In fact, if this setting is set to “0”, consider adding a value here and see what happens. Just don’t make it too small. You might consider starting with value of about 600 seconds and see what happens. If that is OK, then try 500 seconds, and so on, until you find out when users start complaining, then you can back off.
Query Wait (s)
If SQL Server is very busy and is hurting for more memory resources, it will queue what it considers memory-intensive queries (those that use sorting or hashing) until there is enough memory available to run them. In some cases, there just isn’t enough memory to run them and they eventually time out, producing an error message. By default, a query will time out after a period of time equal to 25 times the estimated amount of time the Query Optimizer thinks it will take for the query to run.
The best solution for such a problem is to add more memory to the server, or to reduce its load. But if that can’t be done, one option, although fraught with problems of its own, is to use the “query wait” configuration option. The default setting for this option is “-1”, which waits the time period described above, and then causes the query to time out. If you want the time out period to be greater so that queries won’t time out, you can set the “query wait” time to a large enough number. As you might guess, you will have to determine this time out number yourself through trial and error.
The problem with using this option is that a transaction with an intensive query may be holding locks, which can cause deadlock or other locking contention problems, which in the end may a bigger problem than the query timing out. Because of this, this option is not recommended to be changed.
If you find a non-default value in your audit, find out why. If there is no good reason to keep it, change it back to the default value. But, if someone has thought this out thoroughly, and if you cannot detect any locking issues, then consider leaving this option as is.
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 the best compromise is 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 easier 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
This option has been removed from SQL Server 2005, and has only been retained for compatibility with existing scripts.
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.
]]>