Aug 24 2015

Targeted Plan Cache Removal

4926596880_321ac69592_m

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:

DBCC FREEPROCCACHE();

BOOM!

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:

DECLARE @PlanHandle VARBINARY(64);

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
    BEGIN
        DBCC FREEPROCCACHE(@PlanHandle);
    END
GO

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?

No.

 

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

DBCC FREEPROCCACHE();

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:

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;

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.

Aug 06 2015

Reading Your Execution Plans

I’m putting the finishing touches on the all day seminar on execution plans that I’ll be putting on at DevConnections this year. I have tons of examples to show you how to interpret execution plans as part of query performance tuning. We’ll be able to fill our time. However, I’ve got a pretty high degree of flexibility on which examples I use. With that in mind, I’d love it if you could bring in your own execution plans that you’d like some help reading. I won’t guarantee a solution for your query problems, but I promise to read through it and make some suggestions.

If you have an execution plan that you don’t mind sharing with the group and you’d like a little consulting time on tuning the query, bring it with you to Las Vegas.

Aug 05 2015

PASS Needs You!

kitchenerIt’s time.

No more procrastinating. No more complaining about the process from the outside. No more excuses.

You have to run for the PASS Board.

The Nominations Committee is all set, off and running. We’ve got great people involved who are going to do their best to ensure that the community gets the right people to run for the board. However, the NomComm isn’t running for the board and the NomComm can’t compel people to run. It’s up to you to step forward and take on this task. If you’ve been out there thinking that maybe you need to step up, you do. If you’ve been thinking that you might be able to give back a little more, you can. If you think you might be the right person to help lead this organization into the future, you are.

It’s you. It’s time.

Applications open today.

Click on this link to find all the information you need to make this happen.

Also, apologies to Lord Kitchener.

Aug 03 2015

Differences Between Actual & Estimated Plans

I have, in the past, made way too much of the need for Actual Plans when doing performance troubleshooting. The primary reason for this is to get the Actual Plan in order to see the differences between the Actual and Estimated Row Counts as a means of understanding how the optimizer saw the data. But, is that the only thing that’s different between Actual & Estimated Plans? Well, pretty much, yeah.

I took two fairly average execution plans from SQL Server 2014 and ran them through Altova’s XML Spy, which does XML comparisons similar to how Redgate SQL Compare will compare two data structures for you. Here is every single difference I found. Everything was additional information in the Actual Plan.

In the information for the first operator, in my case, a SELECT operator, in the QueryPlan element, two properties:

DegreeOfParallelism
MemoryGrant

In the MemoryGrantInfo element, six properties:

RequiredMemory
DesiredMemory
RequestedMemory
GrantWaitTime
GrantedMemory
MemoryUsed

Then, in all the operators, in each RelOp element in the XML of the execution plan, one additional XML element, RuntimeInformation with these properties:

RuntimeCountersPerThread
ActualRows
ActualEndOfScans
ActualExecutions

This data is not exhaustive. I used a relatively benign query (it hit about 10 tables, had some suggested missing indexes, and about 40 operators all told). I’m sure if I tried lots of different types of queries, I might find a few additional differences. But for your average, “Hey, Let’s Tune This”, query, we’re looking at one additional element in the operators and twelve (12) additional properties, only four of which are in the majority of the operators.

Don’t get me wrong. If I have the choice, I want to see the Actual Plan, because that Actual/Estimated row count and execution count comparison are important. However, they are not the be all, end all, of reading execution plans and query tuning. This means, if you can get the Estimated Plan, it’s probably good enough most of the time. Heck, you can look at the estimates and then look at the data and statistics directly to get a sense if they’re accurate or not. The only time you’re likely to see any differences (other than those noted above) between an Actual and Estimated Plan is in the event of a recompile. It’s also worth noting, the plan you’re going to retrieve from the cache is going to be an Estimated Plan.

Do not be afraid of the Estimated Plan.


 

If you want to spend a lot of quality time with me, talking execution plans and query tuning, I’ve got a couple of different all day pre-conference seminars coming up. The first is in Las Vegas at the Connections conference. Click here to register. The second will be down in San Diego, the day before their SQL Saturday event.

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 28 2015

Grant’s Fly-by-night Leadership Course – The Plan

  1. Get a better name
  2. Define my personal goals for the course

I won’t be sharing these with you. I may share them with my Inner Circle (defined below). I’m thinking this is a 2-3 year commitment I’m making. I need to have my own set of measurements to ensure that this will be worth the time and effort that I’m putting into it.

  1. Define a set of principles and goals for the course

I want this course to have meaningful aim. It’s not, absolutely not, about management. Management and Leadership are two different things. This is about being a leader. It must have a purpose. The goals defined here, and like everything else, subject to change over time, will set the tone for each of the other choices. Here’s another place where I may lean heavily on the Inner Circle

  1. Create a first draft course agenda

