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.

Oct 02 2015

Speaker of the Month: October 2015

Working on my third year of “Speaker of the Month” posts now. The good news, I haven’t run out of people to award it to. Most months, if I’ve been able to go to a community event or two, I have three or four candidates.  Same this month.

Speaker of the Month for October 2015 is Rob Volk and his presentation, Revenge: THE SQL, at SQL Saturday Las Vegas.

What can I say about this presentation? Let me start by saying that you never, ever want to make Rob angry. Next, I think Rob needs a hobby that will help him turn his mind away from EVIL. Next, Rob has to be one of the smarter people that I know. Next, Rob is very funny. Oh yeah, and there was a presentation about all sorts of evil things you can do with SQL Server and SQL Server Management Studio. Rob’s delivery is excellent. Actually, I wish we had recorded that session for several reasons. It was good. More than that though, Rob had a room full of MVPs and presenters and… well, we kind of went after him. It was like it didn’t matter. He worked it right into his presentation and went through everything. I learned tons of stuff, much of it not necessarily useful for my production servers. All the things Rob presented did give you great illustrations about some of the internal workings of SSMS, SQL Server and Robs twisted imagination.

The only critique I can offer Rob is that he has so much material to deliver that he might want to trim it just a little. We drilled down on a couple of questions which seriously impacted how much he could get out. Is having too much material a really bad thing? Not necessarily. You just want to make sure that you hit the most important stuff and that you have time to reemphasize what the most important stuff is. That’s it though. It really was a masters class on how to do sessions right.

Thanks, Rob. Oh, and by the way, stay away from my machine.

Sep 14 2015

PASS Nominations Committee 2015

The Nominations Committee has done their job and the final report has been published.

Good luck to everyone running. Thank you for stepping up and taking part in this process.

This is the first time in a very long time that I don’t get to put out a “You should listen to me and vote for…” blog post. Since I’m on the board I feel more than a little squeamish coming out for any candidate. If I openly support Person Y over Person X, have I just yacked off all the supporters of Person X? Have I yacked off Person X? Most importantly, have I potentially poisoned a relationship that I need in order to be effective while on the Board? Add to that the fact that I was ask to serve on the Nominations Committee this year. Now I’ve been asked to sit in judgement of a number of people and declare their various fitness for the board. Yeah, no one is getting a nod for the election this year.

Instead, let’s talk about the Nominations Committee (NomCom) process. The members of the Committee are publicly announced. The process in support of the NomCom is also published. I was assigned volunteered to be on the NomCom as one of the Board representatives. Kidding. I was very happy to get to take part in it this year since I had just gone through it for the first time last year. The committee operation was great. Not surprising. Look at the people on the committee. We met a few times to be sure that everyone was acquainted with the rules of the election as well as each other. Then we scheduled interviews with the candidates that qualified based on the documentation in the process. We were able to read their applications ahead of time. The interview consisted of us taking turns asking questions. I kept notes of interesting answers, answers I liked, answers I didn’t, and used the notes to come up with my rating of each candidate (No, you can’t see the notes. I burned them and then scattered the ashes). From where I sat, the process was smooth and straight forward. It was a real pleasure to work with all the other committee members. I’m not blowing smoke here. We really had fun. Did I mention we kept a chat going through Skype during the interviews so that we knew who was asking which question next… and so we could have a little fun picking on each other (not the candidates, we are professionals). Evidently Grant and #2 seem to be connected in some fashion (you had to be there).

I honestly can’t think of any major process improvements I would suggest for next year. One minor change I’d like to see introduced is that the documentation we currently have conflates Management and Leadership. I think they’re different, very different, and should be graded differently. It’s a very minor change, but one I think that’s important.

In short, I think the PASS community was well served by the members. We asked tough questions of the people running for the board. Each and every one gave great answers. It’s the kind of experience and process you want to take part in as a volunteer.

Thank you to my fellow Committee members. You guys were great.

Sep 09 2015

Yes, Foreign Keys Help Performance

I created this example several years ago that illustrates how foreign key constraints can help performance. It’s a contrived example. Granted. I feel like it illustrates the point.

However, over the years, people have questioned one aspect of it. The optimizer uses the foreign keys to figure out which tables can be eliminated from the query, making for a more efficient plan and making the query run faster. The pushback has always been, “Yeah, Grant, but nobody writes T-SQL where they include extra tables that they don’t need.”

My initial response, after I stop laughing, is to point out any number of ORM tools. But, you know what, let’s assume that’s correct. No one would ever create a giant catch-all view that has all their JOINs in one place so they don’t have to write them for different queries. Stuff like that never happens. Everyone will only, ever, write a query based on the exact tables they need, so this simplification process of the optimizer is a bad example. I agree.

Please go read through the other post for all the details of the set up. I’m just going to focus on one query in this post.

First, in the example, the SELECT only references the Person table and there is no WHERE clause. So we could completely eliminate all the other tables. But, I still want to illustrate how foreign keys help, so let’s assume, for some reason, we need the JOIN (yes, contrived, but it’s going to illustrate the point, again):

