Advertisement
Guest User

Untitled

a guest
Sep 28th, 2015
358
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.94 KB | None | 0 0
  1. /***************************************************************************
  2. * QUERY #1
  3. ***************************************************************************/
  4.  
  5. CREATE TABLE dbo.fiveMillionNumbers (
  6.     n INT PRIMARY KEY
  7. )
  8. GO
  9. INSERT INTO dbo.fiveMillionNumbers WITH (TABLOCK) (n)
  10. SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  11. FROM master..spt_values v1
  12. CROSS JOIN master..spt_values v2
  13. CROSS JOIN master..spt_values v3
  14. GO
  15.  
  16. -- Begin a transaction so that we can easily measure log usage
  17. BEGIN TRAN
  18. GO
  19.    
  20. SET STATISTICS TIME ON
  21. GO
  22.  
  23. CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
  24. GO
  25. INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
  26. SELECT n
  27. -- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
  28. FROM dbo.fiveMillionNumbers
  29. -- Provides greater consistency on my laptop, where other processes are running
  30. OPTION (MAXDOP 1)
  31. GO
  32.  
  33. SET STATISTICS TIME OFF
  34. GO
  35.  
  36. -- Inspect log usage
  37. SELECT tst.session_id, DB_NAME(tdt.database_id) AS db_name,
  38.     tdt.database_transaction_log_bytes_used / (1024.*1024.) AS log_mb_used
  39. FROM sys.dm_tran_database_transactions tdt
  40. JOIN sys.dm_tran_session_transactions tst
  41.     ON tst.transaction_id = tdt.transaction_id
  42. WHERE tst.session_id = @@SPID
  43. GO
  44.  
  45. COMMIT
  46. GO
  47.  
  48. -- Cleanup
  49. DROP TABLE dbo.minimalLoggingTest
  50. GO
  51. DROP TABLE dbo.fiveMillionNumbers
  52. GO
  53.  
  54.  
  55. /***************************************************************************
  56. * QUERY #2
  57. ***************************************************************************/
  58.  
  59. CREATE TABLE dbo.fiveMillionNumbersBadEstimate (
  60.     n INT PRIMARY KEY
  61. )
  62. GO
  63. INSERT INTO dbo.fiveMillionNumbersBadEstimate WITH (TABLOCK) (n)
  64. SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  65. FROM master..spt_values v1
  66. CROSS JOIN master..spt_values v2
  67. CROSS JOIN master..spt_values v3
  68. GO
  69. -- Simulate a complex sub-query or view where SQL Server yields a
  70. -- row estimate of just 1000 rows even though 5MM rows are actually present
  71. UPDATE STATISTICS dbo.fiveMillionNumbersBadEstimate
  72. WITH ROWCOUNT = 1000
  73. GO
  74.  
  75. -- Begin a transaction so that we can easily measure log usage
  76. BEGIN TRAN
  77. GO
  78.    
  79. SET STATISTICS TIME ON
  80. GO
  81.  
  82. CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
  83. GO
  84. INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
  85. SELECT n
  86. -- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
  87. FROM dbo.fiveMillionNumbersBadEstimate
  88. -- Provides greater consistency on my laptop, where other processes are running
  89. OPTION (MAXDOP 1)
  90. GO
  91.  
  92. SET STATISTICS TIME OFF
  93. GO
  94.  
  95. -- Inspect log usage
  96. SELECT tst.session_id, DB_NAME(tdt.database_id) AS db_name,
  97.     tdt.database_transaction_log_bytes_used / (1024.*1024.) AS log_mb_used
  98. FROM sys.dm_tran_database_transactions tdt
  99. JOIN sys.dm_tran_session_transactions tst
  100.     ON tst.transaction_id = tdt.transaction_id
  101. WHERE tst.session_id = @@SPID
  102. GO
  103.  
  104. COMMIT
  105. GO
  106.  
  107. -- Cleanup
  108. DROP TABLE dbo.minimalLoggingTest
  109. GO
  110. DROP TABLE dbo.fiveMillionNumbersBadEstimate
  111. GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement