API tools faq
paste
NEERAJPRASADSHARMA

3 Test

NEERAJPRASADSHARMA
Jul 20th, 2017
290
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 10.85 KB | None | 0 0
raw download clone embed print report
  1.  
  2.  
  3.  
  4.  
  5. SELECT @@VERSION
  6.  
  7. /*                                                       BEGIN PART FRAG1.1     */
  8.  
  9.  
  10. ----- BEGIN GET FRAGMENTATION INFORMATION
  11. EXEC GETINDEXINFO 'FRAG1'
  12. GO
  13. -----  END GET FRAGMENTATION INFORMATION
  14.  
  15.  
  16. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  17. EXEC CleanBufferAndWaitBeforeTest
  18. SET STATISTICS TIME ON
  19. Select Count_Big (*) From [DBO].[FRAG1] Where Primarykey >0 Option (MAxDop 1)
  20. SET STATISTICS TIME OFF
  21. EXEC GetStatINFO
  22. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  23.  
  24. -----END PART FRAG1.1
  25.  
  26.  
  27.  
  28. -------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN
  29.  
  30. --BEGIN Rebuild the Index
  31. Alter Table [DBO].[FRAG1] REBUILD
  32. --END  Rebuild the Index
  33.  
  34.  
  35. ----- BEGIN GET FRAGMENTATION INFORMATION
  36. EXEC GETINDEXINFO 'FRAG1'
  37. ----- END GET FRAGMENTATION INFORMATION
  38.  
  39.  
  40.  
  41. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  42. EXEC CleanBufferAndWaitBeforeTest
  43. SET STATISTICS TIME ON
  44. Select Count_Big (*) From [DBO].[FRAG1] Where Primarykey >0 Option (MAxDop 1)
  45. SET STATISTICS TIME OFF
  46. EXEC GetStatINFO
  47. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  48.  
  49.  
  50. -------------END PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN
  51.  
  52.  
  53. ----------------------BEGIN PART FRAG1.3: REORGANIZE THE INDEX AND TEST AGAIN
  54.  
  55.  
  56. --BEGIN REORGANIZE the Index
  57. ALTER INDEX ALL ON [FRAG1] REORGANIZE
  58. GO
  59. --END REORGANIZE the Index
  60.  
  61.  
  62. ----- BEGIN GET FRAGMENTATION INFORMATION
  63. EXEC GETINDEXINFO 'FRAG1'
  64. ----- END GET FRAGMENTATION INFORMATION
  65.  
  66.  
  67.  
  68. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  69. EXEC CleanBufferAndWaitBeforeTest
  70. SET STATISTICS TIME ON
  71. Select Count_Big (*) From [DBO].[FRAG1] Where Primarykey >0 Option (MAxDop 1)
  72. SET STATISTICS TIME OFF
  73. EXEC GetStatINFO
  74. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  75.  
  76.  
  77. ----------------------END PART FRAG1.3: REORGANIZE THE INDEX AND TEST AGAIN
  78.  
  79.  
  80.  
  81.  
  82. -------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN
  83.  
  84. --BEGIN Rebuild the Index
  85. Alter Table [DBO].[FRAG1] REBUILD
  86. --END  Rebuild the Index
  87.  
  88.  
  89. ----- BEGIN GET FRAGMENTATION INFORMATION
  90. EXEC GETINDEXINFO 'FRAG1'
  91. ----- END GET FRAGMENTATION INFORMATION
  92.  
  93.  
  94.  
  95. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  96. EXEC CleanBufferAndWaitBeforeTest
  97. SET STATISTICS TIME ON
  98. Select Count_Big (*) From [DBO].[FRAG1] Where Primarykey >0 Option (MAxDop 1)
  99. SET STATISTICS TIME OFF
  100. EXEC GetStatINFO
  101. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  102.  
  103.  
  104. -------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN
  105.  
  106. -------------END PART FRAG1  
  107.  
  108.  
  109.  
  110. -----------------------------------------------------------------------------------------------------------------------------------------------------------
  111.  
  112.  
  113. /*                                                       BEGIN PART FRAG2.2     */
  114.  
  115.  
  116. ----- BEGIN GET FRAGMENTATION INFORMATION
  117. EXEC GETINDEXINFO 'FRAG2'
  118. GO
  119. -----  END GET FRAGMENTATION INFORMATION
  120.  
  121.  
  122. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  123. EXEC CleanBufferAndWaitBeforeTest
  124. SET STATISTICS TIME ON
  125. Select Count_Big (*) From [DBO].[FRAG2] Where Primarykey >0 Option (MAxDop 1)
  126. SET STATISTICS TIME OFF
  127. EXEC GetStatINFO
  128. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  129.  
  130. -----END PART FRAG2.1
  131.  
  132.  
  133.  
  134. -------------BEGIN PART FRAG2.2: REBUILD THE INDEX AND TEST AGAIN
  135.  
  136. --BEGIN Rebuild the Index
  137. Alter Table [DBO].[FRAG2] REBUILD
  138. --END  Rebuild the Index
  139.  
  140.  
  141. ----- BEGIN GET FRAGMENTATION INFORMATION
  142. EXEC GETINDEXINFO 'FRAG2'
  143. ----- END GET FRAGMENTATION INFORMATION
  144.  
  145.  
  146.  
  147. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  148. EXEC CleanBufferAndWaitBeforeTest
  149. SET STATISTICS TIME ON
  150. Select Count_Big (*) From [DBO].[FRAG2] Where Primarykey >0 Option (MAxDop 1)
  151. SET STATISTICS TIME OFF
  152. EXEC GetStatINFO
  153. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  154.  
  155.  
  156. -------------END PART FRAG2.2: REBUILD THE INDEX AND TEST AGAIN
  157.  
  158.  
  159. ----------------------BEGIN PART FRAG2.3: REORGANIZE THE INDEX AND TEST AGAIN
  160.  
  161.  
  162. --BEGIN REORGANIZE the Index
  163. ALTER INDEX ALL ON [FRAG2] REORGANIZE
  164. GO
  165. --END REORGANIZE the Index
  166.  
  167.  
  168. ----- BEGIN GET FRAGMENTATION INFORMATION
  169. EXEC GETINDEXINFO 'FRAG2'
  170. ----- END GET FRAGMENTATION INFORMATION
  171.  
  172.  
  173.  
  174. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  175. EXEC CleanBufferAndWaitBeforeTest
  176. SET STATISTICS TIME ON
  177. Select Count_Big (*) From [DBO].[FRAG2] Where Primarykey >0 Option (MAxDop 1)
  178. SET STATISTICS TIME OFF
  179. EXEC GetStatINFO
  180. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  181.  
  182.  
  183. ----------------------END PART FRAG2.3: REORGANIZE THE INDEX AND TEST AGAIN
  184.  
  185.  
  186.  
  187.  
  188. -------------BEGIN PART FRAG2.4: REBUILD THE INDEX AND TEST AGAIN
  189.  
  190. --BEGIN Rebuild the Index
  191. Alter Table [DBO].[FRAG2] REBUILD
  192. --END  Rebuild the Index
  193.  
  194.  
  195. ----- BEGIN GET FRAGMENTATION INFORMATION
  196. EXEC GETINDEXINFO 'FRAG2'
  197. ----- END GET FRAGMENTATION INFORMATION
  198.  
  199.  
  200.  
  201. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  202. EXEC CleanBufferAndWaitBeforeTest
  203. SET STATISTICS TIME ON
  204. Select Count_Big (*) From [DBO].[FRAG2] Where Primarykey >0 Option (MAxDop 1)
  205. SET STATISTICS TIME OFF
  206. EXEC GetStatINFO
  207. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  208.  
  209.  
  210. -------------END PART FRAG2.4: REBUILD THE INDEX AND TEST AGAIN
  211.  
  212. -------------END PART FRAG2  
  213.  
  214.  
  215.  
  216.  
  217.  
  218.  
  219. -----------------------------------------------------------------------------------------------------------------------------------------------------------
  220.  
  221.  
  222. /*                                                       BEGIN PART FRAG3.2     */
  223.  
  224.  
  225. ----- BEGIN GET FRAGMENTATION INFORMATION
  226. EXEC GETINDEXINFO 'FRAG3'
  227. GO
  228. -----  END GET FRAGMENTATION INFORMATION
  229.  
  230.  
  231. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  232. EXEC CleanBufferAndWaitBeforeTest
  233. SET STATISTICS TIME ON
  234. Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
  235. SET STATISTICS TIME OFF
  236. EXEC GetStatINFO
  237. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  238.  
  239. -----END PART FRAG3.1
  240.  
  241.  
  242.  
  243. -------------BEGIN PART FRAG3.2: REBUILD THE INDEX AND TEST AGAIN
  244.  
  245. --BEGIN Rebuild the Index
  246. Alter Table [DBO].[FRAG3] REBUILD
  247. --END  Rebuild the Index
  248.  
  249.  
  250. ----- BEGIN GET FRAGMENTATION INFORMATION
  251. EXEC GETINDEXINFO 'FRAG3'
  252. ----- END GET FRAGMENTATION INFORMATION
  253.  
  254.  
  255.  
  256. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  257. EXEC CleanBufferAndWaitBeforeTest
  258. SET STATISTICS TIME ON
  259. Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
  260. SET STATISTICS TIME OFF
  261. EXEC GetStatINFO
  262. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  263.  
  264.  
  265. -------------END PART FRAG3.2: REBUILD THE INDEX AND TEST AGAIN
  266.  
  267.  
  268. ----------------------BEGIN PART FRAG3.3: REORGANIZE THE INDEX AND TEST AGAIN
  269.  
  270.  
  271. --BEGIN REORGANIZE the Index
  272. ALTER INDEX ALL ON [FRAG3] REORGANIZE
  273. GO
  274. --END REORGANIZE the Index
  275.  
  276.  
  277. ----- BEGIN GET FRAGMENTATION INFORMATION
  278. EXEC GETINDEXINFO 'FRAG3'
  279. ----- END GET FRAGMENTATION INFORMATION
  280.  
  281.  
  282.  
  283. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  284. EXEC CleanBufferAndWaitBeforeTest
  285. SET STATISTICS TIME ON
  286. Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
  287. SET STATISTICS TIME OFF
  288. EXEC GetStatINFO
  289. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  290.  
  291.  
  292. ----------------------END PART FRAG3.3: REORGANIZE THE INDEX AND TEST AGAIN
  293.  
  294.  
  295.  
  296.  
  297. -------------BEGIN PART FRAG3.4: REBUILD THE INDEX AND TEST AGAIN
  298.  
  299. --BEGIN Rebuild the Index
  300. Alter Table [DBO].[FRAG3] REBUILD
  301. --END  Rebuild the Index
  302.  
  303.  
  304. ----- BEGIN GET FRAGMENTATION INFORMATION
  305. EXEC GETINDEXINFO 'FRAG3'
  306. ----- END GET FRAGMENTATION INFORMATION
  307.  
  308.  
  309.  
  310. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  311. EXEC CleanBufferAndWaitBeforeTest
  312. SET STATISTICS TIME ON
  313. Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
  314. SET STATISTICS TIME OFF
  315. EXEC GetStatINFO
  316. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  317.  
  318.  
  319. -------------END PART FRAG3.4: REBUILD THE INDEX AND TEST AGAIN
  320.  
  321. -------------END PART FRAG3  
  322.  
  323.  
  324.  
  325. -----------------------------------------------------------------------------------------------------------------------------------------------------------
  326.  
  327.  
  328. /*                                                       BEGIN PART FRAG4.2     */
  329.  
  330.  
  331. ----- BEGIN GET FRAGMENTATION INFORMATION
  332. EXEC GETINDEXINFO 'FRAG4'
  333. GO
  334. -----  END GET FRAGMENTATION INFORMATION
  335.  
  336.  
  337. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  338. EXEC CleanBufferAndWaitBeforeTest
  339. SET STATISTICS TIME ON
  340. Select Count_Big (*) From [DBO].[FRAG4] Where Primarykey >0 Option (MAxDop 1)
  341. SET STATISTICS TIME OFF
  342. EXEC GetStatINFO
  343. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  344.  
  345. -----END PART FRAG4.1
  346.  
  347.  
  348.  
  349. -------------BEGIN PART FRAG4.2: REBUILD THE INDEX AND TEST AGAIN
  350.  
  351. --BEGIN Rebuild the Index
  352. Alter Table [DBO].[FRAG4] REBUILD
  353. --END  Rebuild the Index
  354.  
  355.  
  356. ----- BEGIN GET FRAGMENTATION INFORMATION
  357. EXEC GETINDEXINFO 'FRAG4'
  358. ----- END GET FRAGMENTATION INFORMATION
  359.  
  360.  
  361.  
  362. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  363. EXEC CleanBufferAndWaitBeforeTest
  364. SET STATISTICS TIME ON
  365. Select Count_Big (*) From [DBO].[FRAG4] Where Primarykey >0 Option (MAxDop 1)
  366. SET STATISTICS TIME OFF
  367. EXEC GetStatINFO
  368. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  369.  
  370.  
  371. -------------END PART FRAG4.2: REBUILD THE INDEX AND TEST AGAIN
  372.  
  373.  
  374. ----------------------BEGIN PART FRAG4.3: REORGANIZE THE INDEX AND TEST AGAIN
  375.  
  376.  
  377. --BEGIN REORGANIZE the Index
  378. ALTER INDEX ALL ON [FRAG4] REORGANIZE
  379. GO
  380. --END REORGANIZE the Index
  381.  
  382.  
  383. ----- BEGIN GET FRAGMENTATION INFORMATION
  384. EXEC GETINDEXINFO 'FRAG4'
  385. ----- END GET FRAGMENTATION INFORMATION
  386.  
  387.  
  388.  
  389. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  390. EXEC CleanBufferAndWaitBeforeTest
  391. SET STATISTICS TIME ON
  392. Select Count_Big (*) From [DBO].[FRAG4] Where Primarykey >0 Option (MAxDop 1)
  393. SET STATISTICS TIME OFF
  394. EXEC GetStatINFO
  395. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  396.  
  397.  
  398. ----------------------END PART FRAG4.3: REORGANIZE THE INDEX AND TEST AGAIN
  399.  
  400.  
  401.  
  402.  
  403. -------------BEGIN PART FRAG4.4: REBUILD THE INDEX AND TEST AGAIN
  404.  
  405. --BEGIN Rebuild the Index
  406. Alter Table [DBO].[FRAG4] REBUILD
  407. --END  Rebuild the Index
  408.  
  409.  
  410. ----- BEGIN GET FRAGMENTATION INFORMATION
  411. EXEC GETINDEXINFO 'FRAG4'
  412. ----- END GET FRAGMENTATION INFORMATION
  413.  
  414.  
  415.  
  416. ----- BEGIN CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  417. EXEC CleanBufferAndWaitBeforeTest
  418. SET STATISTICS TIME ON
  419. Select Count_Big (*) From [DBO].[FRAG4] Where Primarykey >0 Option (MAxDop 1)
  420. SET STATISTICS TIME OFF
  421. EXEC GetStatINFO
  422. ----- END CLEANBUFFER AND ALL WAIT FROM DATABASE AND MEASURE TIME
  423.  
  424.  
  425. -------------END PART FRAG4.4: REBUILD THE INDEX AND TEST AGAIN
  426.  
  427. -------------END PART FRAG4
Add Comment
Please, Sign In to add comment
Public Pastes
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!