Mar 25 2016

Happy Dance!

I’m all like:

snoopy-dance

Because I saw this on an eval:

I’ve been trying to ramp up to take advantage of my MSDN subscription and haven’t known where to start. I don’t have that excuse now.

And then I was all like:

Happy_Dance

Because:

We are moving a lot of stuff to Azure. I had some experience using SQL Azure but felt blind when doing it. Grant made me feel better about my experience as it is very much like he explained.

and:

Azure is becoming a REAL THING. It’s nice to get such a great primer of it.

<calming down>

I’m quite pleased to see that Azure sessions are getting such an improved reception.

<SQUEEE>

Mar 21 2016

Cross Database Query in Azure SQL Database

You can’t query across databases in Azure SQL Database… or can you?

Let’s check. I’ve created two new databases on an existing server:

dblist

I’ve created two tables on each respective database:

CREATE TABLE dbo.DB1Table (
     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)
    );


CREATE TABLE dbo.DB2Table (
     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)
    );

Now, let’s query the DB2 table from the DB1 database:

SELECT  *
FROM    DB2.dbo.DB2Table AS dt;

And here’s the lovely error message:

Msg 40515, Level 15, State 1, Line 35
Reference to database and/or server name in ‘DB2.dbo.DB2Table’ is not supported in this version of SQL Server.

So, like I said, you can’t do three part name cross-database queries in Azure SQL Database… oh wait, that’s not quite what I said is it. Let’s do this. Let’s create a new security credential within DB1 for a login that can get us into DB2:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'securitymatters';

CREATE DATABASE SCOPED CREDENTIAL DB2Security 
WITH IDENTITY = 'Grant',
SECRET = 'securitymatters';

Then, we’ll use that to define an external data source:

CREATE EXTERNAL DATA SOURCE DB2Access
WITH (
	TYPE=RDBMS,
	LOCATION='myservernotyours.database.secure.windows.net',
	DATABASE_NAME='DB2',
	CREDENTIAL= DB2Security);

With this, we can put Elastic Query (corrected from Polybase see note below) to work and create an external table:

CREATE EXTERNAL TABLE dbo.DB2Table (
	ID int,
	Val varchar(50))
WITH
(
	DATA_SOURCE = DB2Access);

And that’s it. If I query dbo.DB2Table from DB1, I get to see data in DB2. In short, you can do a cross database query within Azure SQL Database. Yeah, it’s going to require some setup and possibly some code modifications since you can’t use the old three part naming for performing the query, but, you can do it. Further, note that these are Standard, not Premium databases. Further further, they’re not a part of an elastic pool. It’s just using the external data source and external table to connect the two databases. However, if the one thing keeping you from moving into Azure SQL Database is the ability to query across databases, that’s gone.

 

Feb 29 2016

Wait Statistics in Azure SQL Database

You need to be aware that you’re going to see differences when you’re working with Azure SQL Database when it comes to wait statistics. If you’re running a v12 Azure SQL Database (and if you’re not, go fix it), you can run a query against sys.dm_os_wait_stats. Here’s the output for the TOP 10 waits ordered by wait time on one of my databases:

waits_os

Everything you’re used to seeing, right? Well… not quite. This is Azure SQL Database. So, let’s use sys.dm_db_wait_stats, a DMO that is only available within Azure. This lists waits by database. The TOP 10 here are:

waits_db

You’ll notice that these results are wildly different from those above. What we’re looking is largely a server versus a database, but not completely. I mean that sys.dm_os_wait_stats is showing the waits for the instance on which my primary Azure SQL Database is currently running. Most of those waits are mine, but because it’s part of the management structure of Azure, sys.dm_os_wait_stats shows some information that’s not applicable, directly, to me. The “server” is not really that. It’s a logical container holding your database. There’s a lot more to it under the covers. To get the waits that are absolutely applicable to me and my databases, I have to go to sys.dm_db_wait_stats.

It goes further. One of the options for sys.dm_os_wait_stats is the ability to reset the waits. You have to call a DBCC command to do it:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

However, if you run this on a Azure SQL Database, you’re going to get an error:

Msg 297, Level 16, State 11, Line 15
The user does not have permission to perform this action.

Makes sense. Azure SQL Database is a Platform as a Service. You don’t have access to the server level objects to make these types of changes.

