I\u2019ve been interested in exploring what changes may be coming through in SQL Azure (Windows Azure \u2013 \u201cSQL Database\u201d now) around query execution cost models and cardinality estimates being fed to them.\u00a0 This was motivated by a couple of things\u2026<\/p>\n

First of all, I saw a blog post from Grant Fritchey<\/a> where he noticed that the estimated costs for a few plan operators were different.<\/p>\n

Secondly, I read a paper called \u201cTesting Cardinality Estimation Models in SQL Server<\/a>\u201d by Campbell Fraser, Leo Giakoumakis, Vikas Hamine, and Katherine F. Moore-Smith.\u00a0 This was a for-fee article, but the non-member price of $15.00 was worth it.\u00a0 One particularly interesting quote was as follows:<\/p>\n

\u201cThe new CE model is planned as a future service release of the Microsoft SQL Azure service.\u201d<\/p><\/blockquote>\n

That quote was a tipping point for further investigation, so, collaborating with Jonathan Kehayias, we discussed a testing approach and set up two different Azure databases, with one database on Web Edition and the other Business Edition.\u00a0 The intention wasn\u2019t to perform \u201cformal\u201d tests, but I did want to sniff around and see what variations in cost and cardinality estimates I could find (if any) between SQL Azure (version 11.0.2006) and SQL Server 2012 (version 11.0.2316) across various types of queries.\u00a0 I used the Credit database for a variety of test queries \u2013 with identical schema and data in all three databases (one engine DB and two Azure DBs).<\/p>\n

One thing I\u2019ve learned so far is that you should watch out for is misinterpreting cost differences for \u201cidentical\u201d databases.\u00a0 Even if you load the same exact schema and rows, you will likely have a different data page count between Engine and Azure (think of the Azure fillfactor and RCSI behavior).\u00a0 For example, after loading my dbo.member table in SQL Azure, it had 159 pages versus the 142 pages in my SQL Server 2012 version.\u00a0 So testing an initial Clustered Index Scan query against that table showed me an estimated IO of 0.1075694 in SQL Server 2012 versus 0.120162 for SQL Azure.\u00a0 So assuming one random I\/O and the rest sequential, I see that my SQL Azure cost is still calculated the same for the Clustered Index Scan:<\/p>\n

— Random I\/O – 0.003125
\n— Sequential – 0.000740741
\nSELECT\u00a0\u00a0\u00a0 0.003125 +
\n0.000740741 * (159-1);<\/p>\n

So the key will be to make sure I\u2019m looking at true apples-to-apples comparisons here.\u00a0 I\u2019ll be testing when I have a few spare moments between other tasks – but in the meantime I\u2019m very interested to learn more about what new changes will come in to SQL Azure in the future.\u00a0 I\u2019ll share anything interesting I find on the blog \u2013 and if you find noteworthy QO items, please share on the comments of this blog as well.<\/p>\n

Thanks!<\/p>\n","protected":false},"excerpt":{"rendered":"

I\u2019ve been interested in exploring what changes may be coming through in SQL Azure (Windows Azure \u2013 \u201cSQL Database\u201d now) around query execution cost models and cardinality estimates being fed to them.\u00a0 This was motivated by a couple of things\u2026 First of all, I saw a blog post from Grant Fritchey where he noticed that […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,22,14],"tags":[],"class_list":["post-478","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-execution-plan","category-azure"],"yoast_head":"\n