SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
- by pinaldave
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.
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
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 high.
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 reduces to a very low value (almost same to 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 rebuild 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 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 indexing is not the best suggestion as that will create database grow again.
I have always remembered the excellent post from Paul Randal regarding Shrinking the database is bad. I suggest every one to read that for accuracy and interesting conversation.
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 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 future. Additionally, one can rebuild index in the tempdb as well, and we will also talk about the same in future. Brent has written a good summary blog post as well.
Are you Shrinking your database? Well, when are you going to stop Shrinking it?
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology
