Testing Azure SQL DW Concurrency shows internally Azure is not running procedures concurrently

Question

I have read the Azure Concurrency and workload management in SQL Data Warehouse https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-develop-concurrency/ and understand the limits placed on the number of concurrent queries one can use based on scale, but what I cannot understand and thus raising the question here is when testing what the document states, I cannot get the results Azure claims are true. Running queries concurrently is still taking almost as long as running them serially.

For examples Here is a test example (just a test) I have 5 stored procedures that when run individually they take around 1 second each to complete. So when I run all 5 serially they take around 5 seconds, this is expected but when I run all 5 sprocs concurrently I would expect them to complete in a little over 1 second but instead they take about 4.5-4.7 seconds to complete.

Can some Azure expert explain what could be going on?

I thought it could be resource contention but sys.dm_pdw_resource_waits shows no blockage while the 5 sprocs are running.

When I run sys.dm_pdw_exec_requests I see all 5 exec sproc queries being submitted withing a few ms. Same is true for Start_time and End_compile_time. the end_time for all 5 sprocs is again within a couple ms but the Total_elapsed_time is closer to 5000 ms instead of the expected 1000 ms. If I run any sproc by itself the duration is around 1000 ms. Its as if Concurrency will start all 5 sprocs at the same time but internally they are queued up and run sequentially. I was originally testing on a DW200 which has 8 slots which should be enough for my 5 sprocs. To be safe I scaled out to a DW1000 which allows me up to 32 concurrent queries (I am using smallrc) but that did not help this issue.

Here is how I tested this (using DW1000)

I loaded 1000 records into 5 separate stage tables (stage1,stage2,etc..)

CREATE TABLE dbo.Stage1
(
     ShortId bigint NOT NULL
    ,TestName varchar(50) NOT NULL
    ,TestValue varchar(50) NOT NULL
    ,CreateDate DateTime NOT NULL 
)
WITH
(
    DISTRIBUTION = HASH (ShortId)
)

I created 5 fact tables (fact1,fact2, etc..) each table has the same 4 columns as stage and is distributed using hash on the first column. I did not include a columnstore index (remember this is only a test)
```
CREATE TABLE dbo.Fact1
(
     ShortId bigint NOT NULL
    ,TestName varchar(50) NOT NULL
    ,TestValue varchar(50) NOT NULL
    ,CreateDate DateTime NOT NULL 
)
WITH
(
    DISTRIBUTION = HASH (ShortId)
)
```

I created 5 stored procedures that inserts data into fact from stage.

CREATE PROCEDURE dbo.TestLoad1
AS
BEGIN
    INSERT INTO dbo.Fact1   --this is dbo.Fact2 in sproc 2 etc...
    SELECT 
       stg.ShortId
      ,stg.PropertyName
      ,stg.PropertyValue 
      ,stg.AcquistionTime
    FROM dbo.Stage1 stg
        WHERE stg.ShortId NOT IN (SELECT ShortId from dbo.Fact1) --Fact2 etc..
END

In C# I created a quick test method that creates 5 connections, commands and uses BeginExecuteReader/EndExecuteReader to execute the sproc. (this is just a test, so forgive the style/code)

SqlConnection cnn1 = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info = True;User ID =;Password = ;Pooling = False;MultipleActiveResultSets = False;Connect Timeout = 30;Encrypt = True;TrustServerCertificate = False");
SqlConnection cnn2 = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info = True;User ID =;Password = ;Pooling = False;MultipleActiveResultSets = False;Connect Timeout = 30;Encrypt = True;TrustServerCertificate = False");
SqlConnection cnn3 = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info = True;User ID =;Password = ;Pooling = False;MultipleActiveResultSets = False;Connect Timeout = 30;Encrypt = True;TrustServerCertificate = False");
SqlConnection cnn4 = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info = True;User ID =;Password = ;Pooling = False;MultipleActiveResultSets = False;Connect Timeout = 30;Encrypt = True;TrustServerCertificate = False");
SqlConnection cnn5 = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info = True;User ID =;Password = ;Pooling = False;MultipleActiveResultSets = False;Connect Timeout = 30;Encrypt = True;TrustServerCertificate = False");

SqlCommand cmd1;
SqlCommand cmd2;
SqlCommand cmd3;
SqlCommand cmd4;
SqlCommand cmd5;
IAsyncResult result1;
IAsyncResult result2;
IAsyncResult result3;
IAsyncResult result4;
IAsyncResult result5;
SqlDataReader reader1;
SqlDataReader reader2;
SqlDataReader reader3;
SqlDataReader reader4;
SqlDataReader reader5;

cnn1.Open();
cnn2.Open();
cnn3.Open();
cnn4.Open();
cnn5.Open();

cmd1 = new SqlCommand("dbo.TestLoad1", cnn1);
cmd2 = new SqlCommand("dbo.TestLoad2", cnn2);
cmd3 = new SqlCommand("dbo.TestLoad3", cnn3);
cmd4 = new SqlCommand("dbo.TestLoad4", cnn4);
cmd5 = new SqlCommand("dbo.TestLoad5", cnn5);

cmd1.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
cmd3.CommandType = CommandType.StoredProcedure;
cmd4.CommandType = CommandType.StoredProcedure;
cmd5.CommandType = CommandType.StoredProcedure;

result1 = cmd1.BeginExecuteReader(CommandBehavior.SingleRow);
result2 = cmd2.BeginExecuteReader(CommandBehavior.SingleRow);
result3 = cmd3.BeginExecuteReader(CommandBehavior.SingleRow);
result4 = cmd4.BeginExecuteReader(CommandBehavior.SingleRow);
result5 = cmd5.BeginExecuteReader(CommandBehavior.SingleRow);

reader1 = cmd1.EndExecuteReader(result1);  //this is where the code waits for 5 seconds
reader2 = cmd2.EndExecuteReader(result2);
reader3 = cmd3.EndExecuteReader(result3);
reader4 = cmd4.EndExecuteReader(result4);
reader5 = cmd5.EndExecuteReader(result5);

reader1.Close();
reader2.Close();
reader3.Close();
reader4.Close();
reader5.Close();

When debugging this C# code each statement is < 1ms until I get to the line reader1 = cmd1.EndExecuteReader(result1); Here it will wait for 4-5 seconds then move on and every line after is again quick (<1ms).

During that delay if I run select * from sys.dm_pdw_exec_requests I see all 5 requests are queued up and running. If I continue to re-run the query duration keeps increasing then all of sudden (around 5 seconds) all 5 queries say they completed.

Any help would be appreciated in explaining what I am doing wrong or what Azure SQL DW is doing internally.

Thank you

Testing Azure SQL DW Concurrency shows internally Azure is not running procedures concurrently

Answers (1)

Related Questions