A Small Collection of SQL Azure Queries

We have been doing some more work with SQL Azure lately, so I have put together a small collection of queries that are pretty useful if you are working with SQL Azure. As a DBA who likes to know what is going on with my database servers and individual databases, I sometimes get frustrated that SQL Azure is more like a black box than I would like. Of course, that is one of the main selling points, i.e. you don’t have to worry about those low level details with SQL Azure.

Microsoft has been adding more DMV query support as they release Service Updates for SQL Azure, but the latest “mini-update” was back in November. This makes me suspect that they are working on some big changes and improvements, since no updates have been released for over four months. At any rate, here are the queries:

 

-- SQL Azure Queries
-- Glenn Berry
-- March 2011
-- https://sqlserverperformance.wordpress.com/
-- Twitter: GlennAlanBerry


-- Get version information
SELECT @@VERSION AS [SQL Version Info];

-- SQL Azure Builds 
-- Build            Description
-- 10.25.9200        RTM Service Update 1
-- 10.25.9268        RTM Service Update 2
-- 10.25.9331        RTM Service Update 3
-- 10.25.9386        RTM Service Update 4
-- 10.25.9445        RTM Service Update 5
-- 10.25.9501        RTM Service Update "5a" (Nov 3, 2010)


-- You must be connected to master database
-- to run these queries

-- Get bandwidth usage by database by hour (for billing)
SELECT database_name, direction, class, time_period, 
       quantity AS [KB Transferred], [time]
FROM sys.bandwidth_usage
ORDER BY [time] DESC;

-- Get overall cost by SKU in dollars
SELECT SKU, SUM    (CASE WHEN USAGE.SKU = N'Web'
                            THEN (Quantity * 9.99/31)
                       WHEN USAGE.SKU = N'Business'
                          THEN (Quantity * 99.99/31)
                 END ) AS [CostInDollars]
FROM sys.Database_Usage AS USAGE
WHERE DATEPART(yy, TIME) = DATEPART(yy, GetUTCDate())
AND DATEPART(mm, TIME) = DATEPART(mm, GetUTCDate())
GROUP BY SKU;


-- Get Bandwidth cost by direction and type
SELECT USAGE.Time_Period, USAGE.Direction,
            CASE WHEN USAGE.Direction = N'Egress'
                    THEN 0.15 * USAGE.BandwidthInKB/(1024 * 1024)
                     WHEN USAGE.DIRECTION = N'Ingress'
                    THEN 0.10 * USAGE.BandwidthInKB/(1024 * 1024)
            END AS [CostInDollars]
FROM (SELECT Time_Period, Direction, SUM(Quantity) AS [BandwidthInKB]
       FROM sys.Bandwidth_Usage
       WHERE  DATEPART(yy, TIME) = DATEPART(yy, GetUTCDate())
       AND    DATEPART(mm, TIME) = DATEPART(mm, GetUTCDate())
       AND class = N'External'
       GROUP BY Time_Period, Direction) AS USAGE;


-- Get number of databases by SKU for this SQL Azure account (for billing)
SELECT sku, quantity, [time]
FROM sys.database_usage
ORDER BY [time] DESC;

-- Get firewall rules (must be connected to master)
SELECT id, name, start_ip_address, end_ip_address, 
create_date, modify_date 
FROM sys.firewall_rules
ORDER BY id;

-- List all logins on "instance" (must be connected to master)
SELECT name, principal_id, [sid], type_desc, 
       is_disabled, create_date, default_database_name
FROM sys.sql_logins
ORDER BY name;

-- List all databases (must be connected to master)
SELECT name, database_id, create_date, [compatibility_level]
FROM sys.databases;


-- Must connect to a user database 
-- in order to run these queries

-- Get max allowed size of database (use your database name)
SELECT CONVERT(BIGINT, DATABASEPROPERTYEX('ngservices' , 'MaxSizeInBytes'))/1073741824.0  AS [MaxSizeInGB];

-- Get current size of database
SELECT (SUM(reserved_page_count) * 8192)/1048576.0 AS [Database Size in MB]
FROM sys.dm_db_partition_stats;

-- Switch to Business Edition ($99.99/month)
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (EDITION = 'BUSINESS', MAXSIZE = 10GB);

-- Refresh SQL Azure Portal web page to see change

-- Switch to Web Edition ($9.99/month)
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (EDITION = 'WEB', MAXSIZE = 1GB);



