I recently presented at SQL Saturday 184 in North Haven and was able to listen to Grant Fritchey’s present “Query Tuning in the Clouds”. What I got out of the presentation was the sense all your tuning skills could be used to optimize a Windows Azure SQL database.
I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications?
Today, I will be reviewing the sample [AUTOS] database using the web interface supplied with the Azure Database. The most important thing to keep track of after setting up a database on Windows Azure is the URL for your database. Placing this URL into the address bar on Internet Explorer will bring up the login page.
We need to enter a user name, user password and optional default database to log into the server. This management page is the command center of web interface. The overview tab has various links to Microsoft resources. It is showing two databases, [AUTOS] and [MATH], defined on the server.
The administrative tab shows the existence of two databases. Since we did not log in with a default database, we need to select a database to work with.
After double clicking the [AUTOS] database, we are placed into the summary page. This page shows database maximum size, database current size, sort collation, and connected users to name a few.
The query performance page is a report that shows typical information that can be queried from the dm_exec_query_stats dynamic management view. This view gives performance statistics of query plans in the cache.
As a database administrator, this is a quick way to identify queries that take a long time to run or queries that run many times. Both types of queries can be good candidates for optimization which reduces the total resources used by the Azure server.
The design page allows us to CREATE (ADD), ALTER (EDIT), and DROP (DELETE) tables, views, and stored procedures. There is also the ability to compose and execute ad-hoc queries. This page defaults to listing the table objects in our database.
As we can see below, we have three tables and fourteen rows of data.
I chose to select the [MODELS] table in the [ACTIVE] schema. Clicking the edit icon brings us to the table menus. We can work with columns, indexes/keys or data that is part of the table. The image below shows us in the columns page.
The indexes and keys page is graphical in nature. We see the table in the middle of the page with indexes on the left and foreign keys on the right. Again, we can add, edit or delete these objects inside this page.
What is a table worth without storing and manipulation data stored within? The data page is a data grid that can be modified by clicking the field of interest. Just like editing data in SSMS, any constraint violations will prevent the data from being committed.
So far, we can do many of the table tasks that we did in SSMS with a local database quite well in the Web Interface with a Azure database.
My only complaint is the missing storage details that break the table down into data and index pages used.
If we navigate back to the design page and chose the view page, we can get a listing of views in our database. We see the one and only view in the [AUTOS] database.
The design views page is where we can alter this existing T-SQL code. There is no intellisense on this page. Thus, the syntax for the view has to be correct. Upon saving the changes, the query will be compiled and any errors will show up.
The views data page is where we can select from the view and look at the result set. This result set is read only. I would like to see is Azure SQL reacts to an DML statement.
If we navigate back to the design page and chose the stored procedures page, we can get a listing of stored procedures in our database. We see the one and only stored procedure in the [AUTOS] database.
Last but not least, we can use the design stored procedures page to edit our T-SQL code. Again, there is no intellisense on this page. However, no code changes were necessary for example to work in the cloud.
The stored procedures data page is dependent upon how the code is defined. If we need to pass parameters to the procedure, we can define them in this window. The run button at the top of the page will execute the stored procedure. Any results sets will appear as data below.
The new query button that appears at the top of design pages allows the designer or administrator to create an ad-hoc query. The screen shot below, the query design page, shows a simple SELECT statement on the [MODELS] table in the [ACTIVE] schema. We can open an existing query file, save the query to a disk file or run the query to see its results.
Both the estimated and actual query plans are displayed. Like Grant Fritchey stated in his presentation, most if not all of your query tuning knowledge can be leveraged in the cloud. We can see that a clustered index scan is used to return the data.
The screen shot below shows the results from executing the query. All nine rows are returned from the table.
In a nutshell, many of the tasks that you can do in SSMS can be done with the WEB INTERFACE.
Here is a list of complaints that I have with the web interface:
- There are no web pages that break a table down into data and index pages. We can select from the dm_db_partition_stats, one of the dynamic management views to monitor SQL azure database, but both data and index pages are wrapped into one.
- There are no web pages to manipulate user defined functions.
- There are no web pages to manage logins and users as well as granting, revoking and denying rights. This has to be done via T-SQL queries.
In summary, if you do not want to worry about managing a physical (local) SQL Server database, a Windows Azure (cloud) SQL Database might be the ticket you are looking for. However, please note that SQL Azure does not support the full implementation of SQL Server 2012 Data Engine.
Here are some useful links if you are considering Windows Azure SQL database.
Windows Azure Data Storage – SQL Database, Table Storage, and Blob Storage
Azure SQL Database – This is the online reference page.
SQL Azure Team Blog – Learn from the team that built and supports this product.