March 21, 2014 at 1:30 am
set implicit_transactions oN
When setting set implicit_transactions oN
the query blocks and it takes more time to delete some 10k rows out of 3 million rows
SP_lock
51 24 98765187 2 PAG 1:35529 U GRANT
51 2 2 1 KEY (5300f8b0685d) X GRANT
51 24 98765187 2 PAG 1:35021 U GRANT
51 24 98765187 2 PAG 1:34260 U GRANT
51 24 98765187 2 PAG 1:35279 U GRANT
51 24 98765187 2 PAG 1:35530 U GRANT
51 2 0 0 EXT 1:1528 X GRANT
51 24 98765187 2 PAG 1:34263 U GRANT
51 24 98765187 2 PAG 1:35276 U GRANT
51 24 98765187 2 PAG 1:35531 U GRANT
51 24 98765187 2 PAG 1:35023 U GRANT
51 24 98765187 2 PAG 1:34262 U GRANT
51 24 98765187 2 PAG 1:35277 U GRANT
51 24 98765187 2 PAG 1:35532 U GRANT
51 24 98765187 2 PAG 1:35016 U GRANT
51 24 98765187 2 PAG 1:34257 U GRANT
51 24 98765187 2 PAG 1:35274 U GRANT
51 24 98765187 2 PAG 1:35533 U GRANT
51 24 98765187 2 PAG 1:35017 U GRANT
51 24 98765187 2 PAG 1:34256 U GRANT
51 24 98765187 2 PAG 1:35275 U GRANT
51 24 98765187 2 PAG 1:35534 U GRANT
51 2 3 2 KEY (b7028d8e784f) X GRANT
51 24 98765187 2 PAG 1:35018 U GRANT
51 24 98765187 2 PAG 1:34259 U GRANT
51 24 98765187 2 PAG 1:35272 U GRANT
51 24 98765187 2 PAG 1:35535 U GRANT
51 2 0 0 EXT 1:2408 X GRANT
51 24 0 0 PAG 1:987651879 X GRANT
Where as if set implicit_transactions OFF (or not giving this option in Stored procedure) results in quicker delete.
IMPLICIT_TRANSACTIONSis for dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
Why set implicit_transactions ON is blocking? if it is blocking what is the need of it. Query taking long time to delete. what is the best option to delete.
how to Completely Commit or rollback the transaction.
XACT_ABORT Rolls back a transaction if a Transact-SQL statement raises a run-time error.
it is at transaction level so can we put inside the stored procedure?
March 21, 2014 at 4:14 am
When you set implicit transactions on, any statement starts a transaction which is not committed until you explicitly commit it. It's something you only use if you know what you're doing, you know exactly what's running and you are happy to explicitly commit the transactions in every single block of code run.
For how best to delete, refer to your post from last week where several people explained the delete in batches
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply