SQL Server Enterprise Platform Management Practice book Notes--sql Server How to set auto-grow and auto-shrink items

SQL Server allows the user to set the database initial and maximum values, which can be configured by auto-grow or auto-shrink. With these configurations, we can prevent application modifications that could be caused by database space problems, or when SQL Server disk space is exhausted. In general, if the database is not very busy, the default setting is auto-grow, which can satisfy most of the requirements. However, in a large number of concurrent cases, the application data file and log file growth itself is a very consumption of system resources and impact performance of the work. So if you rely entirely on SQL Server auto-completion, you may cause system performance to be unstable. A more sophisticated system should anticipate and guide the flow of data in advance, taking into account possible space-use requirements. Try to avoid running out of space so that SQL Server has to automatically grow. At the same time, ensure that each automatic growth can be completed in an acceptable timeframe and meet the needs of the client application in a timely manner.

Let's discuss some of the features of SQL Server data files and log file space requests.

If we have a database, it has 3 data files (if they belong to the same filegroup) and two log files

Filename	Existing size (MB)	Existing idle size (MB)
Mydb_primary	2000	200
Mydb_secondary1	2000	100
Mydb_seconday2	2000	100
Mydb_log1	1000	500
Mydb_log2	1000	1000

Suppose you now have a client to insert 40MB of data, 20MB of log records, how will SQL Server write to these files?

SQL Server has different ways of handling data and logs.

Data files

SQL Server distributes the new data to all spatial data files according to the size of the existing free space for all the files in the same filegroup . If a file is already full, SQL Server does not continue to write to the file, but instead writes it to other space-bound files.

For example: Because 3 files are idle, 200:100:100,40MB data is written to 3 files according to the 20MB:10MB:10MB ratio.

Log file

SQL Server writes in strict order to log records. So although there are two log files here, SQL Server writes only one of them at a point in time. Only this file is full, and SQL Server will write to the other one.

In the above case database, 20MB of log records will be written to Mydb_log1.

Sometimes we add multiple data files and put them on separate disks to achieve the purpose of dispersing I/O load. From the above treatment we can see. If you want to do this, for data files, you must ensure that all data files in the same filegroup have essentially the same size of free space. (not as large as these files can be.) if the data file on a hard disk is already full, SQL Server will no longer write to the hard disk. If the amount of free space is relative, the number of SQL Server writes is also relatively reduced.

For log files, because SQL Server has only one file at a time, adding multiple log files can be of little help to performance.

What happens to SQL sever if all the files are full? Here the price of the data and the log file will be slightly different.

For data files, SQL Server chooses one of the files (possibly any) to autogrow, rather than having each data file grow automatically. All subsequent data is written to this automatically growing file until the file is full again and SQL Server is going to do the next autogrow. In other words, relying on autogrow to see only one file growth, it is difficult to enjoy the effects of I/O load balancing.

For log files, SQL Server automatically grows the current log file to guarantee the continuity of the log records.

When an action triggers a file to grow automatically, SQL Server waits for that action. The original operation will continue until the file auto-growth is over. If auto-growth takes a long time, the original operation can not wait to cancel the timeout (the default threshold is 15 seconds), not only the operation will be rolled back, the file autogrow will also be canceled. In other words, the document did not get any growth. The worst case scenario is that, at a point in time, there are a lot of things that need to be requested for new space, but no one can wait until the file auto-growth is complete. This is reflected in the end user's data, that any modification operation can not be committed, all timeouts. Until a connection can wait long enough for SQL Server to get this auto-growth done. After the finish, the other time the operation will suddenly return to normal.

Why is an automatic growth likely to take a long time? This is largely due to the large amount of space that needs to grow each time. Data files are stored in units of 8KB. So when the data file grows, SQL Server also formats the newly added parts. If you want to grow a lot of space at a time, such as on a GB or dozens of GB, the formatting process can be time-consuming. The later version of SQL Server2005 uses a bit of latency technology. As long as the growth of new space has been allocated well. This auto-growth is even done. SQL Server will use a background thread to finish the remaining formatting. This greatly shortens the time for an increase. The front end is not prone to timeout failures.

Another extreme is that each time the autogrow value is too small, SQL Server has to do several auto-growth to meet the operational requirements. The same size, a one step time score a few times the growth of a lot less. So the auto-growth value is not too small.

In view of the above points, let us summarize:

1, to set a fixed size growth, but not proportional . This avoids the unnecessary hassle of having to grow too much or too little at a time. It is recommended to compare smaller databases and set up 50MB to 100MB at a time. For large databases, set a growth of 100MB to 200MB at a time.

2, to regularly detect the use of each data file, as far as possible to ensure that the remaining space for each file as large, or the desired proportion.

3 . Set the maximum file size to prevent SQL Server files from growing out of disk space and affecting the operating system.

4, after the occurrence of growth, to timely check the new data file space allocation situation. Avoid SQL Server always writing data to individual files.

In addition to automatic growth, the database has an auto-shrink feature. If this feature is set, SQL Server checks file usage every half hour. DBCC SHRINKFILE actions are automatically run if the free space is greater than 25%,sql server. So this feature prevents the data from requesting too much space and not using it. For a system with a very tight disk space, this setting is undoubtedly helpful. But from the database's own health and performance considerations, this setting is not recommended for multiple use. This is because:

1. SQL Server will grow automatically only when the space is exhausted. If there is no reason to look for growth, it will fundamentally avoid the exhaustion of space. Although you can temporarily shrink the file size with the DBCC SHRINKFILE feature, the next data is likely to grow. Shrinking a database is just a way to cure the symptoms.

2, data file contraction to bring more fragments of the file

3. Whether the database shrinks or grows, it is a waste of resources for SQL Server. In heavy-duty systems, the impact on performance is particularly high. They are trying to avoid rather than encourage the operation.

In a word: In a busy database, the recommended setting is to turn on the automatic database Growth option to prevent the application from running out of database space, but avoid automatic growth. Also, try not to use the auto-shrink function.

SQL Server Enterprise Platform Management Practice book Notes--sql Server How to set auto-grow and auto-shrink items