Pass Summit 2015

It’s two weeks until Pass Summit 2015!

It feels a little weird this year, it’s the first year in quite some time where I’m not presenting in Seattle. The reason for that’s pretty simple, I didn’t submit any abstracts. It still feels strange to be going as an attendee, to be able to pick and chose sessions as I like, rather than having to work around the slots that I have to speak in, and to be able to spend time just wandering around the conference rather than rushing from place to place as is usual.

Still, that gives me a good opportunity to attend sessions I’m interested in. Looking over the schedule, my 5 must-attend sessions are (in no particular order)

Plus the keynote with Dr DeWitt and Dr Nehme is an event not to be missed!

Of course, there aren’t just the sessions. There’s the Birds of a Feather lunch, the Women in IT lunch, the SQL Clinic and the CAT team to ask difficult questions of, and the hundreds and thousands of ad-hoc conversations in the corridors, many of which may be more interesting than the sessions themselves. Not to mention the multitude of parties and events in the evenings.

Oh, and there will be Crazy Hat day as well. For those who don’t know what I’m taking about, it’s Argenis Without Borders, version 2. Please donate if you can, I really want to see Argenis dancing to music from a trombone.

All about Execution Plans

Coming to the PASS Summit in October this year? Excellent!

I say excellent, because Grant (blog|twitter) and I are presenting a full-day seminar on the Monday, all about execution plans. Excited already? If not, let me give you a taste of what we’ll be discussing.

Grant will be kicking things off with a look at how the optimiser works. Not 500 level deep internals (that’d take all day by itself), but an overview of how the optimiser turns this

SELECT t.Title, u.UserName, u.UserLevel, t.LastModified, t.LastPoster
FROM Threads t
INNER JOIN Posts p on t.ThreadID = p.ThreadID
INNER JOIN Users u on p.Poster = u.UserID
WHERE t.ThreadID = 42

into this


as well as some DMVs that give a view on what the optimiser is doing.

After that, I’ll be digging into what the plan cache is, how it’s structured and how plans are matched to incoming queries. I’ll also cover how you might go about monitoring SQL’s usage of the cache.

After that we’ll discuss the myriad and varied ways to get execution plans out of SQL and what the various different options for that return and what some of the terms involved really mean (estimated execution plans anyone?).

Once all that’s out there (which will probably take the entire morning) it’ll be onto the fun of reading the execution plans, what those various icons represent and what all their arcane properties are telling you. Maybe, just maybe we’ll also have a look at the raw XML form of the plan, just for fun.

And because theory’s not very useful without something to apply it to, we’ll be ending off the day by seeing how you can use the information from the execution plan to tune your queries. After all, everyone wants better performance, right?

Still not completely convinced? Then check out the 24 hours of PASS coming in September. Both Grant and I are doing a pre-con preview. I’m doing extracting execution plans and Grant’s doing a portion of the reading execution plans section.

Right, now that everyone’s dying to attend this, I’m going to toss out a little bit of a challenge. If you’re coming to our precon and you have an execution plan that you don’t understand (maybe a strange operator, maybe a property with a value you’ve never seen), mail it to me (gail@<blog domain>) with a note as to what you want clarifying and we might discuss it during the pre-con. Some rules, just to keep things sane

  • No confidential stuff. I don’t want legal trouble, neither do you.
  • If the exec plan takes 5 minutes to open in SSMS, I’m deleting it.
  • If it’s multiple pages in SSMS, I’m deleting it.
  • I don’t promise to look at anyone’s plans, it depends how we’re doing on time.

PASS Summit Session Votes

I blogged a week ago about the sessions that I submitted for the PASS Summit this year. At the point that I scheduled the post I hadn’t seen the announcement from PASS, hence a second post on the same topic.

This year, you can vote for sessions that you want to see. How much influence this will have over the Program Committee isn’t defined anywhere, but anything is better than nothing.

Voting closes on the 20th May (Friday), so if you have any interest in what gets presented at PASS Summit this year, go over to the speaker preference survey and vote.

Oh, and to make things easier, if you want to vote for any of my sessions, they’re here

Pass summit abstracts

I missed the Pass summit last November for a number of reasons, but I’m hoping I can attend this year. Given that, I submitted a number of abstracts for consideration.

The limits on number were as follows: Up to 4 abstracts for regular, spotlight or half-day sessions (ie main conference sessions) plus, providing some requirements are met, up to 2 abstracts for pre-con sessions.

