Execution Plan are different between SQL Server2008 R2 and SQL Azure

Question

Hi, buddy, I met this problem.

1, I backup database from SQL azure by Tool'DacImportExportCli.exe', So the data between SQL Azure and SQL Server2008 R2 local are same.

2, I run the same SP.

 On SQL Azure: the execute time is about 3 mins.

 On Local SQL Server R2: the execute time is 1-2s.

3, Then I found the execute plan for these two run are totally different.

4, I run the SP on these two situation yesterday, both of these situation is quickly.

So I don't know SQL Azure did what yesterday night. How can make these two same SPs use the same execute plan?

 

 

Answer 1

Hi wangweiguo,

A similar question was posted on this forum a few days ago:

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/50a6bbbf-b9d8-44d6-9e65-85e3179df143

In essense, you're not going to get an exact query plan from the two environments. You need to look at your query, parameters, how the query is written, and keep in mind that SQL Azure is a hosted enviornment and will generate a query plan to ensure proper execution in that enviornment.

---

Scott Klein Blue Syntax http://www.bluesyntax.net

Answer 2

SQL Azure is configured to only allow MAXDOP=1 and it looks like your on-premises instance is parallelizing the query.  Try adding MAXDOP=1 to your on-premises query and then compare the query plans. 

In addition, I would check for missing indexes and out of date statistics in your SQL Azure instance.

 

Evan

Answer 3

Thanks a lot, buddy.

 I fix that problem by rewrite the SQL. But I'll review your answers for the different execution plan. Thanks a lot.