SHARE
TWEET

4 Any Problem With Rebuild

NEERAJPRASADSHARMA Jul 20th, 2017 33 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top