.::: Reindex, Reorganize and Rebuild Indexes & Update statistics in SQL Server base on Fragmentation :::.

1. Check Database_ID (contoh database id 5 & 6 )
select @@Servername as ServerName,name as dbname,database_id  from sys.databases;
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'teguhth';
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'AdventureWorks2019';
 

2. check fragmentation above > 30
SELECT @@servername as ServerName,db_name() as DBName,getdate() as DateCollect,S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
'Alter Index ' +I.name+ ' on ' +
S.name + '.' +
T.name + ' Rebuild' as QueryReindex,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 30
ORDER BY DDIPS.avg_fragmentation_in_percent desc
 

3. check fragmentation above > 30 for sql 2008 below
SELECT @@servername as ServerName,db_name() as DBName,getdate() as DateCollect S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
'Alter Index ' +I.name+ ' on ' +
S.name + '.' +
T.name + ' Rebuild' as QueryReindex,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (6, NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 30
ORDER BY DDIPS.avg_fragmentation_in_percent desc
 
4. check fragmentation above > 30 with filter table example table 'Employee'

SELECT @@servername as ServerName,db_name() as DBName,getdate() as DateCollect,S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
'Alter Index ' +I.name+ ' on ' +
S.name + '.' +
T.name + ' Rebuild' as QueryReindex,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (6, NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
and T.name ='Employee'
AND DDIPS.avg_fragmentation_in_percent > 30
ORDER BY DDIPS.avg_fragmentation_in_percent desc
 

5. run fragmentation from result no 2
Alter Index AK_SalesOrderDetailEnlarged_rowguid on Sales.SalesOrderDetailEnlarged Rebuild
Alter Index AK_SalesOrderHeaderEnlarged_rowguid on Sales.SalesOrderHeaderEnlarged Rebuild
Alter Index IX_SalesOrderHeaderEnlarged_CustomerID on Sales.SalesOrderHeaderEnlarged Rebuild
Alter Index IX_SalesOrderDetailEnlarged_ProductID on Sales.SalesOrderDetailEnlarged Rebuild
Alter Index PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID on Sales.SalesOrderDetailEnlarged Rebuild
Alter Index AK_Employee_NationalIDNumber on HumanResources.Employee Rebuild
Alter Index PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID on HumanResources.EmployeeDepartmentHistory Rebuild
Alter Index PK_EmployeePayHistory_BusinessEntityID_RateChangeDate on HumanResources.EmployeePayHistory Rebuild
Alter Index PK_SalesPersonQuotaHistory_BusinessEntityID_QuotaDate on Sales.SalesPersonQuotaHistory Rebuild
Alter Index AK_Product_ProductNumber on Production.Product Rebuild
Alter Index AK_Product_rowguid on Production.Product Rebuild
Alter Index AK_SpecialOfferProduct_rowguid on Sales.SpecialOfferProduct Rebuild
Alter Index PK_StateProvince_StateProvinceID on Person.StateProvince Rebuild
Alter Index IX_Store_SalesPersonID on Sales.Store Rebuild
Alter Index PK_ProductProductPhoto_ProductID_ProductPhotoID on Production.ProductProductPhoto Rebuild
Alter Index PK_ProductReview_ProductReviewID on Production.ProductReview Rebuild
Alter Index IX_ProductVendor_UnitMeasureCode on Purchasing.ProductVendor Rebuild
Alter Index PK_Vendor_BusinessEntityID on Purchasing.Vendor Rebuild
Alter Index PK_CountryRegion_CountryRegionCode on Person.CountryRegion Rebuild
Alter Index AK_CountryRegion_Name on Person.CountryRegion Rebuild
Alter Index PK_ProductListPriceHistory_ProductID_StartDate on Production.ProductListPriceHistory Rebuild
Alter Index PK_SpecialOfferProduct_SpecialOfferID_ProductID on Sales.SpecialOfferProduct Rebuild
Alter Index AK_Store_rowguid on Sales.Store Rebuild
Alter Index PK_ProductCostHistory_ProductID_StartDate on Production.ProductCostHistory Rebuild
Alter Index AK_ProductDescription_rowguid on Production.ProductDescription Rebuild
Alter Index IX_BusinessEntityContact_PersonID on Person.BusinessEntityContact Rebuild
Alter Index IX_BusinessEntityContact_ContactTypeID on Person.BusinessEntityContact Rebuild
Alter Index IX_ProductReview_ProductID_Name on Production.ProductReview Rebuild
Alter Index AK_Employee_LoginID on HumanResources.Employee Rebuild
 

6. Update statistic
use AdventureWorks2019
go
sp_updatestats
go
 

7. check fragmentation after rebuil index
SELECT @@servername as ServerName,db_name() as DBName,getdate() as DateCollect,S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
'Alter Index ' +I.name+ ' on ' +
S.name + '.' +
T.name + ' Rebuild' as QueryReindex,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 30
ORDER BY DDIPS.avg_fragmentation_in_percent desc