4

Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2).

Since then, it has been behaving rather erratically.

We have a relatively simple table called dbo.Asset. On it, there is a column called ContentSetId which is of type integer. This column is also covered by an index:

CREATE NONCLUSTERED INDEX [IX_Asset_ContentSet] ON [dbo].[Asset]
(
    [ContentSetId] ASC
)

The table contains around 2 million records.

The below query took 36 seconds to run the first time, but 0 seconds on subsequent runs. We are seeing similar behaviour in other queries too, whereby they can take upwards of a minute to return under 1000 rows, but subsequent runs are much, much quicker.

SELECT TOP 100 * FROM dbo.Asset WHERE ContentSetId = 3

As stated above, we have only noticed this behaviour occurring since changing from the business edition to the new standard s2 one.

Any suggestions would be very welcome!

1
  • At a guess, I think it's related to Data IO... The Azure management portal monitor for the metric "Data IO percentage" shows it has been hitting 100% regularly since changing the editions, but for the 13 days previously, its peak was 6.95%. This suggests I might need to upgrade to the Premium editions...but they seem way more expensive than the Business edition ever was?! Oct 15, 2014 at 16:25

2 Answers 2

4

My diagnosis from the information that is currently available:

Sounds like your tables are for whatever reason mostly uncached. The first run brings all data into cache. The plans probably have tons of random IO. This is extremely slow to run on a disk, and almost unnoticeable in memory. Therefore, I guess that the new edition does not provide you as much buffer pool memory as previously.

To be clear: The old edition has the same fundamental problem but thanks to more cache memory apparently it never manifested itself. You'd see the same effect on-premises.

Investigate whether you can chose an Azure offer that has more buffer pool memory. AFAIK there is no explicit control or documentation.

Or, tune your schema and queries so that hot queries use a smaller working set. Covering indexes with just the right columns are a potential way to do that (or worsen the situation by creating even more working set!).

A helpful benchmark of Azure SQL Database.

It is very hard running a database without a guaranteed buffer pool size. Performance loss can be sudden and dramatic. You better hope that you never outgrow the buffer pool allotted in P3, or else you hit a very hard wall. There is no way to pay for more memory.

3
  • Think I see where you're coming from. I'm bumping up our edition in Azure, so hopefully should see some improvement. A bit more research seems to reveal that Azure's new editions seem to offer a lot less performance for the same price, so it might be that coughing up more money is the answer. Oct 15, 2014 at 20:11
  • If you'Re curious you can test allocated buffer pool size by scanning the same table repeatedly and adding small amounts of data. Eventually the scan will suddenly take much longer. At that point measure the table size.
    – usr
    Oct 15, 2014 at 20:53
  • 1
    It does seem as though upgrading the Azure edition has made the difference, though I am quite shocked, as we're now paying 4x what we were before. It seems like we're not the only ones though. Oh well. Oct 16, 2014 at 15:45
5

I conducted a series of performance tests against the new service tiers (as mentioned by another poster above).

I tested I/O rates back in July and have now also tested the memory (i.e. max buffer pool size) in each tier: http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/

(I was going to add this as a comment on dark_perfect's comment above, but on this site I don't have enough reputation to add comments yet).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.