ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 71 discussion

Actual exam question from Microsoft's 70-762
Question #: 71
Topic #: 1
[All 70-762 Questions]

Note: This question is part of a series of questions that present the same scenario. Each question in the 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
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️
References:
http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs-explicit.html
by Mosufe at Aug. 25, 2020, 8:34 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Mosufe
Highly Voted 4 years, 8 months ago
Give a look at this: https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/ And no, with implicit transactions the time is waaaaaay bigger than with explicit ones
upvoted 7 times
...
Hoglet
Most Recent 4 years, 4 months ago
Batches and Transactions are 2 different things. For a fixed number of batches, the number of transactions determines how long it takes. If I have 100k batches, 1 transaction per batch, total elapsed time will be greater than if we used a single transaction performing the COMMIT at the end. The question states that we have a single batch (of many rows). It doesn't matter how the transaction is handled, as we have a single batch we can only have a single transaction. The speed will always be the same.
upvoted 1 times
...
TheDUdeu
4 years, 5 months ago
Explicit is faster. Use this to have SQL server not have to write each transaction to the log. Instead it performs all transactions on one statement therefore having better performance.
upvoted 2 times
...
Alex5x
4 years, 5 months ago
How do you understand this: "The stored procedure inserts thousands of records as a single batch into the Employees table."? I see it as a single INSERT of thousands of records. This means that the SP probably has a table valued input param or XML param which allows to pass those thousands of records. Or maybe it just selects those thousands of records from another table. Anyway, if there is only one INSERT, then no matter which transaction mode is used - implicit, explicit or autocommit. So, the answer is NO. If the question said "The stored procedure inserts thousands of records in a WHILE LOOP by inserting one record at a time", then the solution would be to either use implicit mode (SET IMPLICIT_TRANSACTIONS ON) + COMMIT TRAN in the end or explicit mode (issue a BEGIN TRANSACTION) + COMMIT TRAN in the end. In other words, the solution would be to avoid autocommit mode (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver15#remarks).
upvoted 4 times
...
pc29lo
4 years, 6 months ago
The answer is YES! set implicit_transactions off DECLARE @c INT SET @c = 1 WHILE @c < 100000 BEGIN insert into [dbo].[Emp] select 4,'',555 SET @c = @c + 1 END GO set implicit_transactions on DECLARE @c INT SET @c = 1 WHILE @c < 100000 BEGIN insert into [dbo].[Emp] select 4,'',555 SET @c = @c + 1 END COMMIT
upvoted 2 times
kiri2020
4 years, 5 months ago
the answer is Yes, set implicit_transactions on or using explicit transaction will reduce insert of the batch rows more than 6 times (with one Commit at the end)
upvoted 1 times
...
Hoglet
4 years, 4 months ago
What you have here is 100k batches each of 1 row, NOT 1 batch of 100k rows. If you try the following, CREATE TABLE emp_staging (col1 INT NOT NULL, col2 VARCHAR(50) NULL, col3 INT NOT NULL) CREATE TABLE emp (ID INT IDENTITY (1, 1) NOT NULL, col1 INT NOT NULL, col2 VARCHAR(50) NULL, col3 INT NOT NULL) DECLARE @c INT = 0 WHILE @c < 100000 BEGIN INSERT into [dbo].[emp_staging] select 4,'',555 SET @c += 1 END SET NOCOUNT ON SET IMPLICIT_TRANSACTIONS ON SET STATISTICS TIME ON INSERT INTO dbo.emp (col1, col2, col3) SELECT * FROM dbo.emp_staging SET STATISTICS TIME OFF SET IMPLICIT_TRANSACTIONS OFF SET STATISTICS TIME ON INSERT INTO dbo.emp (col1, col2, col3) SELECT * FROM dbo.emp_staging SET STATISTICS TIME OFF The elapsed time for both with vary, but by the same amount. What you are seeing is the overhead of committing 10K transactions (implicit_transactions off) vs committing 1 transaction (implicit_transactions on)
upvoted 1 times
...
Tinashe16
4 years, 4 months ago
This explanation is incorrect. First, you are using explicit transactions. Second, the code provided doesn’t have a begin tran. Third, using implicit tran is slower. In your scenario, you are actually explaining why the answer should be NO. But you’re saying the answer is YES.
upvoted 1 times
Tinashe16
4 years, 4 months ago
But the answer is NO. Because implicit tran is slower.
upvoted 2 times
...
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago