4 Any Problem With Rebuild

----- number of outof order pages
begin tran
drop table [DBO].FRAG3

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )



INSERT INTO [DBO].FRAG3
SELECT
n ,

'Some text..'

FROM Numbers

Where N/2 = N/2.0







ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

--500000
Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

commit

SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count
FROM sys.dm_db_index_physical_stats
(db_id('TutorialSQLServer'), object_id ('FRAG3' ), 1 ,null,'DETAILED')


SELECT
OutOfOrderPages =Count_Big(*)

FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.FRAG3'), 1, NULL, 'DETAILED')
WHERE is_allocated = 1 and page_level=0 and page_type!=10 and
(previous_page_page_id > allocated_page_page_id  or allocated_page_page_id >next_page_page_id)
 --71429 out of order pages out of 107143

 Alter table FRAG3 Rebuild


SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count
FROM sys.dm_db_index_physical_stats
(db_id('TutorialSQLServer'), object_id ('FRAG3' ), 1 ,null,'DETAILED')


SELECT
OutOfOrderPages =Count_Big(*)

FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.FRAG3'), 1, NULL, 'DETAILED')
WHERE is_allocated = 1 and page_level=0 and page_type!=10 and
(previous_page_page_id > allocated_page_page_id  or allocated_page_page_id >next_page_page_id)
 --8 out of order pages out of 71430


----- END number of outof order pages