A Small Collection of SQL Azure Queries

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

-- Get Occupied Size of Tables

select sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 as MB
from sys.dm_db_partition_stats, 
     sys.objects 
where sys.dm_db_partition_stats.object_id = sys.objects.object_id 
group by sys.objects.name order by MB Desc