I submitted five abstracts, four were ones I’ve been thinking about for some time, the last was a last minute surprise.

Bad plan! Sit!

Bad execution plans are the bane of database performance everywhere they crop up. But what is a bad execution plan? How do you identify one in your system and, once identified how do you go about fixing it?

In this top-rated session from the 24 Hours of PASS we’ll look at some things that make a plan ‘bad’, how you might detect such plans and various methods of fixing the problem, both immediately and long-term.

I wasn’t planning on submitting this one to be honest. It’s one I did for the 24 hours of Pass back in February, and I didn’t think it appropriate to redo at the Summit the same year. Some people at Pass disagreed with me on that. The session was one of the top 3 from 24 hours of Pass and as such got a guaranteed slot at Pass summit, and a chance for a Spotlight slot.

Is that a parameter I smell?

All too often a forum post on erratic query performance is met with a reply ‘Oh, it’s parameter sniffing. You can fix it with .’ The problem with that answer, even if it has identified the cause, is that it’s only part true. Parameter sniffing is not simply a problem that needs fixing, it’s an essential part of well-performing queries. In most cases.

Come to this session to learn what Parameter Sniffing really is and why it’s a good thing, most of the time. Learn how to identify the scenarios where it’s not good, why a feature that is supposed to improve query performance sometimes degrades it, and what your options are for resolving the problems when they do occur.

Performance improvements in 60 min or less. Guaranteed

The system is slow, users cry
It’s impacting our bottom line
In meetings the curses fly
The situation is far from fine

While up on the IT floor
The DBA tears his hair
He’ll soon be shown the door
If he cannot the performance repair,

It often seems, from looking at forum posts and client requests, that the steady-state of databases is ‘too slow’ and all too often the people who are tasked with resolving performance problems are overwhelmed by the shear scope of the problem, and aren’t really sure where to start.

In this demo-heavy session, we’ll look at a fictional company’s database and website and work through finding the worst offenders in terms of poor performance, identifying the causes of the problems and at least starting to get the queries to perform better and the users to stop phoning and complaining.

Yes, part of the abstract is written in verse. It’s something I was threatening to do since last year, and I decided that it shouldn’t harm my chances, much.

Dos and don’ts of database corruption

Database corruption is one of the worst things you can encounter as a DBA. It can result in downtime, data loss, and unhappy users. What’s scary about corruption is that it can strike out of the blue and with no warning. If maintenance is not being done regularly on the database it’s easy for corruption to go unnoticed until it’s too late to repair without losing data.
In this session we’ll look at

  • Easy maintenance operations you should be running right now to ensure the fastest possible identification and resolution of corruption
  • Best practices for handling a database that you suspect may be corrupted
  • Common actions that can worsen the problem.
  • Appropriate steps to take and methods of recovery

I did this session initially for Quest, for their Pain of the Week webcast back in February. I enjoyed doing it a lot, and it got some good feedback, so I decided to submit it for Summit as a contrast to my usual performance-related presentations.

All about Execution Plans

Last, but far from least…

Grant Fritchey (blog | twitter) and I submitted a join pre-con session on execution plans. This is an area Grant is a well-known expert on, having written a book on it, and we’re hoping that we’ll be given the chance to devote a full day to this topic.

The key to understanding how SQL Server is processing your queries is the execution plan.

This full day session focuses on the execution plan. We will start right at the beginning and talk about the compile process. We’ll also go over how, and more importantly, why, plans are stored in cache and how they are removed.

We’ll spend time exploring the key differences between actual and estimated plans, and why those descriptions are more than a little misleading. We’ll also show you assorted methods to obtain a query’s execution plan and what the differences and tradeoffs of each are.

A full day class on execution plans would not be complete without spending time learning to reading them. You’ll learn where to find useful information in execution plans, what the common operators are and how to decipher the sometimes cryptic messages the plans are sending to you. We’ll also debunk some myths surrounding query operators and execution plans.

All of this is meant to further your understanding of how queries work in order to improve the queries you’re responsible for. With this in mind, we’ll show how you can use execution plans to tune queries. All of the information presented will be taken from real world examples. We’ll build on the information through the day so that at the end, after following us through multiple examples at your own computer, you’ll have a stronger understanding of how to read, interpret and actually use execution plans in your day-to-day job.

SQL Pass session evaluations

I finally got the last of my PASS Summit session evals and so, like some other people, I thought I’d make them public.