So, we’ll fix it. I want to reset the database wait stats:

DBCC SQLPERF ('sys.dm_db_wait_stats', CLEAR);

This results in:

Msg 2526, Level 16, State 12, Line 15
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

If you read the documentation, you can’t reset the wait stats for the database. However, the wait stats will be reset if your database goes through a failover, which is actually hard to detect (still trying to figure this out actually). You can’t currently control the reset.

With the understanding of what you’re seeing, you can use sys.dm_db_wait_stats in the same way you would sys.dm_os_wait_stats.

Oh, and if you’re looking to filter the unnecessary wait stats, Paul Randal’s script works fine. Just substitute ‘db’ for ‘os’ and you’ll be happy.

EDIT: Just so we’re clear, sys.dm_db_wait_stats shows you just the waits in a single database. The database you’re running them from. Further, this DMV is only available in Azure SQL Database. I hope that helps the clarity of the message a little.

EDIT2: I’ve changed the description after some conversations. I think I’m closer to reality based on what I’ve been told and Jeremiah’s comment below. It’s hard because I’m explaining something I don’t directly support because I don’t work at Microsoft.


For lots more information on query tuning, I’m presenting an all day pre-con at SQL Day in Wroclaw Poland on May 16.

Feb 18 2016

Azure SQL Database Error

I was on SQL Cruise where I was scheduled to present a session on Azure SQL Database. I recorded all my demonstrations before we went to sea, but, I planned to attempt to try live demo’s. Yeah, yeah. Well, anyway, I got a unique error when I attempted to connect:

Forced connection closes from remote host

That’s a partial message from the whole error. I don’t have a good screen capture. I wasn’t able to find anything on it through Ging searches, but this week I was at Microsoft for a training course on Azure. I asked the room. The rough answer is (paraphrasing):

The IP address I was attempting to connect from is not is not on the approved list

Interesting. I didn’t realize there were blackout zones. The really interesting thing was, this only occurred when I attempted to connect through SQL Server Management Studio. I was able to connect through the Azure Portal. One more wrinkle in the interesting challenges offered by Azure and the Data Platform.

Jan 25 2016

Finding Your Query in Query Store

Query Store is pretty amazing. I’m loving working with it. I think it’s likely to change how query tuning will be done in the future. Lots of people are probably going to just use the reports and tools in SQL Server Management Studio. However, a pretty healthy chunk of us will start using the system views in order to programmatically access the information stored in Query Store. One of the first things you’re going to want to do is track down your query.

The primary views you’ll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let’s take four scenarios and see if we can retrieve the correct query. First up, an ad hoc query:

SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = 'Hamilton';

If we wanted to retrieve this from the Query Store AdventureWorks2014, we’d run a query like this:

SELECT * FROM sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text = 'SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = ''Hamilton''';

Of note, I had to drop the statement terminator from the text of the query, the semi-colon, in order to retrieve the correct query. That’ll be good to know in a moment. This retrieves the query information I requested, just fine.

For our next example, let’s simplify things a whole bunch:

SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;

If I then attempt to retrieve the information from Query Store like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363';

It actually doesn’t work. Note, I took off the statement terminator, just like before. In fact, the problem here can be identified if we look at the T-SQL from the execution plan from the second example:

SELECT * FROM [Production].[BillOfMaterials] [bom] WHERE [bom].[BillOfMaterialsID][email protected]

This query has gone through simple parameterization. So, in order to retrieve the information from Query Store, we have a function, sys.fn_stmt_sql_handle_from_sql_stmt that we have to incorporate like this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
CROSS APPLY sys.fn_stmt_sql_handle_from_sql_stmt('SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;', qsq.query_parameterization_type) AS fsshfss

And note, I have the statement terminator back in place, but this function takes that into account. I joined to the query_store_query table in order to get the parameterization type value. With the function figuring out the statement handle based on the text I originally ran the query through, everything is hunky dory.

Let’s look at one more example:

EXEC dbo.spAddressByCity
    @City = N'London';

This is a stored procedure, so I can just do this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
WHERE   qsq.object_id = OBJECT_ID('dbo.spAddressByCity');

However, you may have multi-statement stored procs, so you might want to query based on the text within the procedure like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City'

