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.

Jan 30 2013

Azure SQL Database Execution Plan Differences

I’ve been exploring execution plans in Azure SQL Databases a lot lately. I’m getting a presentation together for  some upcoming SQL Saturday events (first one is SQL Saturday #177, Silicon Valley). If you scroll to the bottom of this previous post, I mentioned that there were clearly differences in the optimizer because queries against empty databases were generating different plans. I’ve loaded up the data in my database, both SQL Server and SQL Database, so I can compare real behaviors. Doing so, I found a fun difference, even though I was running the query and generating the plan from SQL Server Management Studio.

Here’s the property sheet from the SELECT operator for the query run against SQL Server:


And here’s the property sheet from the SELECT operator for the query run against SQL Database:


Two differences jump out. The smaller of the two is that for the same data set, there are differences in estimated values. I noted that before, but it’s fun to see it coming up. By the way, if a good estimate could be defined as “closest without going over” then the optimizer in SQL Database is closer with 1.9 to the SQL Server value of 2.1. Just sayin’. Anyway, the big thing that you may have noticed was the NonParallelPlanReason with a value of CouldNotGenerateValidParallelPlan. Oooh, cool! Not that I would have expected a parallel plan for an estimated cost of .0149347. Assuming SQL Database has a default value of 5 for parallel plans, that’s pretty far off (and I hope they don’t still use that ancient value, nor should you). Some enterprising individual could try to suss out how much cost is needed to get a parallel plan (I don’t have time for that right now). More to come on execution plans and query tuning in Azure SQL Databases.

Jan 23 2013

Saving Execution Plans on Azure SQL Database

In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it’s pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can’t think of at the moment?

To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most obviously, when you look at the plan in SSMS you can just right click on that plan and select “Save Execution Plan As…” from the context menu. This will allow you to create a .sqlplan file on your OS. From there, do what you want. The file saved is XML, but the .sqlplan format is read by SSMS as a graphical plan. You can look at the underlying XML any time you want by right clicking and selecting the appropriate context menu.

And Azure SQL Database?

Well, things are a little different. Let’s start with the query. After you execute the query to get an actual plan or call up the estimated plan you’ll see the plan in the Portal.


You can see the query, the plan, and options with the plan (to be explored in a later post). If you right click on the plan as you would within SSMS, the context menu is singularly unhelpful since it will only have one option ‘Silverlight.’ (yes, we’re still running Silverlight in this part of Azure, don’t shoot the messenger). So I guess there’s no way to save that plan, right? Wrong. Look up at the top of the image above. You’ll see a button that looks like a 3.25″ floppy disk (kids, get granddad to explain what that is) and the phrase “Save As” underneath it. Click on that and you can save your query. Ah, but there’s a drop down under file types and there you’ll see “SQL Plan File.”


Provide a file name and you can save the execution plan down to your machine. Easy.

Ah, but there is a catch. Let’s say we query the Dynamic Management Objects to pull plans out of the cache. This query will run on both SQL Server and Azure SQL Database:

SELECT	deqp.query_plan
FROM	sys.dm_exec_query_stats AS deqs
		CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
		CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE	dest.text LIKE 'SELECT	m.MovieName,

The results, when run from SSMS, will look something like this:


That’s an embedded URL that you can click which will then open the execution plan from the cache. But, when the same query is run from SQL Database Management Portal, the results look like this:



No options for a clickable interface here to open the plans. You can try copying the output and then pasting it into Notepad then saving as a .sqlplan file. Of course, that’s only going to work for small plans since the result set here is not unlimited. You could build an app to query the cache and output to file. But, in general, querying directly to the plan cache from the SQL Database Management Portal gives you very few options for displaying your query plan.

Since this is such an important part of query tuning, here’s hoping that we get further enhancements to the Management Portal in the future.

Jan 21 2013

Pro SQL Server 2012 Practices: Chapter 12

A9781430247708-3d_1I was very privileged to get the opportunity to write a chapter in a book with some of my friends and SQL Family, Pro SQL Server 2012 Practices
. Just as each of us took a chapter to write, each of us going to take a chapter to review. But, being the greedy sort, I’m going to review two. First up, Chapter 12, “Windows Azure SQL Database for DBAs”, by Herve Roggero (b|t)

Personally, I love Azure. And I love Azure SQL Databases. But, I get the fear and trepidation they might cause. I also get the urge to write about them, but I never really felt like I should approach them from a book. Everything changes so much, so quickly in Azure and books just take a while to get out the door. It all seemed like an exercise in terror. But, Herve Roggero has taken a very smart approach here. Instead of a straight how-to, which is what I would have stupidly done, Herve has done a “why” approach. Why would you use this? What does it offer? What business need are you fulfilling? And I really love that approach to technology, not worrying about the technical aspects of it, but the business needs that it answers.

Herve starts with the basic architecture of the SQL Database. His emphasis is on the fact that we’re talking availability and up-time instead of performance. He does a good overview of how things are put together on the server so you can understand what’s happening to your data and why. He then goes on to show differences between what you are presumably familiar with, SQL Server, and SQL Database. He also covers the various management tools available online as part of Azure and security settings. He’s very careful to qualify things so that you know that everything is subject to change, but he still packs a lot of useful information in as he discusses the types of databases available and how to backup your data (you don’t, but you can get around that to a degree).

Herve spends a lot of time talking about how Federations are currently working within SQL Database. This is because Federations are one of the key business needs that the Azure SQL Database answers. He gives a great overview of Federations works then walks through a simple example so that you can understand what he meant. He finishes up that section with a review on the limitations so you will know how it affects your business needs.

Since performance is a secondary consideration everyone can breathe a sigh of relief, right? No! Of course not. And Herve doesn’t let us down. He covers some of the most important performance Dynamic Management Objects (although he calls them DMVs, dude, get with the program. Kidding, kidding.) you’ll need for understanding which of your queries are performing badly. He also provides a nice overview of how execution plans are presented within the SQL Database Management Portal (SDMP, a new abbreviation, I’ll use this). He also shows how the Query Performance dashboard will help you identify problem queries within your SQL Database.

Herve also covers some of the other utilities included with SQL Database such as Reporting Services and SQL Data sync. It’s important to remember, we’re not just talking T-SQL here. This is a full infrastructure to answer complete business needs. Herve doesn’t forget. He concludes with a short discussion on price, something that must be taken into account when talking about Azure SQL Database.

In summary, this is an excellent introduction to the topic of the Azure SQL Database. Herve has supplied you with the information you need to begin to make decisions on whether or not this could be something to satisfy business needs within your organization. Nice work Herve.

Jan 14 2013

Execution Plans on Azure SQL Database Portal

If you’ve been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You’ll get back results, timing, everything you need to observe behavior within the system. What’s that? You never heard of this? Let’s figure it out real quick. Here’s my portal to my Azure SQL Databases:

Azure SQL Database Portal showing usage overview

Azure SQL Database Portal showing usage overview

SQLAzureManageButtonYes, all sorts of things you can do from here, but we’re focused on the bottom of the screen at the Manage button. Clicking this button will open up the Management portal window, part of which you can see below:

Part of the Azure SQL Management Portal showing some of what can be done.

Part of the Azure SQL Management Portal showing some of what can be done.

Pretty prominent in the upper left corner, you can see the New Query button. I’m sure you’ll be shocked to find that clicking on that gives you another window in the portal as shown below. Worth noting though is the listing under My Work on the left side of the screen. These are more or less windows that you can switch between, without losing your work. It’s almost like tabs in SSMS. You can open multiple queries as you see. I have two unnamed queries I’m playing with and a file, TraceCaptureDef.1.sql, which will generate all sorts of fun errors if I try to run it on Azure. But the main point is, don’t feel like you have to open multiple tabs in your browser, you have a lot of control right there in the Management Portal.

Azure Portal with a Query Window open

Azure Portal with a Query Window open

I have a query there ready to go. A few more points about that query. Note that you do have some color coding, showing key words highlighted in blue. I typed this query and formatted in Management Studio before pasting it into the query window here, but that formatting went away to a degree. Something to keep in mind. Also note, that you don’t have properties about this query that you can set. If you want to see statitistc io or time, which you can, you’ll have to set them manually using T-SQL code. But once set, you can get messages showing how long the query ran and what sort of resources were used as you see here:


Messages shown after running a query in Azure SQL Database

Messages shown after running a query in Azure SQL Database

You can also choose to look at an Estimated execution plan by clicking the appropriate button, or toggle the inclusion of an Actual Execution plan by again, clicking the button at the top of the window. Here’s what you get for an Actual plan within Azure SQL Database:

Execution plan for the query outlined above as displayed in Azure Management Portal

Execution plan for the query outlined above as displayed in Azure Management Portal

I’ve posted in the past about some of the details that you can access when looking at execution plans within Azure SQL Databases. I’ll be posting some more on the Management Portal, execution plans, and query tuning in Azure.  Worth mentioning, if you look at my posts from several month ago, how they’re displaying icons in the plans has already changed. This is a seriously moving target.

Before I go, compare that execution plan with the same one from SSMS:

SSMS Version of the same query against an identical structure

SSMS Version of the same query against an identical structure

A couple of things to point out. Obviously, it’s different. But the real point here is that these are identical, empty, databases. The structures are 100% the same. The queries are 100% the same. But the Azure SQL Database plan includes a scan where there’s a seek in SSMS. More fun little indications that the optimizer that exists in Azure is not the same as in the base product (and yes, I’ve got the latest SP and CU installed). Something to keep in mind when you start writing queries against your Azure SQL Database instance.