CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33974

Will Rebuilding Indexes stop my production database to work?

I was using SQL Server 2012 as my production since more than 2 years. In this period, I added some indexes but never touch fragmentation. Honestly, I was unaware about index fragmentation. But Today When I ran,

SELECT 
    dbschemas.[name] as 'Schema', 
    dbtables.[name] as 'Table', 
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN 
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN 
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN 
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                             AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
ORDER BY 
    indexstats.avg_fragmentation_in_percent DESC

I got these results:

Schema  Table                           Index                           avg_fragmentation_in_percent    page_count
--------------------------------------------------------------------------------------------------------------
dbo     ELMAH_Error                 IX_ELMAH_Error_App_Time_Seq         99.2438563327032                529
dbo     ELMAH_Error                 PK_ELMAH_Error                      98.8664987405542                794
dbo     ProductsCountriesVariants   PK__Products__3214EC270AC6A076      98                              100
dbo     Tags                        PK_Tags                             91.2820512820513                195
dbo     ProductGallery              PK_ProductGallery                   89.0909090909091                220
dbo     devicecatalog               IX_DeviceCatalog_NativeDeviceID     88.5964912280702                342
dbo     Products                    IX_Products_RetailerID_ModelNumber  88.5245901639344                122
dbo     Products                    IX_Products_RetailerID_SKU          82.7272727272727                110
--------------------------------------------------------------------------------------------------------------

Should I rebuild all indexes? Will it effect my database running on production? The interesting thing is that I have just created IX_DeviceCatalog_NativeDeviceID index few minutes ago.

Upvotes: 3

Views: 5157

Answers (2)

Steve
Steve

Reputation: 5545

You can also see if a reorganize will help until you can do a full rebuild. You need to do more research but basically, reorganize as often as needed to keep your fragmentation under 20-30% until you can rebuild it during off-hours. Ignore anything with less than 15-20 pages because the fragmentation is not reliable.

Indexes with a NULL name are temporary indexes the system creates until the service is restarted.

Upvotes: 1

DavidG
DavidG

Reputation: 118957

Rebuilding an index will lock the table unless you specify that the operation is done online:

ALTER INDEX REBUILD WITH (ONLINE = ON)

That is only available in Enterprise version and above.

Upvotes: 3

Related Questions