Note: This question is part of a series of questions that present the same scenario. Each question in this series
contains a unique solution. Determine whether the solution meets the stated goals.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of
records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You
examine disk-related performance counters for the Microsoft SQL Server instance and observe several high
values that include a disk performance issue. You examine wait statistics and observe an unusually high
WRITELOG value.
You need to improve the application response time.
Solution: You update the application to use implicit transactions when connecting to the database.
Does the solution meet the goal?
A.
Yes
B.
No
Explanation:
http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs-explicit.html
Assuming that it is multi statement batch adding an transaction should improve WRITELOG. Without transaction each statement is separate transaction in a batch and must written to log individually.
6
2
But there is “implicit” transaction.
Implicit Transaction log generation: continuous chain of transactions
Explicit Transaction log generation: one transaction log entry.
And implicit transactions is OFF by default. So probably you will change transaction mode to the worse one.
I think correct approach is to split inserting to smaller pieces.
But I don’t know how to answer on the question. There is not enough information for me.
0
4
i think the answer is NO.
SEE this document:
https://blogs.msdn.microsoft.com/sqlsakthi/2011/04/16/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server/
To show a demo about difference between Autocommit and EXPLICIT transaction which changes the behavior of Log Flush, changed his code:
Before:
WHILE @i < 60000
Begin
INSERT INTO table values (1,'Name')
End
After:
BEGIN TRAN
WHILE @i < 60000
Begin
INSERT INTO table values (1,'Name')
End
COMMIT TRAN
Second batch with EXPLICIT transaction completed in just 2 seconds (compared to 8 mins when Auto commit transaction) in the same problematic environment. The reason is because every Auto Commit is flushing the transaction log buffer to disk for every INSERT but in the case of EXPLICIT XSN, log buffers are flushed only when they are full or due to any of the 4 conditions mentioned above, so the less log block flush, less the time we wait for I/O which decreases transaction latency. (Note that this was a test environment so almost, no other requests were running at that time)
3
0
so: the sp insert thousands of rows in a sigle batch. the batch is the collection of all the inserts.
with the default (EXPLICIT TRANSACTION) – if the stored procedure does not use BEGIN TRAN and COMMIT (they are optional), each statement is automatically committed (generating a lot of I/O)
so setting IMPLICIT TRANSACTION ON , make us able to give a single commit at the end of the batch. there is no enough information but i would choose YES.
4
0