Defragmentation script for SQL Server

When you SQL server database becomes slower over time without apparent reasons, probably you’re facing a fragmentation problem. Database table fragmentation is one of the major performance killers.

With the following maintenance script you defrag you tables and indexes.

-- =============================================
-- Copyright by Author Sajal Dam, ISBN 1590594215 
-- Description: Performs defragmentation actions
-- tables and indexes in your database.
-- Execute this script once a month or more often 
-- if necessary
-- =============================================
CREATE PROC [dbo].[pr_Sys_DefragDatabase]
AS
SET NOCOUNT ON
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
    ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
    IndexId INT, [Level] INT, Pages INT, [Rows] INT,
    MinimumRecordSize INT, MaximumRecordSize INT,
    AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
    ExtentSwitches INT, AverageFreeBytes FLOAT,
    AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
    ActualCount INT, LogicalFragmentation FLOAT,
    ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
    [id] INT IDENTITY,
    ObjectName VARCHAR(255),
    IndexName VARCHAR(255),
    ScanDensity FLOAT
)

--Identify all user tables in the current database to analyze
-- fragmentation
SELECT SO.id as [id], SO.name as [name] INTO #UserTables
    FROM sysobjects SO
    INNER JOIN sysusers SU
    ON SO.uid = SU.uid
    WHERE type = 'U'
    AND SU.name = 'dbo' 
    ORDER BY [id]
 
