SQL Server 2008 R2: StreamInsight AdvanceTimePolicy.Adjust

While building content for the upcoming Metro training for SQL Server 2008 R2, Bill Chesnut and I were puzzled about the Adjust option for AdvanceTimePolicy in the AdvanceTimeSettings for a stream. It was described in Books Online as causing the timestamp for the event to be adjusted forward to the time of the latest CTI (current time increment). No matter what we tried though, we couldn’t seem to get it to do anything.

After discussing it with Roman from the product group, we worked out what our issue was.

We were using EventShape.Point. That means that the starttime and endtime are 1 cronon apart (smallest unit of time for the system). Our events had times that were prior to the latest CTI timestamp but we weren’t seeing them be adjusted.

Turns out that the adjustment only occurs when your event interval (ie: from starttime to endtime) overlaps the CTI. Then, the starttime of the event is adjusted to match the CTI. This means the event has been adjusted to start at the CTI timestamp and still end when it was recorded as ending before adjustment.

Because we were using EventShape.Point, no adjustment was occuring as our event didn’t overlap the CTI. Had we been using EventShape.Interval, and had a starttime before the CTI and and endtime at or after the CTI, we would have seen the adjustment working.

2010-01-09