Recent Articles:

PASS Summit 2014: Recommendations and Invitations (#sqlpass #Summit14)

PASS Summit 2014 Banner_200x200The first week of November, we’ll be at the PASS Summit inSeattle, WA.  This is the highlight of most SQL Server speakers’ years…a sort of educational, Christmassy nerdfest, with a feel of a class reunion mixed in. We have a lot of fun, and so I’d like to draw your attention to some of the better bits.

Taking part, if you can’t attend

The keynotes and luncheons are usually live broadcast, and you can order session recordings to cuddle up with at home. I suppose you can put on a kilt, go hit a karaoke bar with friends, drink too much, and contract the nerd flu, if it’ll make you feel more like you were at Summit. That’s what I’d do.

Some of the best stuff, if you’re there

Before the Summit starts:

During the Summit:

  • Get an invitation to High Tea 2014, our very fun and inappropriate party, if it turns out that’s your thing. (Requests and speaker abstracts due October 15!)
  • Hit a luncheon or two:  Women in Technology, and/or the Birds of a Feather.
  • Drop by the evening events. The Welcome Reception is usually worthwhile, and the Community Appreciation Party is at the quite awesome EMP Museum this year!
  • Check the After Hours page for the Photowalk, karaoke, Kilt Day, Table Top Game Night, and more.

Our Sessions

On Wednesday November 5 ( 3:00pm, room 602-604), Sean is presenting Performance Tuning Your Backups. This is quite possibly his best and most mind-blowing session. If you’re attending the Summit and you haven’t seen it, I really think you should.

On Thursday November 6 (1:30pm, room 401), Jen is presenting How to Interview a DBA: A Panel Debate, along with

This is a set of people with very strong opinions, so I fully expect it’s going to be a very interesting panel debate. Just the way we like it.

Happy days,
Jen McCown

http://www.MidnightDBA.com/Jen

Tip: Concatenate rows with XML, or COALESCE

Edit: I went and changed the first query without paying close enough attention. I’ve fixed the explanation and the query.

Concatenate rows with XMLMoonAndHEROnly

I still reference my Forgotten T-SQL Cheat Sheet, especially for the XML trick that turns a set of rows into a list. The T-SQL Cheat Sheet example uses a parent-child table combination, but what if we want to get the same effect from a single table? Here:

SELECT	DISTINCT Person ,
		Books = STUFF(( SELECT	',' + Book
				FROM	Books AS B2
				WHERE B2.Person = B1.Person	
				ORDER BY Book
				FOR XML	PATH('')
				), 1, 1, '')
FROM	Books AS B1;

 

This works for cases where you need several rows of comma delimited lists. So, data like this:

PERSON BOOK
Bob Book1
Bob Book2
Bob Book3
Tom Book7
Tom Book9

 

Could be easily run through the above query,to get a result like this:

PERSON BOOK
Bob Book1,Book2,Book3
Tom Book7,Book9

 

Concatenate rows with COALESCE

Now, a simpler case to concatenate rows is to just use COALESCE. It won’t give you multiple rows, but sometimes you just need a single, simple list:

    DECLARE	@Books VARCHAR(8000);
    SELECT @Books = COALESCE(@Books + ', ', '') + Book FROM Books;
    SELECT @Books as AllBooks;

If we used this on the same data set, we’d get this:

AllBooks
Book1,Book2,Book3,Book7,Book9

Just a quick reminder from your friendly neighborhood SQL-woman.
Happy days,
Jen
http://www.MidnightDBA.com/Jen

Why don’t they make the optimizer faster? (An essay on nested views)

A tired optimizer gives up on nested views

A tired optimizer gives up on nested views

We got a question recently that basically boiled down to this: Nested views shouldn’t be such a performance suck. Why doesn’t Microsoft make the optimizer deal with nested views better?

We’ve looked into this a good deal – nested views are one of my pet peeves, and I’ve blogged about it at least a couple of times (see Detangling Nested Views and Proving Views Can Hurt Performance). What it boils down to is, detangling nested views is way harder than you would think, even for a computer.

“It’s not that hard…”

The straight set math gets exponentially more complicated with every additional level of nesting. You can see this pretty easily if you put together a nested view of 2 levels deep, vs 3 or 4 levels, and then try to pick apart the logic. Plus, each view references anywhere from 1, to some ludicrously high number of tables, often repeating joins to the same tables in different ways. So, it’s even more complex than it first appears to a casual observer.

Go ahead, take an afternoon or seven and try refactoring a nested view manually. You’ll start to feel a lot sorrier for the optimizer.

“Yeah but, the computer could do it, right?”

I’ve talked to the optimizer team about this sort of thing, because it really does seem mathematically possible to work out the best possible plan for a nested view…something that eliminates unnecessary joins, simplifies duplicate result sets, and so on.  I’d be willing to bet that it is  mathematically possible. But the query optimizer itself gives up trying to find a better plan after a certain amount of time*, and that’s by design. In most cases, it’s not worth it to spend 20 seconds (or 90 minutes) finding the perfect, best optimized plan, if you’re taking the execution time from 4 seconds down to 3 with that better plan.

(By they way, take a quick look at this blog post by Jason Strate…pay attention to the bit with “StatementOptmEarlyAbortReason”…what we’re talking about is either “TimeOut” or “GoodEnoughPlanFound”: http://www.jasonstrate.com/2010/12/can-you-dig-it-stmtsimple-element/ ).

So yeah, most likely the optimizer could certainly find some absolutely fabulous plan through the much of a deeply nested view, but it simply isn’t worth it in most cases. It quickly gets to the point where an optimization like that might (theoretically) take hours, if not days to work out the perfect plan. And then in the future, when the plan is pushed out of cache, the optimizer will have to do it all over again. And then, again. And on it goes.

“Well FINE then. So what do I do with these 9-layer views?”

It might be nice if MS gave us the option of turning on a “find best plan at all cost” settings, if only for troubleshooting and refactoring, but it certainly hasn’t happened yet.  I myself have taken a couple of pokes at starting a tool that could do some of the refactoring for you, but only ever got so far as to list out the referenced elements of a nested view.  In all, your best bet is to take the time to work out what it is that the system needs, and refactor your nested views yourself. Your queries, and your optimizer, will thank you for it.

Happy days,
Jen McCown

 

* Technically this isn’t really a timeout, but a number of attempts to find the plan. The number of attempts the optimizer makes depends on query complexity. Once it runs through that number of attempts, it gives up…but they call it a timeout. Whatever works, I guess. Thanks to Grant Fritchey for that clarification!

Speaking of Grant: For an excellent article on how the SQL Server optimizer chooses when to give up on your ridiculously overcomplex unique snowflake of a query, see Grant Fritchey’s post “Reason for Early Termination of Statement“.

Minion Reindex by MidnightDBA is here!

 

Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!


 

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/