Category: Azure

Oct 14 2015

Getting Started With DocumentDB

I’ve put this off for too long. It’s time to get my feet wet with some new tech.

Step 1 is easy. Go to the Azure portal and start the process for creating a DocumentDB:


While that’s running, let’s see what’s on the interwebs about getting started in DocumentDB…

Nice. I know I’m going to have write a little code to exercise this thing. Here’s a great run-through on exactly how to do it. Actually, the first hit when I searched on “Getting Started With DocumentDB.” Microsoft has a start page on DocumentDB, but it was clearly put together by someone from marketing. Scroll down to the bottom. There are a couple of interesting links including SQL Query Within DocumentDB. Now we’re talking. Here’s a Curah! or (which is it? do I care?) on DocumentDB with some of the above links and a few others. I spot a theme on this one. Here’s a more thorough how-to on querying DocumentDB.

Plenty to read, lots to do. And look, I have a DocumentDB database ready and waiting:


I’ll report back as I get things going.

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.

Aug 24 2015

Targeted Plan Cache Removal


A lot of times you’ll hear how people are experiencing sudden, intermittent, poor performance on a query, bad parameter sniffing at work, so they’ll fix it by running the following code:



Yeah, you just nuked the cache on your server because you wanted to take out a single terrorist query. Now, yes, that problematic query is going to recompile and hopefully have a better execution plan. Also, so are all the other queries on your system. That spike in CPU and the slow-down all your business people are experiencing… Your fault for going nuclear.

Instead of a nuke, why not use a sniper rifle to just remove the one problematic plan. Here’s a little piece of code to help out:


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

IF @PlanHandle IS NOT NULL
        DBCC FREEPROCCACHE(@PlanHandle);

Take a look at the documentation for FREEPROCCACHE. You can target specific plans using a plan_handle, a sql_handle, or even a resource governor pool by passing pool_name. I take advantage of that with this query to pull the plan_handle from sys.dm_exec_procedure_stats. You could use T-SQL text too, you’d just have to add in sys.dm_exec_query_text to one of the other DMOs that has the plan_handle or sql_handle such as sys.dm_exec_requests or sys.dm_exec_query_stats. However you choose to do it, you can use a targeted approach to remove plans from cache.

Let’s take the nukes off the table.

Want to really get into talking about the plan cache, query tuning, parameter sniffing and the rest? I have two upcoming all day pre-conference seminars. There’s still room at IT/Dev Connections in Las Vegas on September 14th. Click here to register. Also, at Connections, I’m hoping to be able to read your execution plans, so bring the really scary ones. Later that same week, I’ll be at SQL Saturday San Diego and will do a pre-con there on query tuning. Click here to get your seat.


Jul 29 2015

Hey Kids! Let’s Put on a Show at the Old Barn

Alternate Title: I’m traveling a bunch. Let’s get together and talk.

A bunch of trips and presentations coming up, so I thought I’d share. First, I’ll be SQL Saturday Omaha for my first time ever presenting in Nebraska. I’m excited to add this state to my list (which is almost over 40 now). If you’re not doing anything August 15th, let’s have a chat. Next, fingers crossed, I’ll get selected to fly back to my home state, Oklahoma, to go to SQL Saturday OKC. These guys put on a great event and hey, it’s Oklahoma so how can it be bad. I hope they announce soon. I need to schedule my flights. This one is on August 29th.

September also has several events. First, I’ll be at SQL Saturday Las Vegas on the 12th of September. My first time at this event so I’m looking forward to it. That’s followed immediately by a pre-con and a couple of sessions at ITDev Connections. I love inter-disciplinary conferences because it gives you a chance to branch out and learn more knowledge across the stack. It’s extremely useful if you’re getting into DevOps (which you should be). My pre-con is on query tuning and execution plans, using one to help the other. This is a great event with a lot of excellent speakers. Go here to register. But I’m not done. Down in San Diego, at their SQL Saturday on the 19th, I’ll also be doing a pre-con on query performance tuning on the 18th. I’ll see if I can’t squeeze some 2016 stuff in there too. Click here now to register for the precon.

But we’re not done. In October, I’m going to hop the pond for one my absolute favorite events, Red Gate’s very own SQL in the City: London. It’s a great event with a great collection of speakers. I won’t lie, we’re going to focus on Database Lifecycle Management (DLM), but there’ll be sessions on query tuning and all sorts of other stuff as well. It’s focused on the Red Gate tools and, let’s face it, it’s a Red Gate style event, so it’s fun and entertaining. If you really do want to drill down on DLM, there’s also the all day seminars that Red Gate is hosting that week. We’ve put a lot of work into these and I think they’re some pretty amazing hands on classes that will get you started automating your own database deployments. Also, while I’m over there, I think I may find my way to a SQL Relay event, so eyes peeled.

Later in October, well, it’s the PASS Summit. ‘Nough said. I’m doing a session focused on the beginner that’s all around statistics. They’re so important to everything you do with queries, that it’s a good idea to spend some time understanding them before you get into all the craziness of query tuning. If you’re attending Summit, please stop by my session. Click here to register for the PASS Summit. Fingers cross, I might be at SQL Saturday Portland if I get accepted.

One more that I don’t mean to leave off, but I think it deserves it’s own paragraph. Right before the PASS Summit, we’re bringing SQL in the City back to Seattle. That’s right, another shot at seeing the great stuff we’re going to bringing to London, plus a few different things (we mix up the speakers and sessions to help keep it all fresh).

Jul 20 2015

SQL Server Management Studio – Footloose and Fancy Free

That’s right. There’s been a divorce. SQL Server Management Studio (SSMS) has been divorced from the server product. In fact, Microsoft is inviting you to the new SSMS coming out party.

I’m pretty excited about this. While I’m very comfortable in SSMS, to a large degree, it’s like that old pair of jeans that you’ve worn for the last 10 years. They’re comfortable too. Well, maybe a little tight when you pull them on out of the wash. One of the knees is gone. The legs are frayed so much it almost looks intentional. You just noticed a hole in the bottom. The zipper is acting up… Yeah, OK. These jeans have had it. So has SSMS.

The plan from Microsoft is to upgrade SSMS independently from the boxed product. In fact, since one of the goals is to coordinate functionality within SSMS with releases of Azure SQL Database, I think it’s pretty safe to say that we’ll be seeing very regular and frequent updates. While this is a good thing for SSMS and it’s a good thing for Azure SQL Database. This level of frequent updates might not be a good thing for individual data pros if you’re not used to actively pursuing new knowledge and new tooling all the time. Or, it can act as an incentive to help keep your knowledge levels up to date because you’re more employable if you’re maintaining your skill set.

Let this act as a prod to go out and learn some new functionality. Oh, and buy a new pair of jeans.


May 26 2015

Database Engine Tuning Advisor

I would love to see the Database Engine Tuning Advisor (DTA) pulled from the product. Completely. Heck, I feel bad that I included a chapter on it in my query tuning book (all updated for SQL Server 2014 by the way). Let me tell you why we need to pull this tool.

First, I understand its purpose. It’s supposed to be a fast and easy way to get some performance tuning done for people who just don’t have the time or knowledge to go through the full process of gathering metrics, evaluating poor performers, understanding root causes and applying indexes to fix those causes. I also readily acknowledge that it actually is an amazing piece of software. If you don’t agree with that, go read this white paper. With those acknowledgements in place, I still think there are two distinct problems with the approach and a serious fundamental issue with the premise. Let’s talk about the problems of the approach first.

Many, probably even most, of your query performance problems are in your T-SQL. Some of your query performance problems are in your table structure. Some of your query performance problems are in your indexes and statistics, but not a majority. The DTA can suggest indexes, statistics, materialized views, and partitioning (partitioning for performance I might add, not data management, and that opens up a completely different can of worms we can’t address here, I don’t have time). In short, it can’t address your number one problem, your code. Since it can’t address the number one problem, all it can do is work around the edges. You’ll run this thing, think you’ve addressed your issues, but your issues are still there and now you’re just as stuck as before you found the DTA and ran it. It just doesn’t fix the core issue, your code.

The second problem I see with it is that it doesn’t have enough intelligence built into it’s functionality. I say that with some reluctance because it really is a pretty amazing piece of functional code. But it suffers from a couple of shortcomings there. It’s completely dependent on the load provided to it. If that load is off, it’s recommendations are off because it just can’t have the intelligence to recognize that a poorly representative load has been provided. This lack of intelligence is supposed to be offset by the person running the DTA to ensure that they are gathering the right information and that they can interpret and test the resultant recommendations. Which brings us to the fundamental issue with the premise.

This is supposed to be run by people with no internals knowledge. Right? BUT! These people are also supposed to make a judgement based on the recommendations whether or not they should be accepted. Further, they should test all the recommendations prior to applying them to their production server. They also must gather a well structured and meaningful representative load from their production system in order to supply the DTA with good information. Further, the very sophisticated set of tests around the DTA actually makes a fundamental assumption that could be radically off, that the person designing the database has done a good and thorough job of correctly normalizing the structures. You agree with all these assumptions on the part of the DTA? Am I alone in thinking that we have a problem here? If people lack any understanding of the internals they won’t be able to judge those recommendations. If people don’t have the ability to gather and interpret performance metrics they won’t be able to test the suggestions of the DTA or provide it with the necessary test load. In short, the DTA can’t be relied on to solve the problem it’s supposed to solve because of the root cause of that problem, peoples lack of knowledge and understanding.

I won’t even get into finding indexes with names like this:


Instead of evaluating the suggestions made by the DTA and applying just those that make sense and will have a positive impact, people assume that every single suggestion from the tool is Gospel. They apply them all, without thinking, without knowledge, without appreciation of the possibility, sometimes even the likelihood, of serious negative impact.

I recognize that many people are stuck. They have a SQL Server instance that’s causing them pain and they don’t have the knowledge necessary to fix it. Further, I know a few of you have used this tool successfully in some situations. I agree that there ought to be some way to mechanically and automatically tune the server. However, the DTA is not that tool, despite it’s clear and obvious sophistication. Let’s get rid of it.

Want to learn how to tune queries? I’m putting on an all day seminar at Connections in September. Click here right now to register. We won’t use the DTA.

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.

Apr 06 2015

Constraints and SELECT Statements

I’ve posted previously about how a foreign key constraint can change how a SELECT query behaves. Logically that just makes sense. But other types of constraints don’t affect execution plans do they?


Let’s take this constraint as an example:

ALTER TABLE Sales.SalesOrderDetail WITH CHECK 
ADD  CONSTRAINT CK_SalesOrderDetail_UnitPrice 
CHECK  ((UnitPrice>=(0.00)))

That will ensure that no values less than zero can slip in there. We can even validate it:

INSERT Sales.SalesOrderDetail
VALUES  (60176, -- SalesOrderID - int
         N'XYZ123', -- CarrierTrackingNumber - nvarchar(25)
         1, -- OrderQty - smallint
         873, -- ProductID - int
         1, -- SpecialOfferID - int
         -22, -- UnitPrice - money
         0.0, -- UnitPriceDiscount - money
         NEWID(), -- rowguid - uniqueidentifier
         GETDATE()  -- ModifiedDate - datetime

Will give me an error:

Msg 547, Level 16, State 0, Line 470
The INSERT statement conflicted with the CHECK constraint “CK_SalesOrderDetail_UnitPrice”. The conflict occurred in database “AdventureWorks2014”, table “Sales.SalesOrderDetail”, column ‘UnitPrice’.

Let’s look at a SELECT query now. If we run this:

SELECT  soh.OrderDate,
        p.Name AS ProductName
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   p.Name = 'Water Bottle - 30 oz.';

The resulting execution plan looks like this:


But, if I modify the query to look like this, adding an additional AND filter on the constrained UnitPrice column:

SELECT  soh.OrderDate,
        p.Name AS ProductName
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   p.Name = 'Water Bottle - 30 oz.'
        AND sod.UnitPrice > $0.0;

You know what happens to the execution plan? Nothing. It stays exactly the same. The optimizer knows that in order to satisfy the query, it can safely ignore the change in the WHERE clause. In fact, you can look at the SELECT operator properties for the two different plans and note that while the Query Hash values changes, the Plan Hash value stays the same. The plans are identical.

With that knowledge, I’m going to modify the query to look like this, reversing the UnitPrice reference to look for data that violates the constraint:

SELECT  soh.OrderDate,
        p.Name AS ProductName
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   p.Name = 'Water Bottle - 30 oz.'
        AND sod.UnitPrice < $0.0;

And now we have a new execution plan:



The optimizer recognized that there is no way that any data can be returned with the WHERE clause above because there is an enforced constraint (note the use of the WITH CHECK clause on the constraint). This completely changes the execution plan in every possible way. Now, instead of attempting to access the data, a Constant Scan operator is put in as a place holder for an empty result set.

To sum up, yes, constraints absolutely affect the choices made by the optimizer when those constraints would have an affect on the plan, even a SELECT query. Also, I would argue, this means that the use of enforced constraints can be a performance enhancer since the optimizer can make intelligent choices about how a given query is dealt with.

UPDATE: Fixed a typo that said the constraint prevented data that was equal to or less than zero. It’s only for data less than zero.