SHARE
TWEET

5 Put Data in New Table

NEERAJPRASADSHARMA Jul 20th, 2017 30 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
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
 
Top