SELECT  p.LastName + ',' + p.FirstName AS PersonName
FROM person.BusinessEntityAddress AS bea
JOIN Person.Person AS p
ON p.BusinessEntityID = bea.BusinessEntityID;
SELECT  p.LastName + ',' + p.FirstName AS PersonName
FROM dbo.MyBusinessEntityAddress AS bea
JOIN dbo.MyPerson AS p
ON p.BusinessEntityID = bea.BusinessEntityID;

The first query goes against a pair of tables with enforced referential constraints. The other query goes against an identical set of tables (indexes are all the same, so is the data and the statistics), but no foreign keys (the final point at the old blog post). If we capture performance metrics on these two queries we see the following:

Execution Time (Avg ms) Reads
Foreign Key 134095.6667 155
No Foreign Key 193937.6667 3929

On average, the second query is about 60ms slower and has 18 times as many reads. Why? Let’s look at the execution plans:


The only difference between these tables is the existence of foreign key constraints, which leads to differences in the estimated number of rows. This occurs because the optimizer knows that the INNER JOIN is going to limit the data coming back to only matching data AND it knows that because there’s an enforced referential constraint, exactly how many rows that will be. Because of this knowledge, it chooses an execution plan that is more efficient.

Foreign keys help performance in SQL Server.

Sep 04 2015

Speaker of the Month: September 2015

Feast or famine.

The last several months I just haven’t seen that many speakers, let alone that many community speakers. Then, in the last month, I’ve seen a ton. Contests being what they are though, someone has to win. To everyone I saw speak at SQL Saturday Oklahoma City and SQL Saturday Omaha who doesn’t see their name here, apologies. I saw great presentations this month. The winner though is Ryan Adams (b|t) and his presentation: Backup and Restore Tips and Tricks.

Ryan started off great, walking around the room, looking people in the eye has he talked and using the slides as a guide, not reading from them at all. He had a bunch of slides that visualized different aspects of backup and restore. I really loved the one on restoring to a point in time. I really liked how he approached the topic as a beginners guide, reassuring them that yes, maintenance plans were OK, probably not a final stopping point in your process, but a great place to start. He had a bunch of excellent demos that highlighted all his points really well. I also loved how he explained the use of STANDBY in backups. It’s one of those things that I know, but I always forget to use well. I’m going to try to incorporate it better because of Ryan’s explanations and demos.

Ryan and I discussed a few places where I thought he could improve. He never read from his slides, but he had a tendency to face away from the audience while talking so that he could use the slides as reference. While I don’t think you have to stand there staring at the audience the whole time you present, you shouldn’t look away from them too long. I also thought the fonts were small on the slides. Yeah, I’m getting old, but that doesn’t mean I should just be kicked to the curb. Make those fonts bigger. Other than that, it was a very professional and informative presentation.

I strongly recommend you track down where Ryan is speaking next. He knows what he’s doing and is absolutely worth listening to. Thanks for the great presentation Ryan.

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.


Aug 19 2015

Time to Change

The_Brady_Bunch_Christopher_Knight_1973I recently spent two days consulting with a company on their database development and deployment processes. They are a small, capable, team of developers, database developers and a DBA who have embraced the idea that they need to be able to automate their deployments. I wasn’t in there to teach them much of anything, but to help walk them through what the possibilities were for their deployments. It was a great experience and I learned a lot. They recognized that it was time to change what they did and how they did it in order to achieve greater efficiencies. They recognized that, while they were successfully building and deploying their databases (a capable team, remember), they were experiencing pain that they could alleviate. Like Peter, it was a time for change.

I think, like Peter, people get freaked out by the idea of changing. The concept of “if it ain’t broke, don’t fix it” is not a crazy or dangerous one to have. Unfortunately though, processes can be both functional and broken if they’re just functional enough. Think about how long it takes you to prepare for a database deployment. Think about how well tested your deployments are before you get to production. Think about the number of mistakes you’ve introduced to your production environment. Think about the changes that you haven’t done because getting them into production would be too hard or take too much time. These are all indications of pain. If you’re hitting lots of pain, well Peter, it’s time to change.

The good news is, like Peter, there’s lots of support out there for you. For example, coming up in London, Friday the 16th, Redgate is hosting SQL in the City. It’s a fantastic event (and yeah, I’m saying that even though I work for Redgate). We’re going to be showing off our tools, especially some of the tools that can help you get your database lifecycle management processes under control. Want to get started on that change so that you can reduce the pain in your deployments? Then click here to register.

Can’t make it to London? How about Seattle? We’re taking the show on the road. We’ll be there on the 26th of October. Click here to sign up.

It’s time to change.

Aug 13 2015

PASS Board Update: July 2015

I was actually travelling for most of July and some of that was on holiday with the family, the real family. That’s why I didn’t get a June update posted.

I don’t have anything much exciting to report. We’re finally getting data out of our Chapter database and can report some interesting stuff. We’ve added 29 new chapters in the fiscal year of 2015. 10 in the US, 12 in EMEA, 2 in LATAM and 5 in APAC. There’s a bunch more. Not sure when it’s all supposed to be published, but I’ll share some of it now, just ’cause. Our chapters logged 1,405 meetings in fiscal year 2015. That’s a pretty serious amount of training. Well done everyone! Thanks for all your work Carmen.

