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

I had a question come up during my “Getting Started with SQL Azure” presentation at SQLSaturday #52 in Denver last Saturday that I did not know the answer to. It had to do with how you could see what your data transfer usage was in and out of a SQL Azure database. This is pretty important to know, since you will be billed at the rate of $0.10/GB In and $0.15/GB Out (for the North American and European data centers).  Knowing this information will help prevent getting an unpleasant surprise when you receive your monthly bill for SQL Azure.

SQL Server MVP Bob Beauchemin gave me a nudge in the right direction, and I was able to put together these queries, which I hope you find useful:

-- SQL Azure System Queries
-- Glenn Berry 
-- September 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

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

-- 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 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 all SQL logins (which are the only kind) for this SQL Azure "instance"
SELECT * 
FROM sys.sql_logins;

-- Get firewall rules for this SQL Azure "instance"
SELECT id, name, start_ip_address, end_ip_address, create_date, modify_date
FROM sys.firewall_rules;

-- Perhaps a look at future where you will be able to clone/backup a database?
SELECT database_id, [start_date], modify_date, percent_complete, error_code,
error_desc, [error_severity], [error_state]
FROM sys.dm_database_copies
ORDER BY [start_date] DESC;

-- Very unclear what this is for, beyond what you can infer from the name
SELECT instance_id, instance_name, [type_name], type_version, [description],
       type_stream, date_created, created_by, database_name
FROM dbo.sysdac_instances;

According to all of their public pronouncements and the flurry of recent activity, Microsoft seems extremely serious about Windows  Azure and SQL Azure. If you are interested in playing around with Windows Azure and SQL Azure for free, you can try the Windows Azure Platform One Month Pass, by following this link. This is only for developers in the United States, and is limited to the first 500 who sign up each month.

                              ]]> https://sqlserverperformance.wordpress.com/2010/08/06/windows-azure-platform-one-month-pass/feed/ 0 46 guderian1961 https://sqlserverperformance.wordpress.com/2010/07/21/project-houston-for-sql-azure/ https://sqlserverperformance.wordpress.com/2010/07/21/project-houston-for-sql-azure/#respond Thu, 22 Jul 2010 03:41:28 +0000 http://sqlserverperformance.wordpress.com/2010/07/21/project-houston-for-sql-azure Continue reading →]]>

Microsoft has released CTP1 of Project Houston for SQL Azure. Project Houston is a Silverlight web based database management tool that lets you manage and develop SQL Azure databases using a web browser. You have to go to SQL Azure Labs to access this CTP of the tool, but it lets you connect to your regular, production SQL Azure databases just like you can with the SQL Server 2008 R2 version of SSMS. Microsoft’s David Robinson has some more detail here.

I tried it out for a bit tonight, and the first thing I noticed was that the Silverlight control for the initial login screen does not let you paste the SQL Azure server name into the control. This is a little annoying, since SQL Azure has quite long server names! Update: It turns out you can paste into the Silverlight control, using Cntl-V. You just cannot right-click and choose paste from a context menu. I simply jumped to the wrong conclusion when I did not see the context menu.

Below, you can see the Start Page. The square box in the middle is actually a cube that you can spin back and forth with the arrows to either side. It displays different information on each side of the cube.

For day to day use, I think SSMS would be a better choice, but I could see some scenarios where a web based tool could be handy.

I have been playing around some more with SQL Azure in order to get ready for an upcoming presentation, so I thought I would share some of the T-SQL commands in the script below. I have a partial copy of the sample AdventureWorksLT2008R2 database installed on my SQL Azure logical “server” in the Southern US data center before this demo starts. You need at least the November CTP of SQL Server 2008 R2, in order to talk to SQL Azure with SSMS.

I have mainly just trying different commands to see what works and what does not work in SQL Azure, so I hope you find this interesting and useful.

-- Trying out SQL Azure
-- Glenn Berry
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry


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


-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE AdventureWorksLT2008R2;
GO


-- ***  Connect to AdventureWorksLT2008R2 database ***


-- Create a table and populate it

--Drop table if it exists
IF OBJECT_ID('dbo.UserAccount', 'U') IS NOT NULL
  DROP TABLE dbo.UserAccount;
GO

-- Create a table
-- Notice no file group or other options allowed in CREATE TABLE for SQL Azure
CREATE TABLE [dbo].[UserAccount](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [Password] [nvarchar](50) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [ExpireDate] [datetime] NULL,
    [FName] [nvarchar](50) NOT NULL,
    [LName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserAccount] PRIMARY KEY CLUSTERED 
([UserID] ASC));


-- Drop index if it exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserAccount]') 
            AND name = N'IX_UserAccount_UserName')
