Although SQL Server is essentially self-tuning, it can take some time for SQL Server to figure out the optimum internal setting, perhaps several hours, depending on the type and level of activity the SQL Server is experiencing.
When the SQL Server service is first started, SQL Server begins with its default configuration settings. But as activity begins in the databases and as queries are run, SQL Server will fine-tune itself and performance will improve with time.
Given this, do not expect to get accurate Performance Monitor results until SQL Server has had a chance to fine-tune itself. Give your databases some time after the mssqlserver service starts before SQL Server fine-tunes itself. [7.0, 2000, 2005] Updated 3-6-2006
*****
If you are running SQL Server 6.5 or SQL Server 7.0, on servers with high-speed disk controllers, you may be able to increase I/O performance by changing the “Max Async IO” SQL Server configuration setting. The default setting is 32, and the maximum setting is 255. You will need to experiment with this setting before you get the ideal setting for your server.
A rule of thumb for coming up with an appropriate “Max Async IO” setting is to multiply by either 2 or 3, the number of physical drives available for simultaneous I/O.
This experimenting should not be done on a production server as you must restart SQL Server each time you make a change. If you set this figure too high, you may start getting a backlog of disk requests, which will increase the Performance Monitor counter Physical Disk Object: Avg. Disk Queue Length to over two per physical drive, which indicates an I/O bottleneck. This option is not available for SQL Server 2000. [6.5, 7.0]
*****
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 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 to a maximum recovery period of about 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 generally don’t 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, reduces 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 when it is restarted.
Ideally, you want to keep the recovery interval as small as possible in order to reduce the amount of time it takes to restart the mssqlserver service. Because each time the mssqlserver service starts, it goes through an automatic recovery process and the larger the “recovery interval,” the longer the recover process will take. You must decide what is the best compromise in performance and recovery time that best fits your needs. [6.5, 7.0, 2000, 2005] Updated 8-21-2006
The default network packet size for clients communicating with SQL Server is 4096 bytes. In most cases, this is the overall best setting. But in some special cases, this SQL Server configuration setting should be changed. If the client regularly transfers large amounts of text or image data to SQL Server, or performs large BCP or DTS operations, then increasing the default size may boost performance because the number of network packets is reduced. On the other hand, if your application only sends and receives small amounts of data, then a smaller packet size can boost responsiveness.
Keep in mind that client software can overrule this setting in SQL Server. Only try changing this setting if you are very familiar with network traffic analysis and have the ability to test the performance, before and after the change. [6.5, 7.0, 2000, 2005] Updated 3-6-2006
*****
If your server has plenty of physical RAM, and you run many memory-intensive queries that make extensive use of hash and sort operations, and there are many queries running concurrently on the server, consider boosting the “min memory per query” SQL Server configuration option from its default of 1024K, to a higher value. This setting tells SQL Server to allocate automatically, at a minimum, the amount of memory set in this configuration setting for running each query. Increasing this setting can boost the performance of memory-intensive queries.
On the other hand, if you increase this value too high, queries could get slower. This is because SQL Server will not run the query until it can allocate the amount of memory specified in this setting, or until the value specified in the “query wait” SQL Server configuration option is exceeded.
You will want to test this setting with several different values until you come up with the ideal setting for your particular SQL Server environment. [7.0, 2000, 2005] Updated 3-6-2006
*****
The “max degree of parallelism” option allows you to specify if parallelism is turned on, turned off, or only turned on for some CPUs, but not for all CPUs in your server. Parallelism refers to the ability of the Query Optimizer to use more than a single CPU to execute a single query. By default, parallelism is turned on and can use as many CPUs as there are in the server. (Unless this has been reduced due to the affinity mask option). If your server has only one CPU, the “max degree of parallelism” value is ignored.
The default for this option is “0”, which means that parallelism is turned on for all available CPUs. If you change this setting to “1”, then parallelism is turned off for all CPUs. Also, this option allows you to specify how many CPUs can be used for parallelism. For example, if your server has 8 CPUs and you only want parallelism to run on 4 of them, you can specify a value of 4 for this option. Although this option is available, it is doubtful if using it would really provide any performance benefits.
If parallelism is turned on, as it is by default if you have multiple CPUs, then the query optimizer will evaluate each query for the possibility of using parallelism, which takes a little overhead. On many OLTP servers, the nature of the queries being run often doesn’t lend itself to using parallelism for running queries. Examples of this include the INSERT, UPDATE, and DELETE statements. Because of this, the query optimizer is wasting its time evaluating each query to see if it can take advantage of parallelism. If you know that your queries will probably rarely or never need the advantage of parallelism, you can save a little overhead by turning this feature off, so queries aren’t evaluated for this.
Of course, if the nature of the queries that are run on your SQL Server can take advantage of parallelism, you will not want to turn parallelism off. For example, if your OLTP server runs many correlated subqueries, or other complex queries, then you will probably want to leave parallelism on. You will want to test this setting to see if making this particular change will help, or hurt, your SQL Server’s performance in your unique operating environment. [7.0, 2000, 2005] Updated 8-21-2006