Fiddling with SQL Azure

I got to test SQL Azure, and found out a few interesting things:

  • I can actually see the names of the actual physical machines in the Microsoft datacenter
  • there is some issue with connections timeouts and blocking management
  • I would still like to run a benchmarking test which will tell me how many concurrent connections I can get and what data throughput I can get to and from Azure

But lets start with the names of the physical servers.

Let’s create a SQL Azure database named SomeDatabase.

In Azure we have quite some limitations when it comes to metadata, and for example we can’t run sp_who2. But we can create the following procedure in the SomeDatabase database and then we can execute it. (the code is taken from Derek Dieter from here: http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3)

 

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
CREATE PROCEDURE [dbo].[sp_who3]
 
AS
BEGIN
 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT
    SPID                = er.session_id
    ,BlkBy              = er.blocking_session_id      
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes    
    ,Executions         = ec.execution_count  
    ,CommandType        = er.command        
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )        
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
ORDER BY
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id
 
END

So when we have created the stored procedure in our Azure database, we can run it and see the resultset.

But before we run the sp_who3 procedure, lets create an table Table1 in our Azure database and let’s run some requests running in explicit transactions.

The code for this will be the same, but run in separate connection:

?Download download.txt
1
2
3
4
begin tran
 
update [dbo].[Table1]
set [Column1]='123'

I am running the requests from both Management Sudio on my local machine and also through the Azure Database Management portal.

So here is how the result of the sp_who3 looks like when I have executed several of these requests:

 

Notice that there are 5 different records in the Host column:

Host
A328-ST00
DB3CDB11021220
DB3CDB11021620
DB3CDB11021115
DB3CDB11020915
The A328-ST00 is my local machine, i.e. my Management Studio host, but all the other ones are the SQL Azure Hosts.

Nice one. I was told that no one can know the host machine names in the Microsoft Azure datacenter. :)

Now, let’s get to the second issue, i.e. how come we have a session blocked by a non-existing session?

As we can see in this picture, there is a session with ID 515, which blocks all sessions, and the session 515 is blocked by session 529 which is not even in the list.

 

So, how come I see only some sessions and not others? What else am I missing?

Is the metadata inforation accurate?

I would guess that the session 529 is the first session executed, which is blocking everyone else, but I have no idea why it is not showing in the list…

 

Anyway, there is so much work to do related to SQL Azure before it even gets to a decent state and ready to be used in production environments… (don’t even get me started on Table Storage, maybe I will blog about it later)

 

 

 

 

Comments are closed.