DROP INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount];
GO

-- Create NC Index
-- Notice no options allowed in CREATE INDEX for SQL Azure
CREATE NONCLUSTERED INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount] 
(
    [UserName] ASC
);

-- Insert a few rows
INSERT INTO dbo.UserAccount(UserName, [Password], CreateDate, [ExpireDate], FName, LName)
VALUES('GlennBerry', 'testpassword', GETDATE(), '12/31/2010', 'Glenn', 'Berry'),
      ('JessicaAlba', 'testpassword', GETDATE(), '12/31/2010', 'Jessica', 'Alba'),
      ('MirandaKerr', 'testpassword', GETDATE(), '12/31/2010', 'Miranda', 'Kerr');


-- See what we have in the table (notice GETDATE() actually returned UTC Date)
SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
FROM dbo.UserAccount;

-- Get the UTC Date
SELECT GETUTCDATE() AS [UTC Date];


-- This works in SQL Azure
EXEC sp_HelpIndex [dbo.UserAccount];
EXEC sp_HelpIndex [SalesLT.Customer];
EXEC sp_HelpIndex [SalesLT.CustomerAddress];

-- This works in SQL Azure
EXEC sp_Help [SalesLT.Customer]


-- Stop the rowcount messsages
SET NOCOUNT ON;

-- Return IO statistics
SET STATISTICS IO ON;

-- Turn on graphical execution plan

-- Run a simple SELECT query
SELECT UserID, UserName, [Password], CreateDate, 
[ExpireDate], FName, LName
FROM dbo.UserAccount;

-- Run a simple SELECT query
SELECT CustomerID, NameStyle, Title, FirstName, MiddleName, 
LastName, Suffix, CompanyName, SalesPerson, EmailAddress, 
Phone, PasswordHash
FROM SALESLT.Customer;


-- Run a simple SELECT query with a WHERE clause
-- Notice SQL Server 2008 syntax works for DECLARE
DECLARE @CustomerID int = 1; 

SELECT CustomerID, NameStyle, Title, FirstName, MiddleName, 
LastName, Suffix, CompanyName, SalesPerson, EmailAddress, 
Phone, PasswordHash
FROM SALESLT.Customer
WHERE CustomerID = @CustomerID;


-- Create a stored procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserInfoByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserInfoByID]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUserInfoByID]
(
    @UserID int
)
AS
    BEGIN
        
        SET NOCOUNT ON;

        SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
        FROM dbo.UserAccount
        WHERE UserID = @UserID;
        
        RETURN;
        
    END
GO

-- Call the stored procedure
EXEC dbo.GetUserInfoByID 1;
EXEC dbo.GetUserInfoByID 2;





-- Must connect to master database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE [Master];
GO

-- *** Connect to master database ***


-- Get firewall rules
SELECT id, name, start_ip_address, end_ip_address, 
create_date, modify_date 
FROM sys.firewall_rules;


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

-- Refresh SQL Azure Portal web page to see change

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


-- Create an "Admin" login and user in the SQL Azure instance

-- List all logins on "instance" (must be connected to master)
SELECT *
FROM sys.sql_logins;

-- Cleanup if necessary
DROP LOGIN TestLogin;
GO
DROP USER TestLoginUser;
GO

-- Create a logon with a "strong" password that
-- can create databases and other logins
CREATE LOGIN TestLogin WITH password='1994Acura#';
GO
-- Create a database user that is linked to login
CREATE USER TestLoginUser FROM LOGIN TestLogin;
GO
EXEC sp_addrolemember 'loginmanager', 'TestLoginUser';
EXEC sp_addrolemember 'dbmanager', 'TestLoginUser';  -- like dbcreator


-- List all logins on "instance" 
SELECT *
FROM sys.sql_logins;

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

-- Drop database if necessary
DROP DATABASE TestDatabase;

-- Create new database on SQL Azure "instance"
CREATE DATABASE TestDatabase;


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

-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE TestDatabase;
GO

-- ***  Connect to TestDatabase ***

-- Create a table
CREATE TABLE [dbo].[TestTable](
    [TestID] [int] IDENTITY(1,1) NOT NULL,
    [TestName] [nvarchar](50) NOT NULL,    
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
([TestID] ASC));

-- Insert a few rows into table
INSERT INTO dbo.TestTable(TestName)
VALUES('Test1'),
      ('Test2'),
      ('Test3');
      
-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;


-- This does not work in SQL Azure
DBCC FREEPROCCACHE;

-- This does not work in SQL Azure
DBCC FREEPROCINDB(5);

-- This does not work in SQL Azure
DBCC DROPCLEANBUFFERS;


