DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to generate index fragmentation report for all the indexes?

The following Microsoft SQL Server T-SQL queries will generate index fragmentation report for SQL Server 2005 / 2008 and SQL Server 2000 versions.

-- Index fragmentation in a database - QUICK SYNTAX

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

------------

USE DatabaseName;

-- SQL Server 2005/2008 Index Fragmentation Report

-- SQL Server DMV - Dynamic Management View

SELECT schema_name(t.schema_id)                           AS [Schema],

       object_name(ps.object_id)                          AS [Table],

       i.name                                             AS [Index],

       ps.Index_type_desc                                 AS IndexType,

       convert(TINYINT,ps.avg_fragmentation_in_percent)   AS [AvgFrag%],

       convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],

       ps.record_count                                    AS RecordCnt,

       ps.fragment_count                                  AS FragmentCnt

FROM     sys.dm_db_index_physical_stats(db_id(db_name()),

             NULL,NULL,NULL,'DETAILED') ps -- Faster option: SAMPLED

         INNER JOIN sys.indexes i

           ON ps.object_id = i.object_id

              AND ps.index_id = i.index_id

         INNER JOIN sys.tables

           ON ps.object_id = t.object_id

WHERE    t.is_ms_shipped = 0

ORDER BY [Schema],  [Table],  [Index]

/*

Schema Table  Index IndexType   AvgFrag%    AvgSpaceUsed%     RecordCnt FragmentCnt

dbo   TranHist  PK_ID_TRANHIST  CLUSTERED INDEX   52    73    484437      191

....*/
------------

-- SQL Server 2005 and on fragmentation queries based on sys.dm_db_index_physical_stats

USE AdventureWorks2008;

GO

-- DETAILED fragmentation info on table

SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),

       * FROM sys.dm_db_index_physical_stats

       (DB_ID(N'AdventureWorks2008'),

        OBJECT_ID(N'Sales.SalesOrderHeaderSalesReason'), NULL, NULL , 'DETAILED');

GO

-- Fragmentation info on table

SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),

       * FROM sys.dm_db_index_physical_stats

       (DB_ID(N'AdventureWorks2008'),

        OBJECT_ID(N'Sales.SalesOrderHeaderSalesReason'), NULL, NULL , NULL);

GO

-- All fragmentation info

SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),

       * FROM sys.dm_db_index_physical_stats

       (DB_ID(N'AdventureWorks2008'),

        NULL, NULL, NULL , NULL);

GO

------------

 

-- SQL Server 2000 index fragmentation for all indexes of a table

use AdventureWorks2008;

dbcc showcontig('Sales.SalesOrderDetail') with all_indexes

/*

....

DBCC SHOWCONTIG scanning 'SalesOrderDetail' table...

Table: 'SalesOrderDetail' (898102240); index ID: 3, database ID: 7

LEAF level scan performed.

- Pages Scanned................................: 227

- Extents Scanned..............................: 31

- Extent Switches..............................: 30

- Avg. Pages per Extent........................: 7.3

- Scan Density [Best Count:Actual Count].......: 93.55% [29:31]

- Logical Scan Fragmentation ..................: 3.08%

- Extent Scan Fragmentation ...................: 25.81%

- Avg. Bytes Free per Page.....................: 79.5

- Avg. Page Density (full).....................: 99.02%

...

*/

------------

------------

-- SQL Server 2000 version - DBCC SHOWCONTIG for index fragmentation

------------

USE Northwind;

 

SELECT   'DBCC SHOWCONTIG (' + convert(VARCHAR,o.id) + ',' +

convert(VARCHAR,i.indid) + ') -- ' + object_name(o.id) + -- table name 

          '.' + i.name -- index name 

FROM     sysobjects o

         JOIN sysindexes i

           ON (o.id = i.id)

WHERE    o.xtype = 'U'

         AND i.rows > 0

ORDER BY object_name(o.id),

         i.indid

 

GO

 

/* Partial results

 

(No column name)

DBCC SHOWCONTIG (21575115,1) -- Categories.PK_Categories

DBCC SHOWCONTIG (21575115,2) -- Categories.CategoryName

DBCC SHOWCONTIG (53575229,1) -- Customers.PK_Customers

DBCC SHOWCONTIG (53575229,2) -- Customers.City

DBCC SHOWCONTIG (53575229,3) -- Customers.CompanyName

DBCC SHOWCONTIG (53575229,4) -- Customers.PostalCode

*/

------------

-- Index rebuild with SQLMAINT - SQL Server 2000 - 100% FILLFACTOR

exec xp_sqlmaint '-D pubs -RebldIdx 100'

------------

Related Link on REBUILD indexes:

http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

Related article:

Are you using SQL's Missing Index DMVs?

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE