NEERAJPRASADSHARMA

5 Put Data in New Table

Jul 20th, 2017
50
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3.  
  4.  
  5.  
  6. ------------------------- BEGIN: Put FRAG3 data into new table
  7.  
  8. BEGIN TRAN
  9.  
  10. CREATE TABLE [DBO].[CopyOfFRAG3] (
  11. Primarykey int NOT NULL ,
  12. SomeData3 char(1000) NOT NULL )
  13.  
  14.  
  15.  
  16. ALTER TABLE DBO.[CopyOfFRAG3]
  17. ADD CONSTRAINT PK_copyofFRAG3  PRIMARY KEY (Primarykey)
  18.  
  19.  
  20.  
  21.  
  22. insert into  DBO.[CopyOfFRAG3]
  23. select * from [DBO].FRAG3
  24. COMMIT
  25.  
  26. ------------------------- END: Put FRAG3 data into new table
  27.  
  28.  
  29.  
  30. ----- BEGIN GET FRAGMENTATION INFORMATION
  31. EXEC GETINDEXINFO 'FRAG3'
  32. ----- END GET FRAGMENTATION INFORMATION
  33.  
  34.  
  35. ----- BEGIN GET FRAGMENTATION INFORMATION
  36. EXEC GETINDEXINFO 'copyofFRAG3'
  37. ----- END GET FRAGMENTATION INFORMATION
  38.  
  39.  
  40.  
  41.  
  42.  
  43. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  44. EXEC CleanBufferAndWaitBeforeTest
  45. SET STATISTICS TIME ON
  46. Select Count_Big (*) From [DBO].copyofFRAG3 Where Primarykey >0 Option (MAxDop 1 )
  47. SET STATISTICS TIME OFF
  48. EXEC GetStatINFO
  49. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  50.  
  51.  
  52.  
  53.  
  54. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  55. EXEC CleanBufferAndWaitBeforeTest
  56. SET STATISTICS TIME ON
  57. Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
  58. SET STATISTICS TIME OFF
  59. EXEC GetStatINFO
  60. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  61.  
  62.  
  63.  
  64.  
  65.  
  66.  
  67.  
  68.  
  69.  
  70. ---- collect Perfmon counter
  71. while  (1=1)
  72. begin
  73.  
  74. CheckPoint;
  75. Dbcc DropCleanBuffers;
  76. Select Count_Big (*) From [DBO].FRAG3 Where Primarykey >0 Option (MAxDop 1 )
  77. --- stop perfon after 10 sec
  78. --Disk Read Bytes/Sec (AVG)40656973
  79. end
  80.  
  81.  
  82.  
  83. ---- Perfmon counter collected
  84. while  (1=1)
  85. begin
  86.  
  87. CheckPoint;
  88. Dbcc DropCleanBuffers;
  89. Select Count_Big (*) From [DBO].copyofFRAG3 Where Primarykey >0 Option (MAxDop 1 )
  90. --- stop perfon after 10 sec
  91. -- Disk Read Bytes/Sec (AVG)93310882
  92. end
  93.  
  94.  
  95.  
  96.  
  97. ------------------------- END FRAG3 data into new table
RAW Paste Data