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,

SSD performance loss

Recently my laptop is getting slower and slower. The startup is pretty fast, but when using Visual Studio and compiling code it slowwww. First I thought that I needed to clean up Windows and check VS extensions, but after a while my SSD drive was my main suspect. After running some tests on my SSD with HD Tune my feeling got confirmed:

64gb_work_ssd

It’s fine when reading data (roughly 190 MB/s average). So that’s what I see when booting up Windows. Writing data is a different story. It tries to be fast, but something is bothering, look at the spikes.. That’s probably causing VS to be slow and my general experience with the machine.

My 64 gig SSD is from the manufacturer DELL and one of the first generation. With another tool I checked if my drive supports the important TRIM command. Luckily it does. Also I checked that there is enough space on the disk (at least 10%) and that’s also not the problem.

ssd128new

Last weekend I bought a brand new SSD from Samsung, 128 GB in size. The 830 series comes in a nice retail package and a free copy of Norton Ghost. Very useful when you want to upgrade your system with this new disk.

 

Time for a comparison. It’s a bit apple and oranges, since the age difference of SSD is like 2 years. And in SSD terms that’s a lot.

 

Here is the HD Tune benchmark for the new Samsung 830 – 128 GB:

128gb_private_ssd

That’s better. Read & write is much higher of course. My point is that write is a flat line when you compare it with my older SSD.

Just for fun, my WD Velicoraptor is showing me this:

WDC_RAPTOR

 

 

 

 

 

 

 

 

 

It’s OK for a old-fashioned disk, compared with my new SSD this disk really is a dinosaur. Probably I could try to low level format my 64 GB SSD to recover some speed that it used to have. First I need to move the data to another disk.