How to determine Index fragmentation and then defragment a database

Index fragmentation is always an issue with big data. I have written some articles before:
How to rebuild all indexes
How to script all indexes
How to examine index fragmentation in SQL Server 2012

The easy way:

SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
, OBJECT_NAME(DPS.OBJECT_ID) AS TableName
, SI.NAME AS IndexName
, DPS.INDEX_TYPE_DESC AS IndexType
, DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
, DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC

As a rule, all indexes with a fragmentation state over 30% should be defragmented:

— Index neu erstellen
EXEC sp_MSforeachtable @command1=”print ‘Erstelle Index für ?’ ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)”
GO

— Index reorganisieren
EXEC sp_MSforeachtable @command1=”print ‘Reorgansiere Index für ?’ ALTER INDEX ALL ON ? REORGANIZE”
GO

Advertisements

SQL Server Azure Index Defragmentation and performance tuning

IndexFragmentation

Reorganising and rebuilding indexes is crucial to maintaining database performance; however on SQL Azure indexes are much less transparent & the standard “DBCC DBREINDEX” approach isn’t supported on the platform.

However; Chris Pietschmann has posted an article on rebuilding all the indexes in a database here.  The operation to rebuild an index is:

ALTER INDEX ALL ON [dbo].[MyTable] REBUILD

In order to do this for all indexes within the database; you can use the below script (This is a modified version of Chris’s which supports tables that are on different schemas).

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
(
SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
FROM INFORMATION_SCHEMA.TABLES IST
WHERE IST.TABLE_TYPE = 'BASE TABLE'
)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT('Rebuilding Indexes on ' + @TableName)
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor