Part 1 | Part 2 | Part 3
Too often I see people creating SQL Server maintenance plans and customized jobs for shrinking the database files (data and/or T-Log) periodically. I always recommend to my clients and colleagues not to shrink database files, especially on a regular basis, before they investigate the cause of the database's growth.
I had this discussion a few times already with people who are more concerned about disk space than about performance, so I decided to test it in order to prove to myself and others what the SQL Server performance impacts are when a database file grows as a result of a transaction.
I am about to test a few growth scenarios:
This is the first tip in the series and examines database data file growth in SQL Server.
TESTING ENVIRONMENT
Hardware and software configuration
The hardware and software configuration of the system where I am performing the tests is as follows:
[IMAGE]
Figure 1: Hardware and software configurations for test system.
SQL Server 2005 SP2, Enterprise Evaluation Edition.
The computer has one local iSCSI LUN.
The database configuration
I created a database named ShrinkDB with the following database files and properties:
[IMAGE]
Figure 2: ShrinkDB database files and properties.
The following query:
select size from sysfiles where fileid = 1
Returns: 256
This is the size of the file (in 8-KB pages). 256*8 K = 2 MB.
In this database, I created the following table:
create table ExpandDB (a varchar(8000))
Objective
My goal is to test performance based on the size of the transaction and the data file autogrowth ratio:
I will analyze the differences in the results and try to come up with a definitive conclusion.
Note: Concurrency is exclud
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ed from the tests.
Methods and code description
For each test, I determine an initial file size and an autogrowth ratio of the database file. For example: initial size = 2 MB, autogrowth = 1 MB.
I also have a target file size for the database file, so the first loop to be executed is supposed to insert rows until at least the target size for the data file is reached:
while (select size from sysfiles where fileid = 1) <= <X>
insert into ExpandDB select replicate ('a',8000))
This loop inserts 8000-byte rows into the ExpandDB table, row by row, until the database file grows beyond X. The ExpandDB table is a heap table, with no index or constraint, to avoid any special optimization or index overhead.
After running the first loop and reaching the target file size, for comparison I need to execute the same amount of inserts but without growing the file. For that reason, I have to truncate the table and clean up the transaction log – to make sure the T-Log does not grow either.
The following query:
select count(*) from ExpandDB
Returns the amount of rows inserted in the first loop, and
select size from sysfiles where fileid = 1
Returns the new database data file's size.
The following will truncate the table and T-Log:
truncate table ExpandDB
go
backup transaction ShrinkDB with truncate_only
go
The next step is to execute the same insert commands:
declare @i int
set @i = 1
while @i <= <Z>
begin
insert into ExpandDB select replicate ('a',8000)
set @i = @i + 1
end
Where <Z> is the amount of rows that we had in the table as a result of the first loop.
Another important thing is to clean the cache before executing first and second loops in order to have a fair performance comparison and to eliminate inequality related to caching:
DBCC DROPCLEANBUFFERS
And last, each test will be executed three times in a row (three iterations) to make sure the results are compatible.
Hence, the first step in each iteration will be to make sure the desired data file's initial size is reached. For example, shrinking the database file:
DBCC SHRINKFILE (N'ShrinkDB' , 0, TRUNCATEONLY)
Performance monitoring tools
The performance is monitored using SQL Profiler.
Results summary
The summary of the results is represented in a tabular comparison of CPU, reads, writes and duration between the first and the second loops – first with file growth and then without file growth.
There are two lines of comparison: Percentage difference (<Second loop is >/<First loop is > *100) and amount of difference (<First loop is > - <Second loop is >). If the value is positive, it means the second loop performs better. If it's negative, it means the opposite. At the end of each comparison table, the average of the comparisons includes the average of the results from the three iterations.
At the end of this article, a table compares all of the averages of all the tests.
TEST RESULTS:
Test 1
Initial file size = 256,
Target file size = 8448,
Small transactions (1 row at a time),
Total number of rows: 8160,
File growth by 1 MB
The target file size of 8448 is x 33 times more than the original size of 256.
I run the following code a few times:
[IMAGE]
Figure 3: Click here to download the script above.
Here are SQL Profiler results of three consecutive executions of the above code (I removed the "debugging" queries for convenience):
[IMAGE]
Figure 4: SQL Profiler results from three executions. (Click on image for enlarged view.)
Summary of the results:
[TABLE]Test 2
Initial file size = 256,
Final file size = 34816,
Small transactions (1 row at a time),
Total number of rows: 33373,
File growth by 1 MB
Now let's test a higher number of inserts. The code:
[IMAGE]Figure 5: SQL code to test a higher number of inserts.
Click here to download the SQL script above.
Summary of the results:
[TABLE]Test 3
Initial file size = 256,
Final file size = 34816,
Small transactions (1 row at a time),
File growth by 10 MB
In this test, the code is the same as in Test 2, but the database data file is now configured to autogrow by 10 MB.
Summary of the results:
[TABLE]Test 4
Initial file size = 33664,
Final file size = 66944,
Large transaction (33373 rows),
File growth by 1 MB
In this test, I insert the rows of the ShrinkDB table to the ShrinkTable again, in a large transaction (33373 rows at once). File growth is 1 MB, I had to increase the T-Log file to 200 MB so it will not grow while the transaction is executed. I am testing how file growth affects large transactions.
The code:
[IMAGE]
Figure 6: SQL code to test how file growth affects large transactions.
Click here to download the SQL script above.
Summary of the results:
[TABLE] Test 5
Initial file size = 33664,
Final file size = 66944,
Large transaction (33373 rows),
File growth by 10 MB
In this test, the code is identical to the one running in Test 4, but the file autogrowth is set to 10 MB.
Summary of the results:
[TABLE] Test 6
Initial file size = 256,
Final file size = 128256,
Small transactions (1 row at a time),
Total number of rows: 33373,
File growth by 1,000 MB
In this test, I insert 33373 rows, once letting the file grow and another time with no growth.
The code:
[IMAGE]
Figure 7: Test to allow the SQL data file to grow.
Click here to download the SQL script above.
Summary of the results:
[TABLE]Test 7
Initial file size = 34816,
Final file size = 1314816,
Large transactions (33373 rows),
File growth by 1,000 MB
The code:
[IMAGE]
Figure 8: Test to allow no SQL data file growth.
Click here to download the SQL script above.
Summary of the results:
[TABLE]Conclusion:
Analyzing the averages summary:
Test 1 – small tran, small autogrowth
[TABLE]
Test 2 – small tran, small autogrowth, more rows
[TABLE]
Test 3 – small tran, bigger autogrowth, # rows like Test 2
[TABLE]
Test 4 – large trans, small autogrowth, # rows like Test 2
[TABLE]
Test 5 – large trans, bigger autogrowth, # rows like Test 2
[TABLE]
Test 6 – small tran, large autogrowth, # rows like Test 2
[TABLE]
Test 7 - large trans, large autogrowth, # rows like Test 2
[TABLE]
The results prove that performance decreases due to file autogrowth, especially when transactions are short and autogrowth is small. Also, the higher the autogrowth, the better the performance. The longer the transaction, the less performance is affected by the autogrowth. So when you shrink your database files, you should ask yourself if it's worth it, meaning that the files will not auto grow again in the near future. Furthermore, you should set up the autogrowth threshold according to the expected database activity and growth.
Even when the gaps in CPU, reads and writes between a transaction causing autogrowth and a transaction without it are not very wide, the duration of the transaction is still affected when the file grows.
Next: What happens when Database T-log grows?
Want to know how to disable the shrink database task? Click here.
[IMAGE]
[IMAGE]CAN YOU SHRINK YOUR DATABASE TO DEATH?
[IMAGE]
[IMAGE] Part 1: Examining data file growth
[IMAGE] Part 2: Transaction log autogrowth behavior
[IMAGE] Part 3: Performance implications of T-log autogrowth
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
Great article! I always wanted to know about the influence of disk fragmentation on performance, but I've never taken the time to do these tests myself. Thanks a lot!
—Volker H.
******************************************
I often see requests on various message boards about "how to shrink," whether it's the database or the log file. From a performance standpoint, don't do it. If it's due to archiving or a rarely occurring job, it is OK, but it should be the exception and not the rule. Remember that the physical disk is going to become fragmented over time due to growth or shrinking, and it will impact performance. Monitor growth metrics and size the DB accordingly; there are plenty of scripts to do that. Growths and shrinks during business hours will impact performance, so be proactive, not reactive. Stop the madness by planning ahead. Your users will appreciate it.
—Terry C.
******************************************
This is a good article. However, my real interest is in the growth of transaction log files in SQL Server. I see that such an article is planned on this subject by the same author. Without specialist tools, it seems to be impossible to tell what causes logs to grow to very large sizes. Shrinking the log file using Management Studio is not reliable, and on some systems, we have resorted to scheduling a procedure that repeatedly attempts to shrink the transaction log files until it is down to size prior to performing backups. I look forward to seeing if the next article addresses these issues.
—Scott B.
******************************************
Interesting article and results. I would be curious to see what effect defragging the database file would have, especially after a large number of small increments of autogrow. Perhaps running an additional test after the file is grown, taking the database offline and running contig on the database file.
I had a site that ran an application that would do fairly frequent (about 20 times/week) operation of deleting/rewriting or adding 10,000 rows in a transaction. Queries on the database would get very slow, that the applications would get timeouts (hardware was not the greatest either). But I found that if I defragged the database files, performance would increase dramatically. I was not using any shrinking procedures, but it was growing steadily.
—Mark H.
[TABLE]
