question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked db042190-2983 answered

is sql server smart enough to use indexed row versions efficiently in multi join queries?

Hi we will be on 2019 in a few months. i dont know whether it will be enterprise or std.

i'm a big fan of row versions (cast as bigint) as a better choice than datetimes to be indexed so we can quickly identify in incremental extracts only those records that changed since last time we ran an an extract. pls see the attached code for 2 sample tables and the control table we use to keep track of the last high key encountered on each table. you can see the first 2 records being inserted to the control table. basically the most current records in this table are "ended" on each extract and 2 new records with new high keys are inserrted.

i'm looking at a relatively deep join right now and have to wonder how smart/efficient sql will be in extracting only the latest changes. in the example i show 2 tables but in reality its n tables that basically chain to each other thru various combos of pk's and pointers and we can assume the pointers are fks too. and for simplicity i'd like to assume they each table has a row version cast as bigint indexed.

the predicate in the join might look something like

where (research1.row_version_number > x and reserach1.row_version_number <= y) or
(research2.row_version_number > x1 and research2.row_version_number <=y2) or...

and the join to the 2nd table might look something like this...

select ...from research1 r1
join research2 r2
on r1.apointer=r2.pk...

so i'm wondering if sql will throw its hands in the air and apply the worst strategy in joining the n tables or try to apply some cool algorithm that even understands the nth table changed but the 1st didnt , yet the 1st table's "related" record needs to be extracted anyway. if the answer is yes, is there a term for what it will do in its plan?

 USE [research]
 GO
    
 SET ANSI_NULLS ON
 GO
    
 SET QUOTED_IDENTIFIER ON
 GO
    
 CREATE TABLE [dbo].[research1](
     [Id] [int] IDENTITY(1,1) NOT NULL,
     pk uniqueidentifier,
     aPointer uniqueidentifier,
     [aFact] [int] NULL,
     row_version_stamp rowversion,
     row_version_number as cast(ROW_VERSION_STAMP as bigint),
        
  CONSTRAINT [PK_research1] PRIMARY KEY CLUSTERED 
 (
     [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
    
 GO
    
 SET ANSI_NULLS ON
 GO
    
 SET QUOTED_IDENTIFIER ON
 GO
    
 CREATE TABLE [dbo].[research2](
     [Id] [int] IDENTITY(1,1) NOT NULL,
     pk uniqueidentifier,
     aPointer uniqueidentifier,
     [aFact] [int] NULL,
     row_version_stamp rowversion,
     row_version_number as cast(ROW_VERSION_STAMP as bigint),
        
  CONSTRAINT [PK_research2] PRIMARY KEY CLUSTERED 
 (
     [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
    
    
 CREATE unique NONCLUSTERED INDEX [IX_research1_rowversionnumber] ON [dbo].[research1]
 (
         ROW_VERSION_NUMBER
 )
 WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
 GO
    
 CREATE unique NONCLUSTERED INDEX [IX_research2_rowversionnumber] ON [dbo].[research2]
 (
         ROW_VERSION_NUMBER
 )
 WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
 GO
    
    
 USE [research]
 GO
    
 SET ANSI_NULLS ON
 GO
    
 SET QUOTED_IDENTIFIER ON
 GO
    
 CREATE TABLE [dbo].[JobControl](
     [JobControlId] [int] IDENTITY(1,1) NOT NULL,
     [PackageName] [varchar](100) NULL,
     [ServerName] [varchar](16) NULL,
     [InstanceName] [varchar](16) NULL,
     [DatabaseName] [varchar](128) NULL,
     [SchemaName] [varchar](128) NULL,
     [TableName] [varchar](128) NULL,
     [HighKey] [varchar](100) NULL,
     [StartDate] [datetime2](7) NULL,
     [EndDate] [datetime2](7) NULL,
 PRIMARY KEY CLUSTERED 
 (
     [JobControlId] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
    
 declare @dt datetime2(7) = getdate()
    
 insert jobcontrol
 (
     [PackageName] ,
     [ServerName] ,
     [InstanceName] ,
     [DatabaseName] ,
     [SchemaName] ,
     [TableName] ,
     [HighKey] ,
     [StartDate] ,
     [EndDate]
 )
    
 select 'pkg1','server1',null,'research','dbo','research1','0',@dt,null
 union
 select 'pkg1','server1',null,'research','dbo','research2','0',@dt,null
sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·

will do. thanks.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

There are many variables in the query plan which will determine if it will use the index or not. The bottom line is you will need to test it yourself and see. It may use the index on one table, and not on another table for many reason.

I would highly recommend adding PERSISTED to your calculated column.

However, I would not do this this way. I would use datetime and transfer all the records from the last run START date. Yes, it may transfer a few records again, but it should be a small number. Your method requires you to store the timestamp last sent. This could be problematic and is not really any different than using datetime.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I would not have too high hopes, but it is impossible to say when only seeing fragments of a query. My experience is that conditions with OR rarely work very well and a rewrite to a UNION query pays off, even if it may be more verbose.

While rowversion is convenient for change detection, there is a risk for missing updates if there are concurrent operations. Particularly, if your using some sort of snapshot.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered TomPhillips-1744 edited

thx gentlemen.

i should have mentioned that getting in and out of the oltp as fast as possible is usually my goal in incremental etl (near real time) processes.

Tom, we are persisting the computed column (it must be the default cuz i dont think about that anymore), and from my experience timestamps are less porous than date times in both directions (ie too many or too few records returned). And as you stated there is no difference (otherwise). In reality, timestamps always return exactly what you need and its impossible for the source system owners to change them for special one time fixes etc.

Erland, I cant imagine that sql will not at least stamp two concurrent updates with different timestamps. and the beauty of this approach is the predicate "or...> last high etl key and <= currently selected max key" which should pick everything up. we have a journal db so even if sql doesnt give me a consistent "state" with both the max keys and record contents between the min's and max's , missed timestamps (update or physical delete) will be sitting in the journal db with their original row version.

like both of you said, something told me sql wasnt that smart. I'm not bashing sql as that would be a tall order. I have some pretty good evidence now (after running a few queries) that sql isnt that smart. lets face it, in order to be that smart it would have to build temporary critical key "filters" top down and bottom up. The bottom up scenarios would be necessitated by the OR conditions.

i'm going to try the following strategy next while continuing to leave the timestamp indexes in place. i'm not logged into work but i believe the depth of the joins is no more than 15 tables. And only 6 or 7 of those tables really need a timestamp because the others are so static and small that they can be joined separately (ie abstracted from the core challenge here) once the critical data is extracted.

If we call the very first table in the query "top dog" or research1, the idea would be to collect all of top dog's PK candidates using the min/max logic in whatever direction is necessary from the 6 or 7 tables and then using a where exists clause over this population driving the query from top dog down. My expectation is that there will be no more than 80,000 candidates overall (on any given run) but that we can come close to that number from any one of these 6 or 7 PK generating result sets. My expectation also is that each pk generating query should run (or add) no longer than 4 seconds. and my gut says that this strategy might open my options on which table is now top dog. Maybe the table right in the middle (waistband?) of the original query should be top dog now, who knows?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Erland, I cant imagine that sql will not at least stamp two concurrent updates with different timestamps.

I suspect that there is either a "not" too few or too many here. Each rowversion value is unique, and they are assigned from a database-wide monotically increasing value.

Wihle rowversion is better than datetime2(3) columns where you can get collisions, and you are at the mercy of the programmer or the person writing update statements that the value is actually updated, rowversion is still not foolproof, as they are not designed for this purpose.

What you need to consider is that updates can occur while you are reading. If you are using a snapshot, you also need to consider that a row with higher rowversion value is commited, while a row with an earlier rowverison is still uncommitted. If you use the MAX from the previous read as your high-water mark, you will never get that other row, even it it commits. If you instead use MIN_ACTIVE_ROWVERSION() as your next high-water mark, your chances are better.

Or look at Change Tracking which is designed for this purpose and more robust.

0 Votes 0 ·

As Erland said, you need to store the max row version at the START if your transfer. Otherwise, you run the risk of missing transactions in progress during the transfer (from experience). You always may have the risk of transferring some of the same records again. You need to allow for that.

I have done this many times, and both ways work. But, I tend to use datetime2(7) instead so I can actually see the datetime it was changed. Yes, it does depend on the developer consistently changing the field and is not automatic. Also, if you used datetime2(7) you will want to get the start date FROM the server you are pulling data to avoid the time skew (again from experience).

If you run SELECT * from table WHERE rowversion (or datetime2) > xxxxx, The query optimizer may or may not use the rowversion index because you are returning every field. It may determine it is better to use the clustered index on smaller tables, and the rowversion index on very large tables. As I said, you will need to test it.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @db042190-2983,

139396-image.png
No.

SQL Server's query optimizer generates an efficient query execution plan when the select query is executed. The execution plan is generated on the basis of statistical information. If the statistical information is not updated in time, an inefficient execution plan may be generated. If the optimizer cannot choose the optimal plan, then you need to check the query plan, statistics, supported indexes, etc., and through the use of hints, you can change the optimizer's selection of the query plan project, so that the optimizer generates a better execution plan.


Regards
Echo



image.png (31.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered ErlandSommarskog commented

thx all.

erland i'm not totally understanding what you say. i am not using a snapshot. and each time i go to extract (every 15 mins) i'm asking for max row version (new high water mark) in the very same read/query (this is what i meant by "state") as the rest of the extracted data.

tom, i'm not sure what you mean by "transfer" but i'm guessing you mean "extract", ie etl. i am getting the new high value at the start of my extract and i think you guys are saying that even that doesnt guarantee that a higher value isn't yet committed or worse that a lower one isnt yet committed and will therefore be ignored by the > x and <= y predicate. if a higher value isnt yet committed, its no biggy because 15 minutes from now it could be the new high water mark or minimally be a candidate for extraction in the next extract. it seems to me that the same pitfalls being discussed here apply to datetimes. the same would seem to be true for cdc because i doubt that cdc cares about uncommitted data. and in a way its kind of too late if we get the uncommitted data 15 minutes from now. we dont really like cdc anyway.

i'll read about min_active_row_version to see if i can get on the same page as you guys. my goal is not to miss "in flux" updates whose row version is less than what sql says is currently the max row version. i wonder if there is a command that can be issued that waits for those to get committed. in a way, i'm creating a snapshot of my own. the snapshot is more or less operational and started over each day. it would be best if it doesnt miss in flux row versions that are less than the current value of max rowversion.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erland i'm not totally understanding what you say. i am not using a snapshot.

So what does this query return?

SELECT is_read_committed_snapshot_on FROM sys.databases  WHERE name = 'yourdb'


my goal is not to miss "in flux" updates whose row version is less than what sql says is currently the max row version. i wonder if there is a command that can be issued that waits for those to get committed. in a way,

No, there is no such command, and SQL Server does not track uncommitted rowversion values.

You need to get this in your head: the rowversion feature is not designed for what you want to do. The main purpose of rowversion is to make it easy to detect concurrent updates when you are using optimistic concurrency. Yet, people have used rowversion for changed detetect - with mixed success. For an environment with many concurrent updates, it is very difficult to get right without missing updates. It is also difficult to understand why you miss these updates, as there are many subtle nuances.

Rowversion can be OK, if the update frequency is OK, and you can accept to miss an occasional update. But it does not sound like that from your posts. Thus, my strong recommendation is that you abandon this idea, and look at Change Tracking instead, as this feature is designed for what you are aiming at. (And don't confuse Change Tracking which Change Data Capture, which is yet another feature.)



0 Votes 0 ·
db042190-2983 avatar image
0 Votes"
db042190-2983 answered TomPhillips-1744 commented

min_active_rowversion() seems like it would be useful in the same query to tell me if a lower row version than what is currently max is in flux, ie not committed. if not , it would be greater than the current max.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I suggest you read this:
https://www.mssqltips.com/sqlservertip/3295/using-rowversion-with-sql-server-integration-services-to-reduce-load-times/

and
https://blog.devgenius.io/sql-server-overview-of-different-methods-for-change-tracking-69b168dc489a

0 Votes 0 ·
db042190-2983 avatar image
0 Votes"
db042190-2983 answered ErlandSommarskog commented

thx Tom i'll read your link next.

erland, i ran that query asking if read committed snapshot is on and got a zero. i'll check later if that is the rcsi feature (14 bytes per record , virtually no deadlocks, etc etc) or the other snapshot feature that i think is more of a query by query thing without the 14 byte overhead per record.

i'll read up on change tracking first. i thought you were talking about cdc.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erland, i ran that query asking if read committed snapshot is on and got a zero. i'll check later if that is the rcsi feature (14 bytes per record , virtually no deadlocks, etc etc) or the other snapshot feature that i think is more of a query by query thing without the 14 byte overhead per record.

RCSI = Read Committed Snapshot Isolation.

Both RCSI and true snapshot isolation comes with the 14 bytes overhead per record.

0 Votes 0 ·
db042190-2983 avatar image
0 Votes"
db042190-2983 answered ErlandSommarskog commented

erland, change tracking is interesting. the most obvious question to me has to do with inserts and deletes. does it track those as well?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered db042190-2983 edited

thanks tom, i read Biddle's tip and its almost identical to what i do. I asked him to read the posts here and comment. erland's points make a lot of sense especially in the somewhat volatile data capture i'm looking at right now.

erland, i see now that change tracking does track inserts and deletes as well. its an interesting option. i like that the "uncommitted" item has to show up eventually. i like that you only need to specify the tables you want included in the tracking. i wonder if our oltp guys would freak that i'm suggesting we try a synchronous tracking feature on an app that probably cant afford to perform poorly. i like that it runs on std edition. i'm guessing that when my process wakes up every 15 minutes, and it needs to read 80k "updates" per table (there are actually only 4 tables), that those reads will be sub second. i'm guessing without getting into the details that what i'll have are PKs from that store. i'm guessing that i (or sql) needs to flush that store when i'm done and sql will ensure that nothing got dropped between my read and restart of tracking. my biggest challenge would be knowing which version of a specific pk is the one i want. when timestamps are recorded, they presumably are from when the record was built, not committed. that could be a problem no less worrisome than some of the issues brought up about row versions.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

at my request, robert biddle has commented on this chain at tom's link https://www.mssqltips.com/sqlservertip/3295/using-rowversion-with-sql-server-integration-services-to-reduce-load-times/ . i have a pending question to him there asking that he expound on "decoupled architectures".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.