SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Azure SQL Database Execution Plan Differences

I’ve been exploring execution plans in Azure SQL Databases a lot lately. I’m getting a presentation together for  some upcoming SQL Saturday events (first one is SQL Saturday #177, Silicon Valley). If you scroll to the bottom of this previous post, I mentioned that there were clearly differences in the optimizer because queries against empty databases were generating different plans. I’ve loaded up the data in my database, both SQL Server and SQL Database, so I can compare real behaviors. Doing so, I found a fun difference, even though I was running the query and generating the plan from SQL Server Management Studio.

Here’s the property sheet from the SELECT operator for the query run against SQL Server:


And here’s the property sheet from the SELECT operator for the query run against SQL Database:


Two differences jump out. The smaller of the two is that for the same data set, there are differences in estimated values. I noted that before, but it’s fun to see it coming up. By the way, if a good estimate could be defined as “closest without going over” then the optimizer in SQL Database is closer with 1.9 to the SQL Server value of 2.1. Just sayin’. Anyway, the big thing that you may have noticed was the NonParallelPlanReason with a value of CouldNotGenerateValidParallelPlan. Oooh, cool! Not that I would have expected a parallel plan for an estimated cost of .0149347. Assuming SQL Database has a default value of 5 for parallel plans, that’s pretty far off (and I hope they don’t still use that ancient value, nor should you). Some enterprising individual could try to suss out how much cost is needed to get a parallel plan (I don’t have time for that right now). More to come on execution plans and query tuning in Azure SQL Databases.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...