August 11, 2016 at 12:06 pm
Hi,
we are going to use change tracking feature in order to track changes in some tables. Microsoft strongly recommends to use snapshot IL in the code that will be querying the changes. Initially I was going to use snapshot IL, however prod DBA have serious concerns about increased load on tempdb because of row versioning, so most likely we will not be using snapshot IL. I am wondering about possible drawbacks of not using snapshot IL. Here is a simplified version of a stored procedure that we are going to use:
create procedure dbo.GetChanges
@pLastSyncVersion bigint,
@pCurrentSyncVersion bigint OUTPUT
as
--set transaction isolation level snapshot;
begin try
if @pLastSyncVersion < CHANGE_TRACKING_MIN_VALID_VERSION(object_id('dbo.SomeTable'))
begin
raiserror('Provided change tracking version is outside of the retention period',16,1)
end;
begin transaction;
set @pCurrentSyncVersion = CHANGE_TRACKING_CURRENT_VERSION();
select
ct.SomeTableID
,ct.SYS_CHANGE_VERSION
,ct.SYS_CHANGE_OPERATION
,i.Column1
,i.Column2
--....
from CHANGETABLE(changes dbo.SomeTable, @pLastSyncVersion) ct
left join dbo.SomeTable i
on ct.InvoiceID = i.InvoiceID
commit transaction;
return 0;
end try
begin catch
.......
end catch
Retention period for change tracking would be default 2 days, stored procedure is supposed to be called by windows service ~ every 5 minutes. In this scenario what are the possible issues that we may face without using snapshot IL?
Thanks.
August 9, 2017 at 7:00 am
Did you ever get a response to your initial question? I'm in the same position and really don't want to enable Snapshot Isolation if I don't have to.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply