1. Disk defragmentation

We will create the disk defragmentation maintenance job under control of the Sql Servers scheduling service.  This way we only have one place to look and we are not depending on any other scheduling services, who can be stopped by anyone. (If anyone stops the Sql Server Agent service he/she gets some other problems :-)

So, connect to your sql server, go to Maintenance Plans and create a New Maintenance Plan, give it a Name, ex. "Defrag-Cleanup-MaintenancePlan".

Double click Subplan_1, name it ex. "Disk Defrag", write a short description and choose a Schedule. Look for other jobs and plans in Sql Manager, in the Windows Scheduler or maybe a scheduled backup program, so you dont begin your defragmentation in the middle of something else. I have choosen a schedule every second week on a sunday. Save the new subplan (Ctrl-S).

Now, if you go to SQL Server Agent -> Jobs you will see that there is a new job, "Defrag-Cleanup-Maintenanceplan.Disk Defrag". Open it and repeat the following for each disk you want to defragmentate:

1. Click on "Steps" and click on "Edit" for the first disk and "New" for the rest.
2. In "Step name" write a name, ex. "Defrag c:"
3. In "Type" choose "Operating system (CmdExec)".
4. In the "Command" window remove anything and write: "defrag [disk]: /f", where [disk] is the disk you want defragmentate.
5. Click "Ok".

After you have created all the steps, it is now time to set up what should happen if one of the steps fails. This could not be done while we created the steps, so we must go through them all again. Edit step 1, and select the Advanced tab.

1. In "On succes action", choose "Go to next step". If this is the last step choose "Quit the job reporting success"
2. In "Retry attempts" choose 1.
3. In "Retry interval" choose, ex. 15 minutes, based on your system.
4. In "On failure action", choose "Quit the job reporting failure".
5. Select to "Include step output in history".
6. Click "Ok".

After the steps for each disk has been created click "Ok" to save the Job. It is now ready to test. Remember that running a disk defragmentation can slow down the users response time. And, something i have experienced, is that if you have the Disk Defragmentation program open (also in the Computer Manager) then the job will fail. Just close them and everything works fine.

2. Index defragmentation

To see how much all your indexes in each database are fragmentet, you can run this script (Code 2). The script can be set to execute a defragmentation of all indexes greater than ex. 30%.

The output from the above code is something like this, where 2 indexes are more than 30% fragmentet:

WOULD be executing ALTER INDEX spt_valuesclust ON spt_values REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently 30%
WOULD be executing ALTER INDEX ix2_spt_values_nu_nc ON spt_values REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently 33%

If you want to see many details of all indexes in one database, you can use this script (Code 3).

Now we will create a Index defragmentattion job under our Defrag-Cleanup-MaintenancePlan.

1. Open the Defrag-Cleanup-MaintenancePlan, click Add Subplan, name it ex. 'Index Defrag', write some description and choose a Schedule. Look for other jobs and plans in Sql Manager, in the Windows Scheduler or maybe a scheduled backup program, so you dont begin your defragmentation in the middle of something else. I have choosen a schedule every week on a sunday at 2.00 AM. Save the new subplan (Ctrl-S).
2. Now the big question is: Do we want to Rebuild or Reorganize the Index? For a technical explanation see BOL or a good article here.Rebuilding an Index is generally more effective that Reorganizing, but it takes more resources and can give locking while it is running. So in this case we choose a Reorganization of our indexes. Drag a 'Reorganize Index Task' box from the left pane to the subplan area, double-click it and select 'All user databases'. Click 'Ok' and save it, Ctrl + S.

That's it! You can test it by right-clicking on the job in Sql Server Agent->Jobs list and click on Start Job at Step...

Code 2. Index defragmentation code

The following code is for Sql server 2005 + 2008.
The variable @ViewOnly is set to 1 for viewing all indexes that is fragmentet more than what the variable @maxfrag is set to. In this case the viables are set to View and 30%.

If you want to defragmentate, set @ViewOnly to 0 (null)

USE [databasename] -- enter the name of your database to test
GO
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @IdxName varchar(128)
DECLARE @ViewOnly bit

-- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:
SET @ViewOnly=1

-- Decide on the maximum fragmentation to allow for.
SET @maxfrag = 30.0

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))+'.'+CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the temporary table.
if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%')
drop table #fraglist

CREATE TABLE #fraglist (
    ObjectName CHAR(255),
    ObjectId INT,
    IndexName CHAR(255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity decimal,
    BestCount INT,
    ActualCount INT,
    LogicalFrag decimal,
    ExtentFrag decimal)

-- Open the cursor.
OPEN tables

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@ViewOnly=1)
        BEGIN
        PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
        END
    ELSE
BEGIN
    PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
    SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
    EXEC (@execstr)
END

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table.
DROP TABLE #fraglist
GO
 

Code 3. Index defragmentation code for one database.

To view details about all indexes in one database you can use the following code.

USE databasename;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
GO

There are many informations to get here, for details see the Books On Line. One of them is the last one, ExtentFragmentation, which shows the fragmentation in percent.

Also, from this article, i have found another way to check all indexes in one database.

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('databasename'), NULL, NULL, NULL , NULL);

Look under avg_fragmentation_in_percent.