Bad news. That doesn’t work. If you looked at the qsqt.query_sql_text value in the previous Query Store query using the OBJECT_ID function, you’ll see that the query looks like this within the Query Store:

(@City nvarchar(30))SELECT  a.AddressID,          a.AddressLine1,          a.AddressLine2,          a.City,          sp.Name AS StateProvinceName,          a.PostalCode  FROM    Person.Address AS a  JOIN    Person.StateProvince AS sp          ON a.StateProvinceID = sp.StateProvinceID  WHERE   a.City = @City

But, if you look at the documentation for sys.fn_stmt_sql_handle_from_sql_stmt that I linked above, it only works with simple or forced parameterization (this, despite having options for none and user, 0 & 1 respectively in the documents). That means you may be resorted to the use of LIKE to retrieve particular statements:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City%';

At least, that’s where we’re at with the current, public, CTP.

Tracking down your query can be a little bit of work and hopefully these tips will make it a little easier.

Jan 18 2016

Removing All SQL Server Query Store Data

While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn’t there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag.

Jamey Johnston (t|b) came through… and it was right there in the documentation that I had been reading, over and over. In fact, it was in the documentation in two different places. Reading is clearly a problem for me today.

Just so that you know, it’s actually really easy:

ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR;

By the way, Query Store is absolutely amazing. Did you know that you can set up an extended event to fire when Query Store detects a query regression? Yep. The extended event query_store_aprc_regression will do just that. Query Store is coming with SQL Server 2016, but it’s already in production in Azure SQL Database.

Oh yes, we’re moving into some very interesting times for query tuning. Now if I can just get my reading comprehension to keep up…

Jan 12 2016

“Applies To…” in the MSDN Documentation

Quick little post. I just wanted to share how happy I am with the new “THIS TOPIC APPLIES TO” infographic. An example here:appliesto

I think it makes things much more clear when you’re attempting to figure out what’s up with some T-SQL syntax.

Well done Microsoft and thank you.

Side note, this only exists in documentation that has been updated recently. I first saw it in some documentation that was updated January 11, 2016. It’s not there in another piece of documentation I saw that was updated October 15, 2015. Here’s hoping it gets put everywhere. It works.

Jan 11 2016

Restoring a Database in Azure

One of the many small things you don’t have to worry about any more when working with Azure SQL Database are those pesky backups… Ha-ha-ha! Yeah, I can’t keep a straight face when I type it. Microsoft takes on the role of DBA and creates backups and log backups for you when you’re working in the Platform as a Service offering of Azure SQL Database. If that doesn’t scare you, I’m not sure what will. However, there’s good news. There are still ways for you to create your own backup (through the bacpac, I outlined it here, years ago). More good news is, Microsoft’s backups actually work. Testing it out is easy. Let’s walk through it once.

I’m going to assume you have an Azure account on which you already have Azure SQL Databases created. If you navigate to one of your databases, the screen, in the new portal, looks something like this:

azuredb

If you look at the tool bar at the top, you can see the command for “Restore.” Clicking on that opens another window:

AzuredbRestore

The first, and most important thing to notice here is that it’s supplying me with a new name. I can change that to anything I want as long as it’s not the name of a database already in existence on my Azure SQL Database Server. You read that correctly, you can restore a database to Azure SQL Database, but there is no WITH REPLACE option. The restore creates a new database. This is important. In a recovery scenario, you need to be sure that you’re prepared to deal with this fact. How could you replace the existing database? Immediately run a pair of ALTER DATABASE commands to change the name of the existing database to something else and then change the name of your newly created database to the old name. That’s your choice.

The other options and information are pretty clear. You can decide on the server you’d like to restore to, allowing you to move or copy a database to another server. You get the current date and time. More importantly, you get the oldest restore point. This is determined by the service tier that this database is currently defined on. Different service tiers have different SLAs from Microsoft on how much backups are kept around. At the bottom you can pick the date, within the range of backups you have available, as well as the hour and minute. At the bottom of the screen, not shown, you can then click Create. That’s right, Create, not Restore. Why? Because, as I already said, the restore operation is creating a new database.

I’ve tested this a number of times with databases from various tiers. It works and works well. I will caution you on another point. Let’s say you want to restore just, say, a couple of rows of data or a table, not the entire database. You’re going to have to get imaginative with how you deal with that because, let’s say I run this query:

