Tips for Performance Tuning SQL Server Database Settings

Tips for Performance Tuning SQL Server Database Settings


http://www.sql-server-performance.co...e_settings.asp

Tips for Performance Tuning SQL Server Database Settings



--------------------------------------------------------------------------------







If you set your SQL Server 7 or SQL 2000 databases and transaction logs to grow automatically, keep in mind that every time this feature kicks in, it takes up a little extra CPU and I/O time. Ideally, you want to minimize how often automatic growth occurs. One way to help do this is to size the database and transaction logs as accurately as possible to their "final" size. Sure, this is virtually impossible to get right on target. But the more accurate your estimates (and some times it takes a some time to come up with a good estimate), the less SQL Server will have to automatically grow its database and transaction logs, helping to boost performance of your application.



This recommendation in particular is important to follow for transaction logs. This is because the more times that SQL Server has to increase the size of a transaction log, the more transaction log virtual files that have to be created and maintained by SQL Server, which increases recovery time, should your transactions log need to be restored. A transaction virtual file is used by SQL Server to internally divide and manage the physical transaction log file. [7.0, 2000] Updated 2-20-2006



*****



In SQL Server 7 and SQL 2000, database and log files can be set to grow automatically. The default growth amount is 10%. This automatic growth number may or may not be ideal for your database. If you find that your database is growing automatically often (such as daily or several times a week), change the growth percentage to a larger number, such as 20% or 30%. Each time the database is increased, SQL Server will suffer a small performance hit. By increasing the amount the database grows each time, the less often it will have to grow.



If your database is very large, 10GB or larger, you may want to use a fixed growth amount instead of a percentage growth amount. This is because a percentage growth amount can be large on a large database. For example, a 10% growth rate on a 10GB database means that when the database grows, it will increase by 1GB. This may or may not be what you want. For example, a fixed growth rate, such as 100MB at a time, might be more appropriate. [7.0, 2000] Updated 2-20-2006



*****



One of the downsides of using SQL Server's ability to grow automatically or to shrink database and transaction log files is that it leads to file fragmentation. I am not talking about internal SQL Server fragmentation, but operating system file fragmentation.



For example, every time a SQL Server file automatically grows, it must find unallocated space on the disk array. More often than not, the space it allocates is not contiguous with the current file. In fact, the space it allocates may come from multiple locations on the disk array. And if you have SQL Server automatically shrink its size, the space it de-allocates will leave fragments of unallocated disk space throughout the array. Most likely, these fragments of unallocated disk space will be filled up with other files as time goes by. The more this happens, the worse the file fragmentation occurs, which in turn leads to lower performance as SQL Server. The more fragmented the files, the longer it takes the array to locate and read the data.



One option is to turn these two features off. This way, you don't have to worry about fragmentation caused by files automatically growing or shrinking. In fact, I always turn off the automatic shrink feature. I do this not only to help reduce fragmentation, but to reduce the overhead incurred by having this feature turned on.



On the other hand, I prefer to leave the automatic growth feature enabled. Why? Because I don't want any database or transaction log to fill up unexpectedly, causing major headaches. So how do I help prevent disk fragmentation if I leave the automatic growth feature on? I do two main things. First, I create the database originally as close to its final size as I can guess. Of course, you can't always guess accurately, and the automatic growth feature may need to kick in at some time. So the second thing I do is to set the automatic growth feature to automatically grow in larger units that the default value. Depending on the size of the database, I have it automatically grow about 20% each time. This way, the automatic growth feature won't kick in as often, and because of this, file fragmentation will be less (although not eliminated).



Of course, another way to deal with file fragmentation is to run a defragmenter tool periodically. I recommend this practice, but caution not to run it too often. Defragmenter tools are very resource intensive, and I prefer to run them manually during lulls when SQL Server is not so busy. Also, defragmenter tools must be run when SQL Server is turned off. This is because these tools cannot defrag a file that is open, which is the case when SQL Server is running. This of course presents a problem in that you must find an available window to perform the defragmentation, which is not always easy.



So if I run a defragmenter tool regularly, why do I even need to bother with the two suggestions above about how to configure automatic file growth and shrinking? I do this because fragmentation still occurs using my suggestions, but at a much slower rate. Because of this fragmentation, I still need to run the defragmenter tool. But since there is less to defragment, I don't have to run tool as often, and when it does run, it doesn't have to run as long.



In any event, if you currently have both automatic growth and shrinking turned on, and you aren't using a defragmenter, then your database's I/O performance is probably being hurt because of file fragmentation. [7.0, 2000] Updated 2-20-2006



*****



If a database will be used for read-only purposes only, such as being used for reporting, consider changing the "read-only" setting to on (the default setting is off). This will eliminate the overhead of locking, and in turn, potentially boost the performance of queries that are being run against it. If you need to modify the database, you can also turn the setting off, make your change, and then turn it back on. [6.5, 7.0, 2000] Updated 2-20-2006