Lies, damned lies and statistics (DBA-388-S)

This session went very well. I was comfortable with the material, it’s a topic I really like and in general it felt, to me at least, like a good session. The ratings seem to agree with that.

Very Poor Poor Average Good Excellent
How would you rate the usefulness of the session information in your day-to-day environment? 1 7 36
How would you rate the Speaker’s presentation skills? 3 5 36
How would you rate the Speaker’s knowledge of the subject? 4 40
How would you rate the accuracy of the session title, description, and experience level to the actual session? 5 39
How would you rate the amount of time allocated to cover the topic/session? 11 33
How would you rate the quality of the presentation materials? 1 7 36

If I make Very Poor = 1 and Excellent = 5 then, averaging all the scores over all the questions, overall that session rated at 4.82/5

Not bad at all.

Edit: The overall PASS Summit session ratings are out and this session came in at 7th overall (all sessions including pre/post cons, all tracks) and 5th in the DBA track, behind only Buck Woody, Kimberly Tripp and Paul Randal I am extremely surprised to have come in that high at a conference like the PASS Summit.

Insight into Indexes (DBA-315)

This session was a whole different story. It did not go well at all, and I didn’t need the ratings to tell me that.

I wasn’t overly comfortable with the material. This is not to say that I didn’t know it, I did, but I wasn’t comfortable with it. In retrospect, I should have scrapped the entire presentation and done it over from scratch in a different way, even if that meant doing it the night before. Lesson learnt there.

To add to that, I broke my own rules for presentations. Usually I’m at the session room at least 5 minutes before the previous session finishes, with my laptop booted, the presentation loaded, management studio (and profiler if necessary) open and any pre-demo scripts already run. That way, as soon as the speaker who’s presenting in the session before mine finishes, I can get on stage, plug the laptop in, get the projector online and then relax.

In this case, I was late. The previous speaker had already left and my laptop was still switched off. Hence I rushed to get everything loaded and ready, and Windows, sensing the urgency, promptly crashed hard.

Cue 2 minutes of frantically trying to reboot laptop (it was ignoring all shut down requests) and load presentation onto the desktop in case my laptop didn’t reboot. All while the AV guy’s trying to get the audio on and the recording started.

Let’s just say it went downhill from there.

So, ratings for that one.

Very poor Poor Average Good Excellent
How would you rate the usefulness of the session information in your day-to-day environment? 2 1 7 23 51
How would you rate the Speaker’s presentation skills? 5 29 50
How would you rate the Speaker’s knowledge of the subject? 1 11 72
How would you rate the accuracy of the session title, description, and experience level to the actual session? 1 4 31 48
How would you rate the amount of time allocated to cover the topic/session? 6 31 47
How would you rate the quality of the presentation materials? 4 33 47

If I do the same averaging as for the first one, that comes out at 4.55. Not the worst I’ve ever had, though not by much. Lessons learnt.

PASS Day 3 and post-con

Once the pre-con was done I dropped into the SQLCAT sessions on consolidation and virtualisation. Late, because I was cleaning up at the blogger’s table (Adam wasn’t there that day). Interesting session, the best-practices were worth noting down and their graphs on performance were encouraging.

After lunch I was intending to go to Allen’s clustering session, but it was over my head, so I switched to Buck’s SQL manageability presentation

Buck Woody’s session is a laugh-riot. Anyone he knows within view gets picked on. and various groups are getting insulted. Insulted in that session

  • Me
  • British people
  • South Africans
  • Developers
  • U2 fans
  • Baptists
  • Microsoft
  • Elderly people

All in good humour, no serious insult intended.

I missed the next session, chatted with Brent Ozar. Last of the day was my session on statistics. Audience was a little tired, but the session went quite well. I asked about table sizes and someone claimed a 100 billion row table. I really do want to know what kind of data they’re storing that generates a table that size.

The post-con day was quiet, in comparison with the days before. I split time between a post-con on semi-structured and unstructured data and some insider sessions. Some cool stuff under discussion in the insider sessions and lots of good debate. The post-con was useful, the part on full-text the most. It’s not something I’ve played much with in the past.

That’s PASS over and done for another year. Same time, same place, next year.

PASS Keynote – Day 3

Keynote opened with announcements regarding the PASS board. There was a very emotional speech from Wayne Snyder as the board said goodbye to Kevin Kline, as he steps down from the PASS board after 10 years.

