Category: Azure

May 16 2016

Query Store, Forced Plans, and New Plans

I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1).

Let’s say you have selected a plan that you want to force. You set it up. Now, let’s say the plan ages out of cache or even goes through a recompile. During the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. However, with Query Store and plan forcing, the plan that’s going to be used is the plan that is being forced. But, does that other plan, the one not used, get stored in Query Store?

I have no idea. Let’s find out.

The Setup

To start with, a small stored procedure that I use all the time for bad parameter sniffing demos that reliably gets different plans with different values due to statistics skew:

CREATE PROC dbo.spAddressByCity @City NVARCHAR(30)
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;

If this procedure is called for a value of ‘London’ it gets a plan with a Merge Join. For most other value it gets a plan with a Loops Join. Here’s an example of the ‘London’ plan:


I’ve run both queries on my test system. I can check their existence in Query Store this way:

SELECT  CAST(qsp.query_plan AS XML),
FROM    sys.query_store_query AS qsq
JOIN    sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE   qsq.object_id = OBJECT_ID('dbo.spAddressByCity');

That’s going to return two (2) rows:


Now, I’m going to remove the second plan, the ‘Mentor’ plan, and I’m going to force the ‘London’ plan with two statements:

EXEC sys.sp_query_store_remove_plan @plan_id =219;

EXEC sys.sp_query_store_force_plan 2,2;

Now, when I execute the query, no matter what values I pass, I’m going to arrive at the ‘London’ plan. However, let’s pull the plan from cache in order to force a recompile:

DECLARE @PlanHandle varbinary(64);

SELECT  @PlanHandle = deps.plan_handle
FROM    sys.dm_exec_procedure_stats AS deps
WHERE   deps.object_id = OBJECT_ID('dbo.spAddressByCity');


The Reveal

When I execute the query using the value of ‘Mentor’ do I get that plan stored in the Query Store? I know which plan will be in the cache. The ‘London’ plan because of I have chosen to force that plan. But, when I query the Query Store again to see what’s there for my stored procedure:


The short answer is, nope. Any possible new plans that could have been stored in Query Store are not there. Yes, we have gone into the compile process, but, due to plan forcing, that process is getting a small bypass and the plan is simply applied from the Query Store. This, even though, you’re going to see a compile or recompile event if you capture them using Extended Events. For more fun on recompiles and the Query Store, see this earlier post of mine.

Keep those questions coming.

I love talking about query tuning. If you want to spend the day with me talking query tuning, you sure can. In August, I’ll be doing an all day pre-con at SQLServer Geeks Annual Summit in Bangalore India.

I’ll be doing a pre-conference seminar in Oslo Norway before SQL Saturday Oslo in September.

Apr 27 2016

Customizing Your Azure Portal

Not all of us are going to use Azure in the same way. That’s just common sense. Therefore, not all of us are going to want it to look the same. Here’s a default view of the portal for a login I created within my own domain:


You can contrast that with this login to my portal that I use for most of my testing and training (this isn’t my company’s production or development portal, which looks still more different):


Clicking on either of these will open them so you can look at details. Not only are the color schemes different, but you’ll note that the selection lists on the left are different as are the grids on the dashboard. All this can be customized for each login, and, within a login, on each Subscription to which that login belongs.

All this means that you can customize the portal for your individual needs, even between your production and development areas within the portal (assuming you’re keeping them separate, which you ought to be). Let’s explore a few of those options.


Over on the left of the screen, is a listing of favorite resources. At the bottom of this list you’ll see a “Browse >” button. Selecting this pops out a list of all available resources:


This is a complete listing of the resources available to your subscription, including anything that is in Preview. Note, on the right hand side of the screen are stars. One of the stars in the list above shows up as gold and filled in. This indicates that it’s currently on my list of resources as a favorite. Selecting one of the resources that is not filled in will add it to the list and selecting one that is filled in will remove it. This is your first customization. Add the resources to favorites that you use all the time. You can even drag them around to rearrange the order in which they appear.

Portal Settings

If you want to change the color scheme, you can do that too. At the top of the screen is a little icon of a gear. Clicking on that opens the Portal Settings blade:


Not only can you modify the colors scheme, but you get to set labels, animations & the language of the portal.


This is where you can get crazy. You have the ability to add all sorts of output, readouts, shortcuts, to your dashboard. You get to set their size, rearrange them, even create additional dashboards. Additional dashboards give you the ability to customize a view so that you could, for example, have your most important Azure SQL Databases displaying their DTU readouts as a single view:


Yeah, yeah, it reads zero. Test databases, remember. Anyway, customizing a dashboard is easy. If you click on “+New dashboard” or “edit dashboard” at the top of the screen, a new blade opens. This allows you to add and rearrange tiles. You’ll see a grid that has dashed lines for the resolution of a screen. It makes it very easy to figure out what’s going to be displayed and what’s not. On the left you get the ability to track down different kinds of tiles. In the example below I have a list of the tiles for Azure SQL Database (you will need to click on this one, it’s big):


You can drag and drop the tiles from the left on to the screen. Once there you can rearrange where they are located, resize them, remove them, pretty much what you would expect. Further, you can customize each tile individually. The tile will have a clickable link called “Edit”. Clicking on that will bring up unique options depending on the tile. In the example below I’m showing the options for the Resource Utilization tile:


I’ve decided to just show DTU percentage and DTU used for the last hour as a Line chart. You can rearrange this as necessary to fit your own needs. You can rename the dashboard, and then, when you’re done, just click on the “Done Customizing” button and you’ll see the dashboard. You can pick which dashboard to look at. You can even share a dashboard with your team so that everyone has access to the same information you do.


This was just a bare bones run-through of what’s possible with the Azure portal. You really can achieve a very high level of customization to get just the information you need, in the way that you need it. Take advantage of this because, at times, it can be quite difficult to manage information on the portal.

Apr 21 2016

Microsoft’s Commitment to Azure

For several years, many of us who were involved with working in Azure from the early days, were concerned that Microsoft had bet the farm and could possibly lose it all. They may well have bet the farm, but more and more it’s extremely clear that there is zero likelihood of them losing the farm. In fact, it’s looking more and more like, while using farming analogies, they’re turning into an agro-corp. Azure is taking off.

You need to start working on adding Azure knowledge to your skill set. If you have access to an MSDN license, getting into Azure is easy because of the credits available. However, not everyone works for a company that provides MSDN or has purchased a license. In that case, getting into Azure, just for testing and learning could be expensive (I frequently “spend” $150/month with my MSDN credits). However, Microsoft is very serious about getting everyone moved into this space. They’ve launched a new free program called IT Pro Cloud Essentials. Not only does it give you Azure credit, but you also get access to O365, another set of skills and understanding you need to acquire.

If cost was one of the reasons you were choosing to not explore Azure, time to give that one up. Explore the offering and then get your learn on.

Apr 12 2016

A View Is Not A Table

Blog post #4 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel

In SQL Server, in the T-SQL you use to query it, a view looks just like a table (I’m using the AdventureWorks2014 database for all these examples):

FROM    Production.vProductAndDescription AS vpad;


SELECT  vpad.Name,
FROM    Production.vProductAndDescription AS vpad
JOIN    Production.Product AS p
        ON p.ProductID = vpad.ProductID
JOIN    Production.vProductModelInstructions AS vpmi
        ON vpmi.ProductModelID = p.ProductModelID
WHERE   vpad.ProductID = 891
        AND vpad.CultureID = 'fr';

The above query actually combines two views and a table. This is what is commonly referred to as a “code smell”. A code smell is a coding practice that works, but that can lead to problems. In this case, we’re talking about performance problems. The performance problems when using views to join to tables and other views as if they were real tables comes about because a standard view is not a table. Its a query. For example, the second view introduced, vPorductModelInstructions looks like this:

ALTER VIEW [Production].[vProductModelInstructions] 
    ,[Instructions].value(N'declare default element namespace ""; 
        (/root/text())[1]', 'nvarchar(max)') AS [Instructions] 
    ,[MfgInstructions].ref.value('@LocationID[1]', 'int') AS [LocationID] 
    ,[MfgInstructions].ref.value('@SetupHours[1]', 'decimal(9, 4)') AS [SetupHours] 
    ,[MfgInstructions].ref.value('@MachineHours[1]', 'decimal(9, 4)') AS [MachineHours] 
    ,[MfgInstructions].ref.value('@LaborHours[1]', 'decimal(9, 4)') AS [LaborHours] 
    ,[MfgInstructions].ref.value('@LotSize[1]', 'int') AS [LotSize] 
    ,[Steps].ref.value('string(.)[1]', 'nvarchar(1024)') AS [Step] 
FROM [Production].[ProductModel] 
CROSS APPLY [Instructions].nodes(N'declare default element namespace ""; 
    /root/Location') MfgInstructions(ref)
CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace ""; 
    step') Steps(ref);


That’s a query against the XML stored in the ProductModel table. The view was created to mask the complexity of the necessary XPath code, while providing a mechanism for retrieving the data from the XML. This is a common use of views. However, when we then treat the view as a table, and join it to other tables and views, we present a problem for the optimizer. Because a view is not a table, but is instead a query, the optimizer has to resolve this query in combination with any other views or tables to arrive at an execution plan for the whole combined mess. While the optimizer is very good at what it does, because of the complexity caused by the additional unnecessary processing to figure out which parts of the view is not needed to satisfy the query, it can make poor choices. That can result in poor performance.

If I were to rewrite the query, it would look something like this:

SELECT  p.Name,
        pm.Instructions.value(N'declare default element namespace ""; 
        (/root/text())[1]', 'nvarchar(max)') AS Instructions
FROM    Production.Product AS p
JOIN    Production.ProductModelProductDescriptionCulture AS pmpdc
        ON pmpdc.ProductModelID = p.ProductModelID
JOIN    Production.ProductDescription AS pd
        ON pd.ProductDescriptionID = pmpdc.ProductDescriptionID
JOIN    Production.ProductModel AS pm
        ON pm.ProductModelID = p.ProductModelID
CROSS APPLY Instructions.nodes(N'declare default element namespace ""; 
    /root/Location') MfgInstructions (ref)
CROSS APPLY MfgInstructions.ref.nodes('declare default element namespace ""; 
    step') Steps (ref)
WHERE   p.ProductID = 891
        AND pmpdc.CultureID = 'fr';

That’s a lot more complex than the query we had above that only referenced three objects and had only two JOIN operations. However, if you capture the I/O and the execution time for these queries, you’ll see a different story.

I used two methods for measuring performance. I used SET STATISTICS IO and SET STATISTICS TIME to ON for the queries for one set of tests. For another set I used Extended Events. Consistently the execution time for the query with the view was around 110ms. The query that didn’t reference any views was around 37ms. The reads were 155 for the query with views, but only 109 for the query without. If you look at the individual table I/O, you can start to see where the differences come from. These are the results from the query with the views:

Table ‘ProductDescription’. Scan count 0, logical reads 56
Table ‘ProductModelProductDescriptionCulture’. Scan count 28, logical reads 56
Table ‘xml_index_nodes_418100530_256001’. Scan count 13, logical reads 37
Table ‘ProductModel’. Scan count 0, logical reads 2
Table ‘Product’. Scan count 0, logical reads 4

These are the results for the query without the view:

Table ‘ProductDescription’. Scan count 0, logical reads 56
Table ‘xml_index_nodes_418100530_256001’. Scan count 13, logical reads 37
Table ‘ProductModelProductDescriptionCulture’. Scan count 6, logical reads 12
Table ‘ProductModel’. Scan count 0, logical reads 2
Table ‘Product’. Scan count 0, logical reads 2

You can see the differences in both ProductModelProductDescriptionCulture and Product. This is because of the differences in the execution plans caused by differences in the choices made by the optimizer.

A standard view is not a table. There is such a thing as a materialized view or indexed view, which is a table. That’s not what we’re talking about here. While you can use a view as if it was a table, don’t mistake it for a table. A view is just a mask in front of a query. It can’t be used like an object so that you avoid rewriting the same JOIN. That will lead to issues for the optimizer as this simple set of examples showed. Don’t shy away from using views, just understand what their real behavior is. A view is a query, not a table.

Mar 29 2016

Do You Teach Azure Data Platform?

azureI offer instruction on the Azure Data Platform, and have for about six years, since shortly after it came out. I started using Azure SQL Database (although it had a different name then) Day 1.

I know a few other people who don’t work for Microsoft, but have been actively pursuing Azure SQL Database, SQL Server on Azure VMs, and pretty much all the Microsoft Data Platform. I’m not counting the BI people who have dived into PowerBI and related tech. The BI people, who are generally pretty smart, jumped on Azure with both feet. I’m talking about the data platform aspect of Azure. The people that I know who regularly teach classes are (in no particular order, sheesh, you people):

Karen Lopez(b|t)
Denny Cherry(b|t)
Jes Borland (b|t)
Thomas LaRock (b|t)
Joe D’Antoni (b|t)
Ron Dameron (b|t)
Aaron Bertrand (b|t)
Tim Radney (b|t)

I’m sure I overlooked someone who is active in this space. Please help me out. Let’s create a good list of active educators on the Azure Data Platform. I believe this is needed so that people know where to go, besides the excellent Microsoft resources (and they are excellent), to get more information. Please, no Microsoft employees. Yeah, many of them are great educators and I’m sure going to go and sit in their classes, as you should. I’m just trying to get the fundamental list of non-Microsoft speakers together and share it out.

Azure interest is growing, fast. Independent voices are valued and needed. Let’s get this list together, published, and maintained. Send me your input through the comments or my email (grant – at – scarydba -dot- com). I’ll get things published ASAP.

Oh, and if I missed you from the initial list and you were an obvious inclusion, my apologies. I’m old.

And, thinking about it, let’s get the BI people in Azure listed too. I was being lazy, not exclusive. Lazy & old. The intent is still good.

If you, or someone you know, is actively teaching Azure Data Platform, I want to know about it so I can add them to the list that I’ll maintain.

Mar 25 2016

Happy Dance!

I’m all like:


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:



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.


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.


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:


I’ve created two tables on each respective database:

     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)

     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)

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

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:


SECRET = 'securitymatters';

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

	CREDENTIAL= DB2Security);

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

	ID int,
	Val varchar(50))
	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:


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:


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 24 2016

Query Store and Recompile

One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database).