--Determine fragmentation of every user table/index
DECLARE @id INT, @name VARCHAR(255), @TableCnt INT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #UserTables
    WHILE @TableCnt > 0
    BEGIN
        SELECT TOP 1 @id=[id], @name=[name]
        FROM #UserTables
        WHERE [id] > @id
        
        INSERT INTO #FragmentationResult
        EXEC('DBCC SHOWCONTIG([' + @name + '])
            WITH ALL_INDEXES, TABLERESULTS')
        SET @TableCnt = @TableCnt - 1
    END
    
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
    FROM #FragmentationResult
    WHERE ScanDensity < 50 --Scan Density is low
    AND LogicalFragmentation > 9 --Logical Scan Fragmentation is high
    AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult

--Defragment tables/indexes with high fragmentation
DECLARE @oname VARCHAR(255), @iname VARCHAR(255), @sdensity FLOAT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #Defragmentation
WHILE @TableCnt > 0
BEGIN
    SELECT TOP 1 @id=[id], @oname = ObjectName, 
        @iname = IndexName, @sdensity = ScanDensity
        FROM #Defragmentation
        WHERE [id] > @id
    PRINT '** De-fragmentation #' + CAST(@id AS VARCHAR(15))+ ' **'
    PRINT 'DBCC DBREINDEX on [' + @oname + '].[' + @iname
            + '] with ScanDensity = ' + CAST(@sdensity AS VARCHAR(15)) + '%'
    DBCC DBREINDEX(@oname, @iname)
    SET @TableCnt = @TableCnt - 1
END

--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF

 

Hope this help,

How to change database primary key field with a foreign key constraint

Today I needed to increase a varchar database from 2 position to 50 positions. At first I thought this would be easy, just make a change script like this:

   1: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null

After running in SSMS I got two error messages back:

Msg 5074, Level 16, State 1, Line 1
The object 'FK_CategoryCode' is dependent on column 'Code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.

Oops.. The Code field is used as a primary key here and referenced by another table tb_Tips. Luckily the database protects data loss by checking the constraints on a table before allowing any changes. (Btw, my database is for storing user tips (tb_Tips) and are categorized within groups via tb_Categories.).

Since I want to increase from 2 to 50 characters, I don’t get any data loss, so I really want to change my field with keeping my data in the tables. After some thinking, I produced a rough plan:

  • Temporarily remove the foreign key constraint in tb_Tips
  • Temporarily remove the primary key constraint in tb_Categories
  • Now the tb_Categories.Code field is key constraints free: upgrade the Code field from 2 to 50 characters
  • Upgrade the reference field in tb_Tips
  • Add primary key constraint for tb_Categories
  • Add foreign key constraint for tb_Tips
  • Done! (and check the data in the tables)

So here is the script:

   1: DECLARE @constraintName varchar(255),@table nvarchar(50), @sql nvarchar(255), @constraintpattern nvarchar(10)
   2: set @table = 'tb_Tips' 
   3: set @constraintpattern = '%categ%'
   4:  
   5: --Remove foreign key constraint for tb_Tips
   6: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
   7: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'FOREIGN KEY'
   8: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
   9: exec sp_executesql @sql
  10:  
  11: --Remove primary key constraint for table tb_Categories
  12: set @table = 'tb_Categories' 
  13: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
  14: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'PRIMARY KEY'
  15: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
  16: --DEBUG: select @sql
  17: exec sp_executesql @sql
  18:  
  19: -- Extend primary key field for table tb_Categories
  20: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null
  21:  
  22: -- Extend foreign key field for table tb_Tips
  23: ALTER TABLE tb_Tips ALTER COLUMN Category_ref varchar(50)
  24:  
  25: -- Add primary key for table tb_Categories
  26: ALTER TABLE tb_Categories WITH NOCHECK 
  27: ADD CONSTRAINT PK_CategoeryCode PRIMARY KEY CLUSTERED (Code)
  28: WITH (FILLFACTOR = 75, PAD_INDEX = ON)
  29:  
  30: -- Add foreign key for table tb_Tips
  31: ALTER TABLE tb_Tips WITH NOCHECK 
  32: ADD CONSTRAINT FK_CategoryCode FOREIGN KEY (Category_ref)
  33:    REFERENCES tb_Categories (Code)

Note 1: I query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system table to retrieve the EXACT name of the constraint. Since I need to run this script also on other database servers, the script should be figure out the name itself.

Note 2 / Note to self: next time always use a integer column with auto increment.

I came across the website of Jon Galloway, which has a brilliant T-SQL script to remove all constraints from a single table (a bit too much for me in the current situation):

   1: -- t-sql scriptlet to drop all constraints on a table
   2: DECLARE @database nvarchar(50)
   3: DECLARE @table nvarchar(50)
   4:  
   5: set @database = 'dotnetnuke'
   6: set @table = 'tabs'
   7:  
   8: DECLARE @sql nvarchar(255)
   9: WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
  10: BEGIN
  11:     select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
  12:     from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
  13:     where    constraint_catalog = @database and 
  14:             table_name = @table
  15:     exec    sp_executesql @sql
  16: END

 

Easy way to clear your SQL server database

Sometimes you want to make a clean start with your SQL server database since it’s filled up with old demo data. We all now that you can delete or truncate your tables easily by executing some T-SQL statements. When you only have a couple of tables and no complex foreign key dependencies you can do it by hand. When you database is larger and for more complex it can be a time consuming task and it’s different for each database schema.

The following script makes it really easy (beware: select the correct database!).

 1: ------------------------------------------------------------------------------------
 2: ---- NOTE: !!! DOUBLE CHECK SELECTED DATABASE !!!! ----
 3: ---- FIRST TRY RUNNING WITH TRANSACTIONS ON, LATER REPLACE ROLLBACK WITH COMMIT ----
 4: ------------------------------------------------------------------------------------
 5: begin transaction
 6:  
 7: -- disable all constraints
 8: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 9:  
 10: -- delete data in all tables
 11: EXEC sp_MSForEachTable "DELETE FROM ?"
 12:  
 13: -- enable all constraints
 14: exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
 15:  
 16: -- some of the tables have identity columns we may want to reseed them
 17: EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"
 18:  
 19: rollback transaction
 20: --commit transaction

Note: I have the code surrounded by a transaction that performs a rollback by default. Just to make sure that you don´t run this script accidentally on the wrong database. You can download the script by using the link below

clear_all_database_tables.sql (1.54 kb)  

Hope this can save some time for you too,