I first met Kevin when I attended the European conference in 2005 (in Munich). I didn’t know anyone and I didn’t know very much. Kevin was at the opening party and he was talking to everyone there, making sure that they felt welcome and that they were engaged in the conversation. There was no feeling that he was faking interest, he was genuinely interested in what people were doing with SQL.

The changes to the board are, as well as the new board members, Wayne Snyder becomes "Immediate past president" and Rushabh Metah takes the role of President.

After the board announcement we get the keynote ‘tax’, long, boring discussion by a Del person. Configuration management, consolidation. Some waffle about disaster recovery, but without any meat. Or maybe consolidation. Haven’t quite worked it out. If this was a session, he’d be talking to an empty room.

David DeWitt’s presentation is looking at the historical and future trends of database platform changes. The improvement in CPU power way outstrips the improvement in disk speed. Hopefully SSDs will fix that.

The disk trends that he’s discussing are scary. Relatively speaking (considering the size of the disks today), drives are much slower than they were 20 or so years ago. Sequential reads are faster than random by a greater factor than some years back. Random reads hurt.

CPUs are faster, waay faster than they were years ago. Accessing memory takes more cycles than historically, like 30x more.

The way databases are currently designed, they incur lots of L1 and L2 cache misses. L2 cache miss can stall the CPU entirely for 200 cycles. This is why it’s so hard to max out modern processors. They spend so much of their time waiting. What makes it worse is that the cache lines are only 64 bytes wide. If a row is more than 64 bytes wide, moving a row from one cache to another will require more than one cycle and potentially more than one cache-miss. Changing database architecture to a column-store may alleviate this.

Compression is a CPU/disk tradeoff and that’s fine. CPUs are 1000x faster now and disk only 65x faster. Hence use some of those CPU cycles to help the poor disks along.

Some very interesting discussions on compression with column-stores. Store the data multiple times in different orders. David: "After all, we need to do something with those terabyte drives"

Run-length encoding works so well with a column store, especially if the columns are stored ‘in order’. Dictionary compression is good if the columns are not in order.

Hybrid model is also an option. Some of the benefits of the two, some downsides of the two. Lot of academic papers on those options. Search Google Scholar if interested

Some photos from David’s presentation





PASS Main conference – Day 2

Too tired to post much right now.

I skipped the keynote this morning, the BI just doesn’t interest me that much. Also missed the morning session to hit the vendor hall, since I couldn’t last night.

The MVP book signing occurred around midday. Must have been around 200 books to sign. Funniest part was the comments from the other authors, putting a sql-related spin on the long queue and pile of books. Overheard “Looks like the input buffer’s filling up down there”

Also skipped the first of the afternoon sessions to sit in the “Ask the Experts” area and chat with a couple of the devs on topics around statistics. There was something I needed to clarify before my session tomorrow. Unfortunately the dev who worked on that are wasn’t there. Hopefully there’ll be some mail around lunchtime tomorrow with the answer.

My session on indexing went ok. Not fantastic, but ok. Will see if stats goes better tomorrow.

For the last session I sat in on a discussion on xquery. Starting to understand how that works, need to play more though. Don’t really agree on the ‘use xml for tables with changing column definitions’ though.

Photos tomorrow.

PASS Main conference – Day 1

So, today was the first day of the main conference and suddenly there’s 4x the number of people around.

One thing I do have to say straight off, congrats to the conference centre for actually having the wireless network working on the 1st day of the conference. Has to be the first time this has happened in the years I’ve been attending.

The keynote wasn’t as interesting as it could be. No real surprise, there’s no product announcements, no astounding features to demo (other than stuff that’s been seen before), no launch to be done. It was a discussion of where we’ve come from and where we’re going (including into the cloud) and then some demos of R2.

Bob’s level 500 session was a good way to start the conference. Maybe not as deep as the memory internals last year, but good, solid info. Useful stuff there.

The “Birds of a feather” lunch seemed to go down well. I chatted about execution plans for over an hour. Didn’t really get too deep into exec plans, other than discussing the difference between estimated and actual plans (none other than the run-time information) how statistics affect plans (I’m doing a full hour-long session on that on Thursday). Anyone interested in reading exec plans, see my blog series here or Grant’s e-book (available from SQLServerCentral)

After lunch there was a session on PowerPivot (formerly known as Gemini) and on scaling SQL beyond 64 processors. Interesting to see what kind of changes were needed to the SQLOS to handle that.

How’s these for servers?


Big Server