Trying to get Actual/Estimated execution plan agains SQL Azure

Question

Trying to get Actual/Estimated execution plan agains SQL Azure

devsql123

SSC-Addicted

Points: 478
More actions
May 26, 2015 at 1:08 am

#317741

Hi Folks
I am little new to SQL Azure...but well aware of SQL Server...so asking this question.
How can i see Actual/Estimated execution plan when i run query against SQL Azure DB ?
When i run the Batch of SQL Statements in SSMS, it shows most of queries with 0% usage...but almost after 241 queries...it stops showing Exectuion Plan details....!!!
I know that when DB in installed Locally or on somewhere on company network...i.e. NOT on Cloud...then hitting query shows Actual/Estimated execution plan in separate tab.
But this time when i am hitting query against SQL Azure...then i am getting incomplete result.
I hope you guys got the idea.
So thought to ask you...
Am i doing something wrong...!!!
Or shall i do something else..to see Actual/Estimated execution plan..against SQL Azure DB....!!!
Please guide me.
Thanks
Devsql

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You're using SSMS to capture a batch (sounds like a WHILE or CURSOR?) that is executing over 250 statements and you're trying to capture all 250 execution plans? I wouldn't recommend that on the earthed product, let alone in Azure. You can't really look through and read 250+ execution plans, so what is it you're trying to do?

Also, by and large, any general setting or option for the behavior of SSMS within the query window works within Azure. So if you wanted to change the output to a tab, you can do that by changing the options. I use that regularly and it works.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

devsql123 SSC-Addicted Points: 478 More actions · Answer 2

Hi Grant,

So Nice to hear from you....can't believe this..that you are replying to my post....wooooow...!!!

In last few days i read several of your articles, saw your photo...you have very good & in-depth tech knowledge...Nice to meet you.

Regarding my question, yes i was using while loop.

Select @TempDate = @StartDate

If Object_Id('#ReportDate') Is Null

BEGIN

Create Table #ReportDate

(

Id Int Identity(1, 1)

,ReportDate Date

)

END

While @TempDate <= @EndDate

Begin

Insert Into #ReportDate(ReportDate)

Select@TempDate

Select @TempDate = Dateadd(DD, 1, @TempDate)

End

So execution plan was showing Query plan for each iteration of While loop.

So when i go above date range of 1 Yr....i was facing this issue.

so for now...just to move ahead in solving problem...i created Regular DB based table...and moved ahead. so this 241 plan problem is solved for now...

But thank you very much for replying me.

Would like to meet you soon...

Sincerely

Devsql

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

I'm happy to help out any way I can.

Why were you trying to get 250 execution plans though? I'm just confused (not questioning you).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

I just ran a quick experiment with this code:

DECLARE @i INT = 0;

WHILE @i < 300

BEGIN

SELECT * FROM dbo.Agent AS a

SET @i += 1;

END

I was able to see all 300 execution plans from within SSMS. I'm running 2014. I connected to both a v11 Azure database and a v12. No issues on any of them. Were you getting errors?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning