Monday, December 13, 2010

SQL AZURE - A Warning to Those Using sys.dm_exec_query_stats

Today, I read Dinakar Nethi's blog post on Troubleshooting and Optimizing Queries with SQL Azure. In that article, he discusses how we can use SQL Azure DMVs to troubleshoot poor performing queries. He relies heavily on sys.dm_exec_query_stats DMV, which I use all the time with on-premise SQL Servers.

While reading it, I remembered reading a blog post from Adam Mechanic about how if there is an alter statement in a batch, sys.dm_exec_query_stats will not pick up any of the statements in the batch. This might be because some DDL statements do not generate query plans, but I'm not sure.

I wanted to see if SQL Azure had the same issue. I was hopeful it wouldn't because they change the implementation of many of their views. Nope...the issue is still there in SQL Azure. I re-wrote Adam's script, because SQL Azure does not support SELECT..INTO. Also, tables in SQL Azure require a primary key.

Here's the script:


--BATCH #1
--In this batch, the select does not appear in sys.dm_exec_query_stats

create table #x
(a int primary key
, b varchar(100))

--This is the alter statement that causes the issue.
alter table #x
add unique (a)

insert into #x
values
  
(1, 'getstuff')
, (
2, 'getMoreStuff')
, (
3, 'getEvenMoreStuff')

select *
from #x

select *
from sys.dm_exec_query_stats
where    plan_handle IN    
(        
select            plan_handle        
from sys.dm_exec_requests        
where            session_id = @@spid    )

drop table #x

GO

--BATCH #2
--In this batch, the select does appear in sys.dm_exec_query_stats,
--because I change the alter table to a create index statement

create table #x
(a int primary key
, b varchar(100))

--I change the alter statement to a create unique index statement.
CREATE UNIQUE INDEX whatever
ON #x (b)

insert into #x
values
  
(1, 'getstuff')
, (
2, 'getMoreStuff')
, (
3, 'getEvenMoreStuff')

select *
from #x

select *
from sys.dm_exec_query_stats
where    plan_handle IN    
(        
select            plan_handle        
from sys.dm_exec_requests        
where            session_id = @@spid    )

drop table #x

Sunday, December 5, 2010

Day Of Azure II Talk - SQL Azure Survey

Lynn and I had a blast speaking at the Day of Azure. We covered SQL Azure as thoroughly as possible in 2 hours. You could probably tell that we could have spent two days on our topic....maybe next time. Here are the slides:

Thursday, December 2, 2010

A Lap Around SQL Azure Data Sync - Step By Step Example

SQL Azure Data Sync is a cloud-based service, hosted on Microsoft SQL Azure, which allows many SQL Azure databases to stay consistent with one another. This capability is ideal for a project I’m currently working on where the users need low latency data access across an entire company but they are geographically separated by great distances with one office in USA and one in Europe. SQL Azure Data Sync allows them to have consistently synchronized data physically close to both locations.

SQL Azure Data Sync is super simple to setup (try saying that five times fast). In this article, I’ll show you how to implement a data synchronization between two databases on the same logical SQL Azure server. We’ll be implementing the design in the diagram below.




STEP 1 – Signup for SQL Azure
If you need some guidance on how to do that, follow this link:
SQL Azure Signup Tutorial

As of this writing (AOTW), Microsoft is literally giving SQL Azure away (Web Edition – 1GB), so you really have no excuse on why you haven’t signed up yet.

STEP 2 – Create Two Databases
OK, here’s where you might get charged. For this example, we’ll create two databases that will sync with each other, and you might get charged when you add the second database. When we're finished, you may want to delete one or both databases to keep costs down. Database usage is metered per day, so your bill should be less than $1, but I haven’t confirmed that.

You create a database by following these steps:

a) Go to http://sql.azure.com. Click on the project you created in Step 1.




b) First you’ll see just the Master database. That doesn’t do us much good, since SQL Azure doesn’t allow us to add tables to the Master Database.




We need two more databases. Click on “Create Database.” The dialog below will pop up. Type in library1, and click “Create.” Do the same for the library2 database.




When you are finished, your console should look like this:





Now you have two blank databases. Let’s add some schema and data to library1, then we'll configure SQL Azure Data Sync to copy them over to library2.

STEP 3 – Add Schema and Data to Library1.

We’ll use SQL Server Management Studio (SSMS) to add the schema and data, though we could have easily used Visual Studio, SQLCMD, or even BCP.

Connect to SQL Azure using your server name, user name, and password. Don’t worry, it’s all encrypted when connecting to SQL Azure. Make sure your internal firewall allows port 1433 to pass through to SQL Azure. AOTW, SQL Azure’s TCP port cannot be adjusted, so don’t bother trying to look up how to change it. It’s a tough break that network admins at many organizations unilaterally block that port.

Your SSMS connection dialog will look something like this:




Once you’re connected, run this script to create two tables. You’ll notice that the TITLES table references the AUTHORS table. That will be important later.


CREATE TABLE authors
(id INT PRIMARY KEY
, name VARCHAR(100) DEFAULT('')
)

CREATE TABLE titles
(id INT PRIMARY KEY
, name VARCHAR(100) DEFAULT(11)
,
authorId INT REFERENCES authors(id))

INSERT INTO authors
VALUES
  
('1', 'Stephen King')
, (
'2', 'George RR Martin')
, (
'3', 'Neal Stephenson')
, (
'4', 'Steig Larsson')


INSERT INTO titles
VALUES
  
