NEERAJPRASADSHARMA

4 Any Problem With Rebuild

Jul 20th, 2017
46
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ----- number of outof order pages
  2. begin tran
  3. drop table [DBO].FRAG3
  4.  
  5. CREATE TABLE [DBO].FRAG3 (
  6. Primarykey int NOT NULL ,
  7. SomeData3 char(1000) NOT NULL )
  8.  
  9.  
  10.  
  11. INSERT INTO [DBO].FRAG3
  12. SELECT
  13. n ,
  14.  
  15. 'Some text..'
  16.  
  17. FROM Numbers
  18.  
  19. Where N/2 = N/2.0
  20.  
  21.  
  22.  
  23.  
  24.  
  25.  
  26.  
  27. ALTER TABLE DBO.FRAG3
  28. ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)
  29.  
  30. --500000
  31. Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
  32. Where  Primarykey>500001
  33.  
  34. commit
  35.  
  36. SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_id,index_type_desc,index_level,
  37. avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count
  38. FROM sys.dm_db_index_physical_stats
  39. (db_id('TutorialSQLServer'), object_id ('FRAG3' ), 1 ,null,'DETAILED')
  40.  
  41.  
  42. SELECT
  43. OutOfOrderPages =Count_Big(*)
  44.  
  45. FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.FRAG3'), 1, NULL, 'DETAILED')
  46. WHERE is_allocated = 1 and page_level=0 and page_type!=10 and
  47. (previous_page_page_id > allocated_page_page_id  or allocated_page_page_id >next_page_page_id)
  48.  --71429 out of order pages out of 107143
  49.  
  50.  Alter table FRAG3 Rebuild
  51.  
  52.  
  53. SELECT OBJECT_NAME(OBJECT_ID) Table_Name, index_id,index_type_desc,index_level,
  54. avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count
  55. FROM sys.dm_db_index_physical_stats
  56. (db_id('TutorialSQLServer'), object_id ('FRAG3' ), 1 ,null,'DETAILED')
  57.  
  58.  
  59. SELECT
  60. OutOfOrderPages =Count_Big(*)
  61.  
  62. FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.FRAG3'), 1, NULL, 'DETAILED')
  63. WHERE is_allocated = 1 and page_level=0 and page_type!=10 and
  64. (previous_page_page_id > allocated_page_page_id  or allocated_page_page_id >next_page_page_id)
  65.  --8 out of order pages out of 71430
  66.  
  67.  
  68. ----- END number of outof order pages
RAW Paste Data