SELECT  *
FROM    [CruiserDirectory_2016-01-07T09-04Z].dbo.table3;

The results I get are an error:

Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in ‘CruiserDirectory_2016-01-07T09-04Z.dbo.table3’ is not supported in this version of SQL Server.

Because, you can’t do cross-database queries in Azure SQL Database.

2f31427My best suggestion here, take a look at a third party solution that allows you to compare the data between two databases, even in Azure, and then provides you with the ability to move those rows or tables that you need. Hint, hint, nudge, nudge, wink, wink, SAY NO MORE! You can then capture the data from one database and move it into the other pretty easily.

That’s it. Azure SQL Database does provide a very simple mechanism for getting at your backups that are automatically created for you. That’s pretty cool.

 

Dec 03 2015

Changing Course On Learning

With all the new stuff on the Microsoft Data Platform, it’s really hard to keep up with it all. I had announced my plans to charge down the DocumentDB road to try to get the basics of that in my head along with learning some JSON so I could get what all the hoopla is about.

However, after a lot of thought and some extensive meetings at Redgate, I’m looking to shift my learning in a new direction.

First up. Arrrrrrrrr!

No, it’s not yet “Talk Like a Pirate Day.” I’m going to start learning the R language. It’s a language for statistical computing and is one of the many underpinnings for what’s going to be happening with a lot of the Machine Learning capabilities in the Data Platform. With Azure SQL Database, and soon, SQL Server 2016, this new language is going to be part of the query landscape. It’s going to cause performance issues and all kinds of wonderful opportunities. I need to know it.

I’m also looking to embrace and extend my knowledge into the Machine Learning area. I’m not sure exactly where that’s going to take me, but again, I’m pretty sure we’re going to see more and more of this within the systems we manage.

With so much of the data stack now available through Azure (Azure SQL Data Warehouse is a game changer and you should be looking at that right now, in your spare time) changing not only what we can do, but how we do it, it’s affecting directly SQL Server. It’s not enough to know and understand just the core engine (it never really was, but we could tell ourselves that). This doesn’t just affect queries and query tuning. It has impact into our Data Lifecycle Management, DevOps and development releases and methods. In short, all the stack is getting impacted by the expanding Data Platform and I intend to be on top of it.

Watch for the R posts coming up, and forgive me if I occasionally sound a little piratical (OK, a little MORE piratical). Also, don’t worry. You’re still going to see stuff on query tuning, execution plans and all the core engine stuff. Fact is, that doesn’t go away just because I’m looking at Azure SQL Database or Azure SQL Data Warehouse or attaching R to my T-SQL, because, under the covers, it’s still SQL Server.

Oct 05 2015

Trace Flags in Azure SQL Database

One of the ways that you take more direct control over your SQL Server instances is through the use of trace flags. There are a number that people recommend you enable by default. Prior to Extended Events for example, I’d say you should turn on trace flag 1222 in order to capture deadlock information on your server (now I just recommend you use the system_health session). I absolutely think you should turn on trace flag 2371 to get better behavior out of your automated statistics updates. There are others that I’ll leave to all the systems experts to advise you on.

What about Azure SQL Database?

I doubt you’ll be shocked, but if I try this:

DBCC TRACEON (2371,-1);

I get the following error:

Msg 2571, Level 14, State 3, Line 1
User ‘xxx’ does not have permission to run DBCC TRACEON.

 

This error makes sense right? We’re talking about a Platform as a Service (PaaS). You’re only managing the database, not the server, so you don’t have access to control underlying server behavior.

How about if we want to just modify the behavior of a query? You can use the query hint QUERYTRACEON to adjust behavior. For example, the new statistics cardinality estimation engine in 2014 and better is just marvelous. It’s in use in Azure SQL Database. However, there are edge cases where the old way can work better for certain queries. If you want to go to the old cardinality estimation engine in SQL Server 2014/2016, you use traceflag 9481 in a query hint like this:

SELECT  *
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE   sod.OrderQty > 30
AND sod.ProductID = 867
OPTION (QUERYTRACEON 9481);

Bad news. The error message is the same.

Working within Azure SQL Database, trace flags are not a part of your tool set.