I missed the July board meeting (holiday, family, apologies, but priorities). The June meeting minutes are already published for those interested. It was my second in-person meeting with the board. The interesting things for me were the executive committee election and the discussion around SQL Rally. I recognize, at this point, it’s all old news, so I’m unsure what to add. I’m very excited about Adam Jorgensen being the president of PASS (although, I also would have been just as excited about having Tom LaRock for another term). SQL Rally… It was a good idea. Unfortunately it was not doing what it should have done, provide a mid-ranged event with minimal management needs. Rally was costing tons and tons of time & money on order to get put on. With the growth of SQL Saturday events, some of them having 1-2 days of pre-cons, it just didn’t make sense, to me, and evidently to much of the rest of the board, to keep going with SQL Rally when we could put on events just as large as Rally with less effort and cost to the organization. It’s these decisions that you elected everyone to make. The passion that everyone brought to the discussions on Rally, well, I think you elected the right people.

One other thing that came out of the meeting, I’m on the Nominations Committee for the upcoming Board Election. We’ve already had some meetings and we’re working on a process that was updated from last year (although we’ve already identified a couple of small changes we want to make for next year).  I won’t be able to report on what was said during these interviews with the candidates that will be coming up next. I will talk about the process around vetting the candidates and publishing our results. We’re going to publish the scores this time instead of just ordering the candidates (which means, you can order them based on the scores if you choose). I’m mixed on this. For example, I came in last in the rankings by last year’s Nomination Committee (not whining, stating a fact). I can’t say that I’d be real excited for everyone to see what the scores were. Heck, I’m not sure I want to know (and I don’t know) what everyone thought of me after the interview process. I can see how some might not be crazy about this. However, I suspect pretty strongly that everyone we interview is going to be clustered pretty tightly together, so I doubt anyone will complain too much. We’ll see.



Aug 11 2015

Leadership: A Name and A Principle

I’m actively working to put together the leadership course that I talked about here and here. No, not full time. I still have to pay the bills with real work. But progress is going forward. I have an initial name and the principle around which we’re going to do this thing:

The Data Community Leadership Program will deliver a world-class platform designed to teach technology and thought leadership through direct training and mentoring in order to better grow and develop new leaders within our community.

Watch this space for more about the Data Community Leadership Program.

Also, this is going to be community driven and run (eventually). Feel free to provide feedback on the name and the principle.

Aug 10 2015

Generating Estimated Plan and the Plan Cache

Does generating an Estimated Plan cause that plan to be loaded into the plan cache?



What? Still here? You want more? Proof? Fine. Let’s first run this bit of code (but please, not on your production server):


That will remove all plans from cache. Now, let’s take this query and generate an Estimated Plan (CTL-L from your keyboard or by clicking on the “Display Estimated Execution Plan” button on the toolbar):

SELECT * FROM Production.ProductModel AS pm;

This will generate a trivial plan showing a scan against the Production.ProductModel table. Now, let’s run another query:

SELECT  deqs.plan_handle
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   dest.text = 'SELECT * FROM Production.ProductModel AS pm;';

That’s just an easy way to see if a plan_handle exists. If a plan was stored in cache for this query, I should see a result. I don’t. Now, you might say that this is because it’s a trivial plan. So, let’s complicate the query a little, add some JOINs and a WHERE clause:

FROM    Production.Product AS p
JOIN    Production.ProductModel AS pm
        ON pm.ProductModelID = p.ProductModelID
JOIN    Production.ProductInventory AS pi
        ON pi.ProductID = p.ProductID
JOIN    Production.Location AS l
        ON l.LocationID = pi.LocationID
WHERE   p.ProductID = 750;

Not the most complicated query around, but, it will go through full optimization because it is not a query that will generate a trivial plan. Let’s generate an Estimated Plan again. Now we’ll modify our query against the cache just a little:

SELECT  deqs.plan_handle
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   dest.text = 'SELECT  *
FROM    Production.Product AS p
JOIN    Production.ProductModel AS pm
        ON pm.ProductModelID = p.ProductModelID
JOIN    Production.ProductInventory AS pi
        ON pi.ProductID = p.ProductID
JOIN    Production.Location AS l
        ON l.LocationID = pi.LocationID
WHERE   p.ProductID = 750;'

You won’t see any rows returned. Now, if I execute the query and then re-run the query against the cache, then I see a plan. How about stored procedures? I have a really simple one I use to teach parameter sniffing:

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

I’ll generate an Estimated Plan and then query the cache using a slightly different approach:

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

Again, assuming I’ve cleared the cache and I haven’t executed the procedure, this returns zero rows. That’s because generating an Estimated Plan does not then load that plan into the cache. The plan gets loaded into cache when it gets executed.

I love talking about execution plans and query tuning. If you do too, we two opportunities to get together. The first is in Las Vegas at the Connections conference. Click here to register. If you bring your execution plans to Vegas, I’ll try to make some time during the session to use your plans to demonstrate to the crowd. The second opportunity will be down in San Diego, the day before their SQL Saturday event.