Monday, February 9, 2015

Why Sql server Database Data file shrink is not a good practice?

Often in DBA world you hear shrink data files and log files. Data file shrink is not a good approach and leads heavy index fragmentation.

Let me demonstrate with some examples. Here I am using the following Sql server version. However the scripts will run on 2008 R2.

Microsoft SQL Server 2014 - 12.0.2254.0 (X64)
Jul 25 2014 18:52:51
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Shrink database causes heavy index fragmentation. when sql server try to free up space, it moves pages from end of the file to beginning of the file.



/*
Author: Hari Prasad Ere
Sub: Shrink DB file demo.
*/

--Create database for demo.
IF DATABASEPROPERTYEX (N'DbShrinkTest', N'Version') IS NOT NULL
    DROP DATABASE [DbShrinkTest];
GO
CREATE DATABASE DbShrinkTest;
GO
USE [DbShrinkTest];
GO

-- supress messages
SET NOCOUNT ON;
GO

 -- Create test tables
CREATE TABLE [Wine] (
    [id] INT IDENTITY,
    [Winetype] CHAR (8000) DEFAULT 'Malbec');
GO
-- Fill up the wine table by excuting go batch 3000 times
INSERT INTO [Wine] DEFAULT VALUES;
GO 3000


-- Create the product table
CREATE TABLE [ProdTable] (
    [ID] INT IDENTITY,
    [ProdName] CHAR (8000) );
CREATE CLUSTERED INDEX [prod_ID] ON [ProdTable] ([ID]);
GO

-- load product table from product adventure works database by excuting go batch 10 times
INSERT INTO [ProdTable] (prodname)
SELECT [Name] from AdventureWorks2014.Production.Product
GO 5

-- select * from wine
--select * from prodtable

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DbShrinkTest'), OBJECT_ID (N'ProdTable'), 1, NULL, NULL);
GO
-- avg_fragmentation is returned on my server is 0.396825396825397.

-- Now lets start test drop the wine table and run shrink
DROP TABLE [Wine];
GO

-- Shrink the database
DBCC SHRINKDATABASE ([DbShrinkTest]);
(Note: shrink file also leads index fragmentation).
GO
Machine generated alternative text:
is Messages 
Dbld Fileld CumentSize 
Minimum Size 
used P ages 
Estim at ed Pages


-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DbShrinkTest'), OBJECT_ID (N'ProdTable'), 1, NULL, NULL);
GO
-- avg_fragmentation is returned on my server is 99.9206349206349.
Machine generated alternative text:
Resuğs Messages 
1 7ğğ206349206ü9 J


-- cleanup
IF DATABASEPROPERTYEX (N'DbShrinkTest', N'Version') IS NOT NULL
    DROP DATABASE [DbShrinkTest];
GO


Workarounds

  1. If your environment is required to shrink, then  REORGANIZE / REBUILD indexes after the SHRINKFILE/Shrink DB.

   2)    Add a new file group, Move user tables and indexes into new file group. Build indexes on new file group and shrink the file on old file group.










No comments:

Post a Comment