Can you shrink your SQL Server database to death?

Conclusion:

Analyzing the averages summary:

Test 1 – small tran, small autogrowth
CPU Reads Writes Duration (ms)
Average % improvement 45.52345285 20.74529261 0.125700284 39.5361444
Average difference 895.6666667 14491 10.33333333 5125.666667

Test 2 – small tran, small autogrowth, more rows
CPU Reads Writes Duration (ms)
Average % improvement 39.16279497 20.96413262 0.158643548 38.31454107
Average difference 2638.666667 59797.33333 53.33333333 20901.66667

Test 3 – small tran, bigger autogrowth, # rows like Test 2
CPU Reads Writes Duration (ms)
Average % improvement 33.71427131 21.60919146 0.072447159 15.46763174
Average difference 3037 61351.33333 24.33333333 7570.666667

Test 4 – large trans, small autogrowth, # rows like Test 2
CPU Reads Writes Duration (ms)
Average % improvement 0.502786472 0.118572811 0.002995716 47.58833245
Average difference 26.33333333 1044 1 17230.33333

Test 5 – large trans, bigger autogrowth, # rows like Test 2
CPU Reads Writes Duration (ms)
Average % improvement 0.13134708 0.011862299 0.002995716 11.86357121
Average difference 5 104.3333333 1 2675.333333

Test 6 – small tran, large autogrowth, # rows like Test 2
CPU Reads Writes Duration (ms)
Average % improvement 1.489788193 0.005990618 0.005990625 8.857505083
Average difference 57.33333333 13 2 3547.666667

Test 7 - large trans, large autogrowth, # rows like Test 2
CPU Reads Writes Duration (ms)
Average % improvement 4.23231944 0.155980142 -6.82066382 8.157970755
Average difference 157 1420 -1351.66667 2048.666667

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.

ABOUT THE AUTHOR:   

[IMAGE]Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.
Copyright 2008 TechTarget

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.