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

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

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

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]=@1

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:

FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  a.AddressID,
        sp.Name AS StateProvinceName,
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,
        sp.Name AS StateProvinceName,
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:


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:


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


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:

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:

FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE   sod.OrderQty > 30
AND sod.ProductID = 867

Bad news. The error message is the same.

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

Apr 16 2015

Azure SQL Database v12 and SQL Magazine

I spend many of my evenings researching and writing. Sometimes it’s writing new books. Sometimes it’s fixing and rewriting old books. Occasionally it’s a blog post like this one. Lately, it’s been a series of articles for SQL Magazine that are all about the new functionality available in Azure SQL Database v12 that was released in February for most data centers in Azure. It’s a whole new ball game. Check out my introductory article for v12 and the one on DMVs in Azure. I have more coming up on CLR in Azure, getting started, PowerShell, DBCC, T-SQL enhancements, Premium Tier and more. I’ll also get into “old” functionality like backup and restore. I’ll also explore new functionality, Azure is nothing if not dynamic, as becomes widely available.

I know a lot of you have been putting off exploring Azure, especially Azure SQL Database until it was “ready.” Guess what? It’s that time. Feel free to lean on me here, and over at SQL Mag, for questions, suggestions, thoughts, concerns, anything you have about Azure.

Apr 13 2015

Azure SQL Database Firewall Settings

The new portal for managing Azure is pretty. I’m not sure I’m in love with it, but it’s pretty.

However, one thing that I have to access regularly is the firewall settings for my Azure SQL Database. I do demos from all over the place. I never know what my IP address is going to be. Rather than expose everything, I just set up whatever IP address I’m on and then remove it later. The old portal made this easy. The new one… not so much.

So, let’s get this down real quick. Assuming you connect to the new portal and go straight to your database, you’ll see this image showing you the database and the server it’s on:


You won’t see anything else that suggests FIREWALL. But, you can click on the server. When you do, you’ll see another panel open up to the right of the one you’re on. It’ll have an image like this:


Still nothing that screams FIREWALL, but if you click on the little gear icon that says SETTINGS you’ll get yet another panel opening to the right that resembles this image:


There she blows, Firewall. Click on that and you’ll see the standard screen for editing your IP address access into the firewall:


Just remember that after adding a new IP address to your firewall you must hit the save icon at the top of the screen, or you still won’t be able to access your database.

Apr 07 2015

Error: Unknown Property ismemoryoptimized

If you’re starting the process of moving your databases in Azure SQL Databast to v12, you need to do one thing. Make sure you have SQL Server 2014 CU5 or better installed on your machine with Management Studio (current list of updates). Otherwise, like me, you’ll be looking at the above error.

Just a quick blog post to help out. I saw this error, did a search, and didn’t hit a single entry telling me what to do. I started the install of CU6 (I needed to catch up on cumulative updates anyway). While that was happening, I went to Twitter and posted to #sqlhelp to see if anyone else had hit this. I got a response from Amit Banarjee pointing me to this MSDB blog on the topic, reinforcing the decision I had already made. I just wish they had posted the error along with everything else in the blog post. It would make things easier.

Mar 25 2013

Execution Plans in Azure SQL Database

Microsoft has stated pretty clearly that they’re putting code on Azure first, ahead of the desktop. Which makes one wonder when we’re going to start to see some of this cool stuff within SSMS. What cool stuff you ask? Well, let me explain. Let’s start with a query:

SELECT	m.MovieName,
FROM	dbo.Movie AS m
		JOIN dbo.MovieStage AS ms
		ON m.MovieId = ms.MovieID
		JOIN dbo.MovieStageDefinition AS msd
		ON ms.MovieStageDefinitionId = msd.MovieStageDefinitionId
		JOIN dbo.StageType AS st
		ON msd.StageTypeId = st.StageTypeId
WHERE	m.MovieId = 42;

When I run this on Windows Azure SQL Database (WASD) I get the following execution plan:

Azure Execution Plan

Kind of weird, kind of useful, right? First thing new that I can do is zoom in using that slider bar you seen in the lower left and then graphical part of the plan looks like this:

Azure Execution Plan Zoomed In

The icons have shifted into the corner and you get the costs of every operator. You also get text describing what the operator does. You can scroll around to see the other operators. Pretty cool stuff. Yeah, I know you can zoom in & out on SSMS, but it doesn’t modify the icons in any way, making viewing it zoomed out all but useless. WASD gets better. Notice the tool bar on the left. I have it here in full size so you can see everything:


Now things get fun. First, at the top, there is a little arrow pointing to the left. You can hide this tool bar by clicking anywhere within it that’s not on one of the icons. The arrow is indicating the possibility of hiding the toolbar. Once hidden, the arrow changes to pointing to the right. Clicking again expands the toolbar. Immediately below that arrow, you see the really fun stuff: Sort by:. You’ll notice that Total is currently selected. This shows me the total estimated cost for each operator within the plan. But, I can modify the display of the plan so that I only see estimated CPU cost or estimated IO cost. The following graphic shows the same plan with CPU selected. See how the estimated cost percentages have all shifted to show the different highlights?


This is a great way to rearrange the view of the plan, bringing out different information. The only beef I have with it is that it doesn’t really sort the operators so much as change the display. I understand what they mean, but I think it’s misleading. Anyway, useful, but I’ve got more. Moving down the tool bar we find the great part: Find by:. Currently on the plans above you can see that None is selected. But what if I select a different operator such as Seek:


Now, each instance of the operator type I selected is highlighted in blue, but, the sort totals for estimated costs are still displaying the numbers in red, so I haven’t lost any of the display that I’ve set up so far. This is wonderful stuff. As you can see, you only get a few operator types, Warnings, Scan, Seek, Merge Join, Hash Match, Nested Loops and Sort. But it’s a great way to begin to explore the plan in ways that we can’t currently within SSMS.

AzureIconsLet’s also note the little icons in the upper right of the plan, displayed to the left in full size. Clicking on these completely changes what’s represented to us from the execution plan. First the little grid looking icon results in this execution plan:


You can click on the graphic to make it bigger so you can see everything. What we have are the operator nodes, listed by Node ID order, showing some of the interesting information from the properties in a grid format. And yeah, because it’s a grid, I can click on any of the column headers and get different sort order for the data. For large plans, this can be a quick and easy way to find the highest estimated cost, or the largest number of actual rows. The grid isn’t a text plan. The grid is just the same XML data that makes up the execution plan laid out slightly differently. Great stuff indeed. What if we click on the little bullet list looking icon?


Now we have a sort of nested display, almost like a human-readable XML. And the little blue icons next to the operator names do allow you to expand and collapse the layout. I haven’t decided how excited I am by this layout because I’m just not sure how I would use it. You can’t resort on the columns even though this looks like a grid. It’s just a way to simplify the display with the collapsible nesting. Still, another tool in the toolbox.

Microsoft is doing some really cool stuff out there on WASD, making the portal there functional and useful.Let’s hope that some of this translates down to the core product.

If you’re interested in learning more about this kind of thing, you should take a look at the pre-conference seminar at TechEd 2013 that I’ll be putting on with Thomas LaRock (b|t) and Dandy Weyn (b|t) in New Orleans and Madrid, Spain. It’s called How to be a Successful DBA in the Changing World of Cloud and On-Premise Data. We’ll spend all day getting you deep into the guts of Azure SQL Database showing how to administer it, tune it, grow it, and make it work. You don’t want to miss it. Click here for New Orleans or here for Madrid to register for this and TechEd 2013.

I promise you, with Tom, Dandy and I all talking at once, this will be informative and entertaining.