*****



When "auto create statistics" is turned on for a database (which it is by default), statistics are automatically created on all columns used in the WHERE clause of a query. This occurs when a query is optimized by the Query Optimizer for the first time, assuming the column doesn't already have statistics created for it. The addition of column statistics can greatly aid the Query Optimizer so that it can create an optimum execution plan for the query.



If this option is turned off, then missing column statistics are not automatically created, when can mean that the Query Optimizer may not be able to produce the optimum execution plan, and the query's performance may suffer. You can still manually create column statistics if you like, even when this option is turned off.



There is really no downside to using this option. The very first time that column statistics are created, there will be a short delay as they are created before the query runs the first time, causing the query to potentially take a little longer to run. But once the column statistics have been created, each time the same query runs, it should now run more efficiently than if the statistics did not exist in the first place. [7.0, 2000] Updated 8-21-2006



*****



To provide the up-to-date statistics the query optimizer needs to make smart query optimization decisions, you will generally want to leave the "auto update statistics" database option on (the default setting). This helps to ensure that the optimizer statistics are valid, helping to ensure that queries are properly optimized when they are run.



But this option is not a panacea. When a SQL Server database is under very heavy load, sometimes the auto update statistics feature can update the statistics on large tables at inappropriate times, such as the busiest time of the day.



If you find that the auto update statistics feature is running at inappropriate times, you may want to turn it off, and then manually update the statistics (using UPDATE STATISTICS) when the database is under a less heavy load.



But again, consider what will happen if you do turn off the auto update statistics feature. While turning this feature off may reduce some stress on your server by not running at inappropriate times of the day, it could also cause some of your queries not to be properly optimized, which could also put extra stress on your server during busy times.



Like many optimization issues, you will probably need to experiment to see if turning this option on or off is more effective for your environment. But as a rule of thumb, if your server is not maxed out, then leaving this option on is probably the best decision. [7.0, 2000] Updated 8-21-2006



*****



Many databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don't be tempted to use the "auto shrink" database option, as it can waste SQL Server resources unnecessarily.



By default, the auto_shrink option is turned off, which means that the only way to free up empty space in a database is to do so manually. If you turn it on, SQL Server will then check every 30 minutes to see if it needs to shrink the database. Not only does this use up resources that could better be used elsewhere, it also can cause unexpected bottlenecks in your database when the auto_shrink process kicks in and does its work.



If you need to shrink databases periodically, perform this step manually using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, or you can use the SQL Server Agent or create a Database Maintenance Plan to schedule regular file shrinking. [7.0, 2000] Updated 8-21-2006



*****



This "auto close" database option is designed for use with the Desktop version of SQL Server 7.0 and 2000, not for the server versions. Because of this, it should not be turned on. What this option does is to close the database when the last database user disconnects from the database. When a connection requests access to the database, the database has to be reopened, and this takes time and overhead.



The problem with this is that if the database is accessed frequently, which is the most likely case, the database may have to close and reopen often, which puts a large performance drag on SQL Server and the applications or users making the connection. [7.0, 2000] Updated 8-21-2006



*****



Because data pages in SQL Server (8K) and Windows 2000 or 2003 Server (512 bytes) are different sizes, it is possible during power failures, or if you are having disk driver or physical disk problems, for your database to become corrupted.



Here's why. Every time the operating system writes an 8K SQL Server data page to disk, it must break up the data into multiple 512-byte pages. After the first 512 byte of data is written, SQL Server assumes that the entire 8K has been written to disk successfully. So if the power should go out before all of the 512 byte pages that make up the 8K SQL Server page are written, then SQL Server does not know what has happened. This is known as a torn page.



As you can imagine, this corrupts the data page, and in effect makes your entire database corrupt. There is no way to fix a database made corrupt due to a torn page, except restore a known good backup. One of the best ways to prevent this problem is to ensure your server has a battery backup. But this does not prevent all problems because a defective disk driver can also cause similar problems (I have seen this.)



If you are worried about getting torn pages in your SQL Server databases, you can have SQL Server tell you if they occur. There is a database option called "torn page detection" that can be turned on and off at the database level. Each database must be set separately. Keep in mind that this option does not prevent torn pages; it only tells you if you have one. Once it discovers one, your database is marked as corrupt, and you have little choice but to restore your database with your latest backup.



In SQL Server 7, this option is turned off by default, and you must turn it on for every database you want it on for. In SQL Server 2000, this option is turned on by default for all databases.



So what's the big deal, why not just turn it on and be safe? The problem is that turning this feature on hurts SQL Server's performance. Maybe not much, but if you already have a SQL Server that is maxed out, then it might make a noticeable difference. As a DBA, you must weight the pros and cons of using this option, and make the best decision for your particular situation. [7.0, 2000] Updated 2-20-2006