This article is part 9 in a series on the top 10 most common mistakes that I have seen impact SQL Server Performance and Resiliency. This post is not all-inclusive.
Most common mistake #9: Automatically Shrinking Your Database
This is a topic that has been written about frequently, and most often, I try not to re-hash what many people have already blogged about. However, as often as I see this I would be amiss if I did not add auto shrink to the list.
Often you will see IT professionals approaching their tasks from different angles. Consider if you were a Systems Admin and you knew you needed some additional storage on a server you might send a request to the storage admin requesting an additional 50 gigs, or whatever amount you need. As a Database Professional, you would be wise to not only include the size of storage that you need but also the performance specifications that you require. As a DBA, we need to understand that SQL Server management may not always translate well to other types of systems management. Now granted this should be no surprise, it is understand we do not approach all things the same way, but where this comes into play is the understanding we all have different backgrounds. We became DBA’s from different career paths.
If you are new to being a Database Administrator or the Primary focus of your job is not to be a DBA you may see the benefits of shrinking a database automatically. If the database shrinks by itself, it might be considered self-management; however, there is a problem when doing this.
When you shrink a data file SQL Server goes in and recovers all the unused pages, during the process it is giving that space back to the OS so the space can be used somewhere else. The downstream effect of this is going to be the fact your indexes are going to become fragmented. This can be demonstrated in a simple test.
I have a database in my lab based on the Chicago Crime Stats. I have been doing a lot of testing in the database with an automated indexing script, that has me inserting a deleting a large number of rows at different times. Over time this database has become rather large for my small lab, it is time to shrink it down to a more manageable size. The first thing done is to check what the status of my indexes is.
This is a simple query that will return all the indexes in the database with its fragmentation level.
SELECT db_name() as [database],
Object_Name(ps.object_id) as [table],
i.name as Index_Name,
round(avg_fragmentation_in_percent, 0) as Frag
FROM sys.dm_db_index_physical_stats(db_id(), null, null, NULL, NULL) ps
Join sys.indexes i on ps.Object_ID = i.object_ID and ps.index_id = i.index_id
The results look like this:
More or less the indexes are looking good; there is not a lot of fragmentation except in the one table (that is a discussion for later topics). What happens if I shrink the whole database, to include not only the log but also the data file as well?
Use the following T-SQL:
DBCC ShrinkDatabase ([ChicagoCrimeStats])
Rerunning the index fragmentation script, I now receive these results:
If I have queries that use the IDX_Crimes_Frag_XCORD_Clustered index, there is a real good chance the performance on that query is going to degrade.
There are times when you may need to shrink a file, some considerations could be after a large delete of records or maybe you archived much of the data out of the database. These sort of operations remove data leaving your databases with a lot of free space. This free space can be reclaimed by using the DBCC Shrinkfile or DBCC Shrinkdatabase T-SQL commands, however be aware you should re-index after those statements are run.
It is not a bad thing to shrink a database as long as you do it in a controlled manor with proper maintenance afterwards.
If you have questions about Optimizing your SQL Server or need assistance with SQL Server in general, reach out to us! XTIVIA and I can assist you with adding resiliency for your business https://www.xtivia.com/contact-us. Please don’t miss my other blogs regarding this topic.
Top 10 Tips for SQL Server Performance and Resiliency
1. Improper Backups
2. Improper Security
3. Improper Maintenance
4. Not having a baseline
5. Max Memory settings
6. Change History
7. Disaster Recovery Plans
8. Configure TempDB