('1', 'A Game of Thrones', '2')
, (
'2', 'A Clash of Kings', '2')
, (
'3', 'The Song of Susannah', '1')
, (
'4', 'The Gunslinger', '1')



There are a couple of things to notice about this script. One, each table in SQL Azure needs a clustered index. I’m creating one by specifying a primary key in the create table statement. AOTW, SQL Azure will allow you to create a table without a clustered index, but it won’t allow you to insert data into it, so there’s no reason to bother to even create the heap table. Two, I like the insert syntax where we can insert multiple rows after the VALUES keyword. That’s not SQL Azure specific, I just think it’s cool.

We have two databases, one filled with tables and data and one empty. Let’s fix that and get to the meat of this demo.

STEP 4 – Setup SQL Azure Data Sync.
a) Goto http://www.sqlazurelabs.com/ and click on “SQL Azure Data Sync”

b) You’ll need to sign in to Windows Live ID.

c) The first time you go to this site, you’ll have to agree to a license agreement.





d) Click “Add New”.




e) Name the Sync Group "LibrarySync". I don’t know the limit of how long this name can be, but I’ve thrown in a lot of text in there and it took it. I wonder if it’s varchar(max). Then click “Next”.

f) Register your server by typing in your server name, user name, and password. Notice how in red it says “Your credentials will be stored in an encrypted format.” This is good news because it saves your credentials when registering other databases on the same server.




g) Then click “Add Hub”. The Hub database is similar to a publishing database in replication. For instance, it will win if there are any update conflicts.

h) Then choose the library2 database and click “Add Member”. Your screen should look something like this:




i) Then click “Next”.

j) You’ll get to a screen that looks like this:




The order you select the tables in this screen is the same order that the tables will be replicated. Remember that we have a foreign key constraint, so it’s real important that we add the authors table before we add the titles table. Click “Finish".

k) OK, now it seems like you’re done, but you’re not. Click on “LibrarySync” and then click “Schedule Sync”. Notice the options you have for scheduling synchronization. You can sync hourly, daily, etc. If you choose hourly, the “Minute” dropdown does not let you schedule at a minute interval, rather it allows you to choose the minute after the hour that the sync will begin. Click “OK”.




l) Technically, your sync is ready to go, but click “Sync Now” and wait a minute or two so we can examine the changes.

Step 5 – Examine the Changes
a) Notice the library2 database has all the schema and data from library1. It also has some other things that SQL Azure Data Sync added for us. BAM! We did it!

b) Look at the 3 tables that SQL Azure Data Sync added in both databases. These tables seem to track sync and schema information.
a. Schema_info
b. Scope_config
c. Scope_info

c) Each user table gets its own tracking table. For instance, we have authors_tracking and titles_tracking. This tells SQL Azure Data Sync which records need to be updated on the other members of the sync group. Notice how this is not an auditing tool like Change Data Capture. It works more like Change Tracking in SQL Server 2008. You won’t get all the changes that led up to the final state of the data.

d) Each user table gets three triggers that are used to keep the databases consistent.

e) There are many stored procedures added to both databases for the same reason. Feel free to poke around and examine the triggers and stored procedures. I found them to be cleanly written. I like how they’re using the MERGE statement, introduced in SQL Server 2008.

f) Feel free to add a record to Library2, and click “Sync Now”. You’ll see it in Library1 in no time, thus proving that the synchronization is indeed bi-directional.

g) On the SQL Azure Data Sync Page, check out the synchronization log by clicking on the Dashboard button.

Final Thoughts
At PDC 2010, Microsoft announced that this service will extend to on-premise SQL Servers. They demo’d how it’s done through an agent that’s installed on the on-premise SQL Server. It should be available to use in CTP 2, which is not publicly available AOTW.

Also, although the initial snapshot pushed the schema down to the library2 database, it will not keep schema in sync without tearing down the sync group and rebuilding it. I recommend you finalize your schema before setting up the sync group.

This is built on the Microsoft Sync Framework. I believe we’ll be seeing this used to synchronize all sorts of data from all sorts of data sources. I think it’s worth learning and I hope this gives you a fair introduction to the technology. Remember to delete at least one of your library databases to keep from being charged. Also, delete your data sync. Good luck!

Wednesday, December 1, 2010

TIG

San Diego Technology Immersion Group (TIG) info



WHERE:
Robert Half Technology
4365 Executive Drive
Suite 450
San Diego, CA 92121

WHEN:
4th Wednesday of every month at 5:45pm, doors lock at 6pm, so don't be late.

WHAT:
We meet once a month to discuss the same book we're all reading.
Those books are chosen off from a range of topics centered on Microsoft Development Technology.
We usually get about 50 members each month.
Right now, we are studying C# and .NET for our beginner track.  We'll do that through January
Starting in February, we will study WPF, XAML, and Silverlight.
Members help each other overcome stumbling blocks while learning new technology.
In addition, we have mentors (experts) helping us understand all this new stuff.



MENTORS:
Ike Ellis
Twitter: http://twitter.com/EllisTeam1
Blog: http://ellisteam.blogspot.com
Website: http://www.ellisteam.net

Scott Reed
Twitter:  http://twitter.com/mscottreed
Blog:  http://brainhzsoftware.com/blog/

Brad Cunningham
Twitter: http://twitter.com/Foovanadil
Blog: http://www.bradcunningham.net/

Jim Bears

OTHER STUFF:
Email Ike to get on the email list so you know about future meetings.  Also, join google groups: http://groups.google.com/group/sdtig

SPECIAL THANKS:
To Robert Half Technologies for donating our meeting space.
To O'Reilly for donating our first book to all of us, Head First C#