-- ***  Connect to AdventureWorksLT2008R2 database ***

-- Run a stored procedure
EXEC dbo.GetCustomerInfoByFirstName N'James';
EXEC dbo.GetUserInfoByID 2;


-- Run some DMV queries against the database

-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;


-- Monitor connections
SELECT s.session_id, s.login_name, e.connection_id,
      s.last_request_end_time, s.cpu_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS e
ON s.session_id = e.session_id;

-- Find top Avg CPU time queries
SELECT TOP (5) 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 2 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;


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



-- DMVs that are available 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; 


-- Drop the Stored procedure
DROP PROCEDURE dbo.GetUserInfoByID;

-- Drop the table
DROP TABLE dbo.UserAccount;

-- Drop the database
DROP DATABASE TestDatabase;

-- Drop the login
DROP LOGIN TestLogin;

]]> https://sqlserverperformance.wordpress.com/2010/03/04/more-sql-azure-samples/feed/ 3 152 guderian1961 https://sqlserverperformance.wordpress.com/2010/02/18/testing-new-features-in-sql-azure-service-update-1/ https://sqlserverperformance.wordpress.com/2010/02/18/testing-new-features-in-sql-azure-service-update-1/#respond Fri, 19 Feb 2010 06:58:43 +0000 http://sqlserverperformance.wordpress.com/2010/02/18/testing-new-features-in-sql-azure-service-update-1 Continue reading →]]>

It seems to be SQL Azure night for me tonight…  I thought I would try out some of the new features in SQL Azure Service Update 1. First I tried ALTER DATABASE to switch between the Web Edition and Business Edition size for a SQL Azure database. I discovered that you have to be connected to the master database in your SQL Azure “instance” (which makes sense). Remember, you cannot just run USE master, or switch the database context in the dropdown in the toolbar in SSMS. You have to be using the November CTP version of SQL Server 2008 R2 SSMS to work with SQL Azure in SSMS.

You have to change the connection to connect specifically to the database that you want (which is a little inconvenient). Then you can run your ALTER DATABASE command. I refreshed the Server Administration web page in the SQL Azure portal to confirm that the database edition had changed.

Then, I tried running the six DMV queries, and they all worked just fine. Finally, I waited more than five minutes (but less than 30 minutes) in SSMS, and verified that SQL Azure did not drop my connection. Everything worked just fine for these three new features.

-- Testing new features in SQL Azure Service Update 1
-- Glenn Berry
-- 2/18/2010

-- Must connect to master database first
-- Remember, there is no USE databasename allowed
-- Switch to Business Edition
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 10 GB);

-- Switch to Web Edition
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 1 GB);

-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- Test new DMV queries in SQL Azure

-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], *
FROM sys.dm_db_partition_stats; 

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; 

]]> https://sqlserverperformance.wordpress.com/2010/02/18/testing-new-features-in-sql-azure-service-update-1/feed/ 0 168 guderian1961 https://sqlserverperformance.wordpress.com/2010/02/18/sql-azure-database-service-update-1-released-on-february-17/ https://sqlserverperformance.wordpress.com/2010/02/18/sql-azure-database-service-update-1-released-on-february-17/#respond Fri, 19 Feb 2010 03:51:43 +0000 http://sqlserverperformance.wordpress.com/2010/02/18/sql-azure-database-service-update-1-released-on-february-17 Continue reading →]]>

Microsoft’s David Robinson has announced on the SQL Azure Team Blog that they have released Service Update 1 for SQL Azure. This update has four main changes:

First, they have enabled  six Dynamic Management Views (DMVs), which will allow you to have a little more insight into what is going on with your SQL Azure databases. Here is the list of what is enabled now.

· sys.dm_exec_connections – This view returns information about the connections established to your database.

· sys.dm_exec_requests – This view returns information about each request that executes within your database

· sys.dm_exec_sessions – This view shows information about all active user connections and internal tasks.

· sys.dm_tran_database_transactions – This view returns information about transactions at the database level.

· sys.dm_tran_active_transactions – This view returns information about transactions for your current logical database.

· sys.dm_db_partition_stats – This view returns page and row-count information for every partition in the current database.

Second, you can switch back and forth between the Web and Business Edition database editions (at 1GB and 10GB sizes, respectively), with a simple ALTER DATABASE T-SQL command.

Third, they have increased the idle connection timeout from five minutes to 30 minutes. This will make normal, interactive development in tools like the 2008 R2 version of SQL Server Management Studio (SSMS) much less painful.

Finally, they have relaxed the requirements regarding long running transactions in order to make it easier to do things like bulk loads into SQL Azure.

These are all welcome changes that make SQL Azure easier to work with very soon after the initial release.