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.
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.
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.