SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance

Earlier, I had written two articles related to Shrinking Database. I wrote about why Shrinking Database is not good.

• SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server
• SQL SERVER – What the Business Says Is Not What the Business Wants

I received many comments on Why Database Shrinking is bad. Today we will go over a very interesting example that I have created for the same. Here are the quick steps of the example.

• Create a test database
• Create two tables and populate with data
• Check the size of both the tables
  • Size of database is very low
• Check the Fragmentation of one table
  • Fragmentation will be very low
• Truncate another table
• Check the size of the table
• Check the fragmentation of the one table
  • Fragmentation will be very low
• SHRINK Database
• Check the size of the table
• Check the fragmentation of the one table
  • Fragmentation will be very HIGH
• REBUILD index on one table
• Check the size of the table
  • Size of database is very HIGH
• Check the fragmentation of the one table
  • Fragmentation will be very low

Here is the script for the same.

USE MASTER
GO
CREATE DATABASE ShrinkIsBed
GO
USE ShrinkIsBed
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Create FirstTable
CREATE TABLE FirstTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Create Clustered Index on ID
CREATE CLUSTERED INDEX [IX_FirstTable_ID] ON FirstTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Create SecondTable
CREATE TABLE SecondTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Create Clustered Index on ID
CREATE CLUSTERED INDEX [IX_SecondTable_ID] ON SecondTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Insert One Hundred Thousand Records
INSERT INTO FirstTable (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Insert One Hundred Thousand Records
INSERT INTO SecondTable (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

Let us check the table size and fragmentation.

Now let us TRUNCATE the table and check the size and Fragmentation.

-- TRUNCATE and SHRINKDB
TRUNCATE TABLE SecondTable
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Shrink the Database
DBCC SHRINKDATABASE (ShrinkIsBed);
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

You can clearly see that after TRUNCATE, the size of the database is not reduced and it is still the same as before TRUNCATE operation. After the Shrinking database operation, we were able to reduce the size of the database. If you notice the fragmentation, it is considerably higher.

The major problem with the Shrink operation is that it increases fragmentation of the database to very high value. Higher fragmentation reduces the performance of the database as reading from that particular table becomes very expensive.

One of the ways to reduce the fragmentation is to rebuild index on the database. Let us rebuild the index and observe fragmentation and database size.

-- Rebuild Index on FirstTable
ALTER INDEX IX_SecondTable_ID ON SecondTable REBUILD
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

You can notice that after rebuilding, Fragmentation is reduced to a very low value (almost same to the original value); however the database size increases way higher than the original. Before rebuilding, the size of the database was 5 MB, and after rebuilding, it is around 20 MB. Regular rebuilding the index is rebuilt in the same user database where the index is placed. This usually increases the size of the database.

Look at irony of the Shrinking database. One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to the size of the database to increase way more than the original size of the database (before shrinking). Well, by Shrinking, one did not gain what he was looking for usually.

Rebuild indexes is not the best suggestion as that will create database grow again.

Let us run following script where we Shrink the database and REORGANIZE.

-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Shrink the Database
DBCC SHRINKDATABASE (ShrinkIsBed);
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Rebuild Index on FirstTable
ALTER INDEX IX_SecondTable_ID ON SecondTable REORGANIZE
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

You can see that REORGANIZE does not increase the size of the database or remove the fragmentation.

Again, I no way suggest that REORGANIZE is the solution over here. This is purely observation using the demo. Read the blog post of Paul Randal.

Following script will clean up the database

-- Clean up
USE MASTER
GO
ALTER DATABASE ShrinkIsBed
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE ShrinkIsBed
GO

There are few valid cases of the Shrinking database as well, but that is not covered in this blog post. We will cover that area some other time in the future. Additionally, one can rebuild index in the tempdb as well, and we will also talk about the same in future.

Are you Shrinking your database? Well, when are you going to stop Shrinking it?

Reference: Pinal Dave (https://blog.sqlauthority.com)