What happens to plan forcing when you have OPTION RECOMPILE on a query?

Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing:

ALTER PROC dbo.AddressByCity @City NVARCHAR(30)
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;

If this procedure is called with the value of ‘Mentor’ you get an execution plan that looks like this:


If you remove that plan from cache and then call the procedure with the value of ‘London’ then the plan looks like this:


It’s parameter sniffing in action. One of the cool pieces of functionality that comes with the Query Store is the ability to choose a plan and make that plan get used, regardless of the parameter values. You do this through the following command:


You pass it a query_id and a plan_id  from the information in the Query Store and now that plan will get used. But, what happens when you have attempted to fix your query using a query hint, such as RECOMPILE? Let’s modify our procedure:

ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
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

If I run the procedure, regardless of the parameter values passed in, I get a new plan for each value. Nothing is stored in cache, but, it is stored in the Query Store.

Let’s pull the query_id and plan_id out of Query Store for this procedure:

SELECT  qsq.query_id,
        CAST(qsp.query_plan AS XML) AS sqlplan
FROM    sys.query_store_query AS qsq
JOIN    sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE   qsq.object_id = OBJECT_ID('dbo.spAddressByCity');

Currently, on my system, this brings back four rows, two distinct query_id and four different plan_id. This is because, when I modified the text of the query within the procedure, I kept the object_id the same, but, the query text is different (the RECOMPILE hint). This means a different record in the Query Store. We’ll use the newer query_id and pick one of the plans to force it:

EXEC sys.sp_query_store_force_plan

Now, no matter what value I pass it, the RECOMPILE hint is effectively ignored. I have the same plan every time (in my case, the London plan). Ah, but is the RECOMPILE hint ignored? In fact, no, it isn’t. If I capture the extended event sql_statement_recompile, I can see every single time I execute this procedure that it’s going through a full recompile… which is then tossed and the plan I chose is forced upon the optimizer. Every time.

This isn’t a revelation. It makes sense. However, the Query Store represents one more thing that we have to think through.

If you want to talk query tuning, execution plans, the Query Store, and more, I’ll be presenting an all day pre-conference seminar at SQLDay in Wroclaw Poland on May 16th, 2016. Let’s talk.

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.