At least initially, I’m just going to sketch out some topics that I want to cover. These include things like: setting goals (SMART goals), setting up methods of review, problem solving, institutional vs. personal power, coaching & mentoring, setting a vision, running a user group/meetup/?, other stuff. I’ll break them into a series of classes and/or articles and/or readings (yeah, this will be work for the people who take it on, tough). The classroom material will have to be generated. Some articles may have to be generated. I’m going to try to find some subject matter experts for some of this, just to get started.

This agenda, as I stated in my initial ramble, will include two very distinct and important points. First, it is going to be oriented towards volunteering. I believe that a very substantial aspect of leadership is helping people, leadership through service. With that in mind, even if this never gets associated with an organization such as PASS, we’re going to be bringing in some subjects that are, at the very least, PASS related. It’s an excellent structure in which to perform volunteer work within a technical sphere. I’m going to use it. Second, there will be tasks that must be completed. You will define these tasks based on the principles and goals of the course

  1. Get volunteers

Four sets of volunteers needed:

Set 1: Inner-Circle
These will be people that I’m going to contact directly that I know have a background in teaching about leadership, mentoring and volunteering. I’ll be using them to help me vet the agenda and the classes I hope to teach as well as support me through the process of picking the mentors who will guide the first group of people doing this.

Set 2: Subject Matter Experts
There are people out there that know more than I do about, well, everything. So for the classroom material and written material that we need to generate, I’m going to call for volunteers. It will be based on the agenda of the course, and I’m going to exercise editorial control (no biggie since I’m a marshmallow of an editor, I made need to take lessons from Tony). But it’s an opportunity for people to share their knowledge. Developed courses are going to become the property of this entity, whatever it is, because it’s the course that has to be the focus, not any one class. With permission, I think I’ll let classes be taught in other venues. I want to share this knowledge, plus the full experience is from the next set of volunteers.

Set 3: Mentors
Ideally, this group will only ever be drawn from attendees of this course (similar to how Wood Badge works). However, we find ourselves in a chicken/egg situation. So, I’m going to try to get 3-6 people who are prepped to be mentors, but who will also be taking the course at the same time (and possibly giving it in part, nothing to say there’s no cross-over between the first three groups of volunteers)

Set 4: Attendees
AKA, rhesus monkeys. We need someone to experiment on. I’m imagining between 9 and 18 people, three for each mentor. Each class will be the same, a group of three for each mentor and only a few mentors. Many more than that and we’re going to spending way too much time sweating personnel management and this is going to be hard enough as it is. These are people who want to step up. They want to become better leaders in their community, their company, or, best of all, both. Also, this first set have to be people who are going to commit to two things. First, they’ll do the course. I’m assuming that’s a requirement for everyone going forward. Second, they’ll help us build the course. We need volunteers who, right out of the gate, are going to be willing to provide actionable feedback (another thing we’ll teach) so that Class #2 is better.

With the exception of the Inner-Circle, I’m going to call for volunteers in a separate blog post, a little farther down the track. I’m not ready at this point to start the process of evaluating people’s CV to determine if they’re a fit for Sets 2-4. So, adding it up, 2-3 in the Inner Circle, 3-4 SMEs, 3-6 Mentors, and 9-18 monkeys, uh, I mean attendees (and yeah, my humor will have to be turned WAY down for this), we’re looking at, assuming zero overlap, 31 people. Woof! I hope there’s lots of overlap. But each course will only be this big. I want it to be a intimate experience.

  1. Pick the tools

Powerpoint. There, done.

OK, slightly more complicated. We’ll need to set up a place for discussions to take place, lots of discussions. I’m leaning towards Slack for the discussions. I’ll probably set up a task list in Trello. I’ll also need to beg borrow or steal a place to host the meetings online, but only for, max, 31 people. I’m not sure about this one. Suggestions are welcome. I think we’ll also need to have a permanent presence on the web. It’ll be a place to describe the course, but also to advertise those who accomplished everything to arrive at their… award? certificate? tramp stamp? (I know, my humor…)

  1. Legal?

I’m serious about the copyright. For this course to be something people will put on their resume, and yeah, that’s one of my own goals, there I leaked, it needs to be a Thing. For it to be a Thing, the information and delivery needs to be regulated through a pretty rigidly defined method with little deviation. Other than that, do I need legal to set up a general course? I’m not sure. This will be non-profit, so maybe a structure will have to be established once any kind of cash transactions get involved (and at least for the first course, it’s going to be free). Any advice here would be useful.

There. That’s the initial outline. Please, don’t start volunteering for anything yet. However, if you see any glaring holes in the start of this, please let me know. I will continue to post back here regularly as things progress.