Performance of sotred procedures in SQL Azure DB

Question

We recently moved one of our site to Azure. We have multiple instance of web role talking with DB. After onboarding to Azure we found deviation in performance. As these were well written SPs and working in production since long so we have want to focus only on Azure specific issue.

Is there any way to see performance of individual stored procedures in Azure. I do understnd we don't have profiler like features in Azure but still is there any way to find data like sp_execution start and endtime. or something similar. Also how we can track the time for each call comnig to SQLAzure from Web role.

Appreciate your help in advance.

Answer 1

Hi Arun Rakwal,

Unlike on-premises SQL Server, performance in SQL Azure can vary with factors, such as location of SQL Azure database and application hosted on, the workload on the related datacenter.

To troubleshoot the performance for a stored procedure, you may look into the Execution Plan and Client Statistics via the Management Studio, also you can use set statistics time on to get the time consumed.  Please see: SQL Azure Performance – Query Optimization.

>> Also how we can track the time for each call comnig to SQLAzure from Web role.
You may have a try to make use of DMVs (sys.dm_exec_query_stats,sys.dm_exec_sql_text) to obtain this information. Please see: Monitoring SQL Azure Using Dynamic Management Views.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi Arun,

Our service has the ability to track the performance of individual stored procedures.  We are a web based service where you can register your database and the stored procedures you want us to monitor and from there we will continuously log the performance of these stored procs and notify you by email if there are any issues with that performance. 

If you are interested in giving this a try, contact me at support AT cotega DOT com and mention this forum post and I will send you a coupon code for 30 days free.

The link to the service is in my signature.

---

SQL Azure Database Monitoring http://www.cotega.com