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
5124987651872PAG1:35529 UGRANT
51221KEY(5300f8b0685d) XGRANT
5124987651872PAG1:35021 UGRANT
5124987651872PAG1:34260 UGRANT
5124987651872PAG1:35279 UGRANT
5124987651872PAG1:35530 UGRANT
51200EXT1:1528 XGRANT
5124987651872PAG1:34263 UGRANT
5124987651872PAG1:35276 UGRANT
5124987651872PAG1:35531 UGRANT
5124987651872PAG1:35023 UGRANT
5124987651872PAG1:34262 UGRANT
5124987651872PAG1:35277 UGRANT
5124987651872PAG1:35532 UGRANT
5124987651872PAG1:35016 UGRANT
5124987651872PAG1:34257 UGRANT
5124987651872PAG1:35274 UGRANT
5124987651872PAG1:35533 UGRANT
5124987651872PAG1:35017 UGRANT
5124987651872PAG1:34256 UGRANT
5124987651872PAG1:35275 UGRANT
5124987651872PAG1:35534 UGRANT
51232KEY(b7028d8e784f) XGRANT
5124987651872PAG1:35018 UGRANT
5124987651872PAG1:34259 UGRANT
5124987651872PAG1:35272 UGRANT
5124987651872PAG1:35535 UGRANT
51200EXT1:2408 XGRANT
512400PAG1:987651879 XGRANT
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_ABORTRolls 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