Sunday, November 13, 2011

Working with SQL Server fragmentation

use AdventureWorks2008R2;
go
select * from
(select * from sys.dm_db_index_physical_stats(db_id('AdventureWorks2008R2'),null,null,null,null)) t
where t.avg_fragmentation_in_percent> 30
GO

use AdventureWorks2008R2;
go
select * from sys.dm_db_index_physical_stats(db_id(),414624520,null,null,null)
GO


select * from sys.indexes where object_id=414624520 and index_id=1;
select * from sys.tables where object_id=414624520;
select * from sys.schemas where schema_id=9;

alter index PK_SpecialOfferProduct_SpecialOfferID_ProductID on Sales.SpecialOfferProduct rebuild with (ONLINE=on);
GO