One of the capabilities of the Cotega service is the ability to schedule execution of stored procedures in Azure SQL. This is handy for many administrators because Azure SQL does not have a SQL Agent capability. One of the interesting side effects I have seen from this is that long running stored procedures can cause issues when they are scheduled to run frequently. If the previous execution of the stored procedure had not completed before the next one starts, there can be issues such as locking or even extreme cases where they pile up and eventually cause connection drops due to reaching the maximum number of request counts.
One solution that I have come up with is to add code to the start of a stored procedure that detects if it is already running. If it is, it simply returns. It was interesting to see that there was not a lot of information that I could find on the internet to detect if an Azure SQL stored procedure is running, so I decided to build one. This is what I came up with which will return a count of the number of times a specific stored procedure is currently running:
CREATE FUNCTION [dbo].[CheckStoredProcActive] (@StoredProcnvarchar(255))
declare @spCount int;
set @spCount=(select count(*) FROM sys.dm_exec_sessionss
INNER JOIN sys.dm_exec_requestsr
CROSSAPPLY sys.dm_exec_sql_text(r.sql_handle) AS SQL
INNER JOIN sys.objects o
WHERE s.is_user_process= 1
AND [email protected]);
Then you can simply add the following to the start of your procedure where you replace [ENTER_STORED_PROC_NAME] with your stored procedure name:
if (dbo.CheckStoredProcActive(‘[ENTER_STORED_PROC_NAME]’)> 0)
I hope others find this helpful.
I am the founder of a startup called Cotega and also a Microsoft employee where I work as a Program Manager. This is a series of posts where I talk about my experience building a startup outside of Microsoft. I do my best to take my Microsoft hat off and tell both the good parts and the bad parts I experienced using Azure.
Working on a monitoring service for SQL Azure Azure SQL Database allows me to get a really good idea of the common issues DBA’s have when working with their database. If I had to choose the most common area of frustration I hear from DBA’s is whether their performance issue is related only to their database or to the entire data center where their database is hosted? It has been a goal of mine from the very early days of Cotega to help solve this problem. Today I am happy to announce that within Cotega, you are now able to see your performance data overlayed with that of the data centers performance. As you can see below, here is an example of connection latency done from outside the Azure data centers. It shows the connection latency of my database (in blue) along with the average latency of all other customers in this datacenter (in orange).
This has taken me some time to build because in order to get a true picture of the data center you are in, I needed to have enough customers in each of the data centers such that I could get an aggregated average of all the data gathered from the monitoring of these database without exposing any information about any one customers performance data.
As of right now, I have a really good picture of all the major Azure data centers (with the exception of East Asia). In fact, even without a Cotega account, you can see the status of each of the data centers here. Please note that this data is related only to that of SQL Database and does not reflect that of other Azure services.
I hope that all of you will consider creating an account to help everyone create an absolutely perfect picture of the health of SQL Azure database data centers. To learn more about how to create an account, please visit www.cotega.com.