-- Get row counts for tables in current database
SELECT OBJECT_NAME(object_id) AS [ObjectName], row_count, object_id, index_id 
FROM sys.dm_db_partition_stats
WHERE index_id < 2
ORDER BY row_count DESC;

-- Monitor connections in current database
SELECT s.session_id, s.login_name, e.connection_id,
      s.last_request_end_time, s.cpu_time, 
      e.connect_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS e
ON s.session_id = e.session_id
ORDER BY s.login_name;


-- Get session count by host name
SELECT [host_name], COUNT(*) AS [SessionCount] 
FROM sys.dm_exec_sessions AS s
GROUP BY [host_name]
ORDER BY [host_name]; 


-- Top Cached Plans By Execution Count
SELECT q.[text], hcpu.total_worker_time, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.execution_count DESC;


-- Top Cached Plans By total worker time (CPU)
SELECT q.[text], hcpu.total_worker_time, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_worker_time DESC;

-- Find top Avg CPU time queries
SELECT TOP (25) MIN(query_stats.statement_text) AS [Statement Text], 
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time],
query_stats.query_hash AS [Query Hash]
FROM (SELECT QS.*, SUBSTRING(ST.[text], (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.[text])
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY [Avg CPU Time] DESC;


-- Top Cached Plans By total logical reads (Memory)
SELECT q.[text], hcpu.total_logical_reads, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_logical_reads DESC;


-- Top Cached Plans By total elapsed time
SELECT q.[text], hcpu.total_elapsed_time, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_elapsed_time DESC;

-- This works in SQL Azure
EXEC sp_updatestats;

-- This works in SQL Azure
UPDATE STATISTICS CurrentPostMeta  -- This is a table name

-- DMVs that were added in SQL Azure Service Update 1
SELECT * FROM sys.dm_exec_connections; 
SELECT * FROM sys.dm_exec_requests; 
SELECT * FROM sys.dm_exec_sessions; 
SELECT * FROM sys.dm_tran_database_transactions;  
SELECT * FROM sys.dm_tran_active_transactions;

-- Other DMFs
SELECT * FROM sys.dm_exec_query_plan       -- needs a plan_handle
SELECT * FROM sys.dm_exec_sql_text           -- needs a plan_handle
SELECT * FROM sys.dm_exec_text_query_plan  -- needs a plan_handle, stmt_start_offset, stmt_end_offset
This entry was posted in Microsoft, SQL Azure and tagged , . Bookmark the permalink.

6 Responses to A Small Collection of SQL Azure Queries

  1. Mark Shay says:

    Thanks for posting these useful queries. This will come in handy to get better handle on my Azure databases

  2. Salvatore Giovanni Contino says:

    You know it is quite amazing how technology envelopes you and no matter where you look or what you hear and see, we are bombarded with “The latest buzz”. What is scary is that at first, its all “Buck Rodgers and the 21st Century” (Boy am I giving away my age) . And then right before your eyes, with a flash, what was … was “could” … Then BAM! We have gone past “could” to “doing” and right on to “done” and how we make it better.

    I have been working as a (database hands on DBA for lack of one) software developer for almost 27 years and the last17ish years have had my head deep into SQL Server from version 4.21 right after Microsoft acquired it form Sybase all the way through all the versions thru SQL Server 2005.

    Now I am in a whirlwind of confusion. Just when I am seriously trying to convince my customer to upgrade to SQL 2008. I have two more names that are buzzing about with a fury; AZURE, and Denali. Im not quite sure if we are talking about the old code for 2008 which is definately a released and used product. (I have it installed and looking at it), or the next generation product. But how can it be, when there is Denali where I have seen an announcement that Microsoft has just releasd CTP 3 (consumer technology preview). Now that is a long way from a full release. Could it be they are working on two versions at the same time or is AZURE actually SQL 2008? And if iti is, why are people still reffering to it by it’s code word?

    PS: Glenn, I do aplogize for speaking way off topic and if that is a no no, I will never do it again.

    Help
    The Italian “Get er Done” guy
    Salvatore

  3. interwebneil says:

    Such a helpful post. Thanks for blogging this.

  4. Pingback: March Madness - SQL Azure - sys.database_usage | SQLRockstar | Thomas LaRock

  5. Pingback: March Madness – SQL Azure – sys.dm_exec_query_stats | SQLRockstar | Thomas LaRock

  6. Pingback: De Olho no Azure – 15/04/2012 « Pensando Azure

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s