For the initial synchronisation DateTime values in the database were unchanged, but for any later incremental synchronisations the DateTime values were adjusted to the timezone of the server (Our Azure Cloud Services are UTC timezone, though I don’t understand why Microsoft by default has them set to US locale with MM/DD/YY date formats)

In our scenario having all of the DateTime values in the cloud local looked like a reasonable option and this article provided some useful insights.

In the end I found that setting the DateSetDateTime for every DateTime column in each DataTable in the synchronisation DataSet to unspecified in the ProcessChangeBatch (our code was based on the samples) method meant that no adjustment was applied to the incremental updates

For a few years I have worked on a midsize Azure application for managing promotional vouchers which are delivered via SMS or email and redeemed via software integrated into the point of sale terminal software of several local vendors.

A promotion has a batch of vouchers which are available for allocation to consumers and I had noticed from the logs and performance counters that the process of getting the next voucher slowed down significantly as the number of available vouchers decreased.

Voucher batches range in size from 100’s to 100,000’s of vouchers and the performance of a small promotion for a local wine shop was where I initially noticed how much the duration increased. The promotion had roughly 850 vouchers the allocation of the first vouchers took 10’s of mSec each but the last 10 vouchers sometimes took more than 1000mSec each.

I took a copy of the live database and removed the customer data so I could explore the performance of my TSQL in a controlled environment. I initially downloaded a copy of the database to one of my development servers and tried to simulate the problem while monitoring performance using SQL Profiler and other tools but the allocation time was fast and consistent.

The database performance appeared to be a SQL Azure specific issue so I built a cut back web role test harness which called the underlying stored procedure so I could closely monitor the performance. The test harness could make a specified number of calls recording the duration of each call and the overall duration. I then de allocated all the vouchers in the wine shop promotion and allocated them in chunks(all durations are in mSec and are the average time it takes to make a single call)

100 vouchers at a time (0 – 800 of 843 vouchers)

43, 88, 136, 191, 260, 305, 358, 379

10 vouchers at a time (800-840 of 843 vouchers)

431, 440, 404, 412

1 voucher at a time (840-843 of 843 vouchers, last one is failure)

400, 423,404, 390

After some debugging, progressive removal of code and looking at query plans I identified the problematic TSQL statement.

This update statement uses a single statement transaction to get the next random un-allocated voucher code in the specified voucher batch.

After some conversations with a SQL Azure support engineer at Microsoft (who was very helpful) he figured out that in SQL Azure the query processor needed a query hint to tell it to use an existing index to make it perform consistently.

I always have plenty of performance counters and logging (using the enterprise library) on my Azure web and worker roles but I was also lucky that I noticed something odd in the logs while checking on the progress of another promotion. I actively monitor the performance of my Azure applications as over time the performance characteristics of the underlying hardware will change as fixes and enhancements are released.