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 Saturday #83

imageFrikkie Bosch opened the day with a short keynote giving an overview of Denali, talking mostly from a marketing point of view about the major features coming in the next version.

The virtual sessions were very popular, we don’t get many good international speakers down in our part of the world and the opportunity to listen to some new speakers was well received.

Benjamin kicked off the virtual sessions with his ’10 query optimiser topics for better performance’. He covered parameter sniffing, showing how the optimiser estimates row counts based on statistics and the sniffed value of parameters, cardinality estimation errors (poor stats or missing statistics) and scalar expressions (hard for the optimiser to guess rows affected). Also discussed were the missing index DMVs and DTA (the database tuning advisor)

My session on poorly performing SQL went very well. It’s a session I’ve done several times and enjoy thoroughly every time. The audience was great, lots of good questions, good comments and good fun. I ran a bit short on time to discuss the fixes for the multiple execution path problem, so I’ll just direct anyone reading to a blog post on it:

Big thanks to all the organisers, all the speakers, all the attendees. Special thanks to the international speakers who either stayed up late or woke early to give their sessions.



SQLSat83-3 SQLSat83-4

SQLSat83-5 SQLSat83-6





Q & A from 24 Hours of PASS session

Earlier this week I took part in the 24 Hours of PASS live webcast event. There were far more questions at the end of the presentation than what I could answer online, so the answers to the rest are given here. (I have edited some of the questions for spelling, grammar and readability). My slide deck and demos I was using are linked at the end. I’m not including a backup of the DB, as it’s 60MB. The schema is there, as is a SQL Data Generator project.

Q: Is the problem here that addition of checking if the variable is null? (This was related to catch-all queries. )

No, not specifically. The problem is the query pattern of <column> = <parameter> OR <parameter> = <constant> . It’s not specific to the case where that constant is NULL, though that is the more common form that I’ve seen.

Q: What are the good alternatives for catch-all queries?

If you are on SQL 2008 SP2 or later you can add OPTION(RECOMPILE) to the catch all query and get a plan that is optimal for each particular set of parameters.

On SQL 2005 and earlier, even with a recompile you would not get an optimal plan. This is because the optimizer was required to create a plan that was safe for reuse, even if it was never going to be reused.

On builds on SQL 2008 prior to SP1, the use of OPTION (RECOMPILE) worked, but there was a bug related to that which could produce inaccurate results, so use with caution.

On builds of SQL 2008 between SP1 and SP2 (or SP1 CU5), the behaviour of a catch-all with recompile was the same as for SQL 2005

If you’re using SQL 2005 (or horrors SQL 2000) or a build of SQL 2008 that did not have the recompile options working, you can use dynamic SQL (there are advantages to dynamic SQL even on the latest builds of SQL 2008). Use the parameters passed to build up a string that just filtered on the columns for which parameters were passed, then use sp_executesql to run the parameterised dynamic SQL statement.

Details are available on my blog –

Q: If you don’t have access to run the execution plan, but have to have DBAs run it for you, is there a good way to have it captured?

Absolutely. In SQL 2005 and above, the execution plan is an XML document. You can save the plan as a .sqlplan file. That file can then be copied, emailed, etc. You can open it in management studio and SSMS will display the graphical plan.

Or, you could download Plan Explorer and open the plan in that.


Upcoming presentations

It’s looking like it’s going to be a busy year.

It’s only January and I already have three presentations lined up for the first quarter of the year.

  • On the 10th February I’m presenting on database corruption for Quest’s Pain of the Week webcast. I’m not Paul Randal, but I think I might be able to muddle through the topic without making too much of a fool of myself.
  • 27th February I’m speaking at SQLSaturday 65 in Vancouver! First trip to Canada, first SQLSaturday attendance. I’m doing a presentation that’s an old favourite of mine – Introduction to Indexes. It’s very much aimed at beginners, so don’t expect any dark magic or mystical secrets
  • 15th March I’m taking part in the 24 Hours of PASS. The session is titled “Bad plan! Sit” Thanks to Steve Jones (blog|twitter) for the initial idea.It’s going to have something to do with bad execution plans. I haven’t decided exactly what yet. I guess that means it will be a surprise.

In addition to these, I’m also presenting at both the February and March meetings of the South African SQL Server usergroup.

That’s definitely enough work for the next two months…

Redgate's Exceptional DBA competition

Redgate’s Exceptional DBA competition is back for a third year! I was one of the judges for this last year and, while I’m not judging it this year, I do have some advice for anyone considering entering.

Be Explicit and detailed

This is not a competition won by luck. There are no dice rolled, no coins tossed, no numbers drawn from a hat.

As an entrant, you need to convince the judges that you (or the person you are nominating) are the best of the best. The only thing that you can use to do that are the answers on the entry form.

The more the better (within reason). To give an idea, last year the answers to one question (What’s the hallmarks of an exceptional DBA?) ranged from one word to half a page. Which of those two do you think the judges rated higher?

If you can, get a colleague to read over your answers before submitting them. Ask them for their opinion, ask them if there are any pieces that they’d change or add to show you (or the person you are nominating) in their very best light.

Spell check

Please, please, please run a spell check and grammar check over your entries before submitting. This goes double if English (or American) is not your first language. There is nothing that makes an entry look bad more than por speeling thet teh jugdes mast spand tyme desifering.

No, not all of us speak English fluently, but there are enough grammar and spell checkers available (hint Firefox includes one if you download the dictionary) that not bothering shows a lack of interest and professionalism. Besides, if the judges can’t work out what you’re saying, they’re not going to rate your entry highly.

On this point, watch the l33t speak and SMS/twitter style word-shortening. They’re harder to read that fully written out words, and space is not at a premium for these entries. Again, you should be trying to show that you are a professional, much like you would when writing up a CV.

Watch the humour

What’s funny for one person may be annoying or offensive to another. A joke about ‘cleaning up after those incompetent developers’ may not be funny to a judge who is a developer or comes from a development background. Again, keep it professional, imagine that these answers are going to be seen by the CIO/owner/MD of the company you work for.

Along the same lines, funny answers aren’t. One entry last year, for the question “Why do you deserve to win?”, gave as an answer “No idea ;-)”

Well dude, if you don’t know why you should win, I sure as hell don’t.

In Conclusion

If you’re planning to enter this competition, you have one chance to make an impression with the judges – your answers. Make it the best impression that you can.

SQL Server Usergroup – February meeting

The February meeting of the SA SQL Server usergroup will be on the 16th of February 2010. Venue and time are the same as always, 18h30 at the Microsoft offices

This month, Richard Sweetnam, one of Microsoft SA’s Premier Field Engineers will be presenting on Tips and Tricks for Management Studio.

Hope to see you all there.

South African SQL Server Usergroup – October meeting

It’s our 1st birthday this month! There will be cake and prizes. I have three autographed SQL books and a backpack laptop bag to give away this month.

The meeting will be in the usual location – the Johannesburg Microsoft offices, 3012 William Nicol Drive, Bryanston. 18h30 on Tuesday the 20th October. I’ll be presenting te a session entitled “Lies, damned lies and Statistics”. This session will also be presented in 4 weeks  time at the PASS Summit in Seattle. Consider this a sneak preview.

Please let me know ASAP if you are coming. We need accurate attendance numbers or there won’t be enough cake. :-)

TechEd Online Interview

Back in August at TechEd Africa I did a TechEd Online interview with Frikkie Bosch. Frikkie’s the marketing manager for the Server products down here in South Africa. We discussed some common mistakes that I’ve seen regarding SQL Server performance. I’m not talking specifics of query/table design here, but rather mistakes at a higher level.

The interview is available on the TechEd Online site. I’m interested in what people think, am I on the mark or completely in the wrong ballpark?