T-SQL Tuesday #43 – Hello, Operator?

Guess what it is that time again, time for T-SQL Tuesday. I missed last month and want to take a moment to tell you why. I had what I thought was the most beautiful post I have done so far. Heart felt, honest and encouraging for others. In a BIZZARE WordPress failure I lost the entire post. I tried so hard for the next 3 days to recreate it but couldn’t. I was dealing with hurt feelings of having a husband who responds with “I told you not to write in WordPress” and a dad who was a bit eager to sit in front of me and talk while I was trying to write. So I just gave up. No worries as I am on a roll and back on track. You see I have worked out lots of things. So here we go.

This month’s host is Rob Farley ( BLOG | TWITTER).  And here is his request:

The topic is Plan Operators. If you ever write T-SQL, you will almost certainly have looked at execution plans (if you haven’t, go look at some now. I mean really – you should be looking at this stuff). As you look at these things, you will almost certainly have had your interest piqued by some, and tried to figure out a bit more about what’s going on.

That’s what I want you to write about! One (or more) plan operators that you looked into. It could be a particular aspect of a plan operator, or you could do a deep dive and tell us everything you know. You could relate a tuning story if you want, or it could be completely academic. Don’t just quote Books Online at me, explain what the operator means to you. You could explore the Compute Scalar operator, or the many-to-many feature of a Merge Join. The Sequence Project, or the Lazy Spool. You’re bound to have researched one of them at some point (if you never have, take the opportunity this week), and have some wisdom to impart. This is a chance to raise the collective understanding about execution plans!

When I moved to TN and started my new job I was what you would have considered bright eyed and bushy tailed. I was eager to do things right, I worked harder and tried to be better always. I remember countless times me snapping at the rest of the data analyst for their poorly written queries and lack of consideration for the server/engine. They would laugh and say but I am only running this one time. Where I would come back with SP_WHO2 and say “yeah you and 2000 of your friends!” Why was I so concerned about the database? Well because I was going to marry the DBA from down the hall. Yup, I knew better than to do anything “stupid”.

I fretted over every query plan, looked up every thing that had a query cost that seemed unreasonable. Now don’t be silly I did this for anything I was either going to 1)share 2)run often or 3)dump in a report. I really never wanted to be caught with my pants down so to speak. Later I moved to the Database Engineering team thinking that all this time I spent reading and researching would make my life easier.  When you work in a fairly large shop things become a matter of the check list. Our PROD DBA team checks each DB release and basically uses a check list to make sure I have met the min requirements. And that is did you include a query plan and does it say you have a missing index? Well if either of these are true you must correct it at all cost. REGARDLESS!

Optimizing and really caring about what is going on only comes along when something is bad. Nested Loops, my current project has me in a nested loop spider web. This database is highly normalized which means I have 6-8 joins in every query. Some of these SPs are complex performing multiple task. Our dev dba was trying to help me optimize and suggested that I use inline scalar user-defined functions to get rid of the nested loops because they are bad. Oh dear, I am not a Senior DBA and I don’t know how to tell him he’ just wrong. So I do what I normally do, nod and walk away letting them try to “fix” it. The next day I am left with my query as is because we know this wasn’t going to work. This particular project has me dealing a lot with XML, oh that cost a lot but really what can you do about XML if that is what is handed to you?

So I didn’t really spill my guts on some of the operators that I read up on and understand. Any one can read the MSDN and figure these things out. But here is the important bit of advice that I think everyone dealing with SQL Server should do. If you haven’t looked a query plan like Rob said get one out, make sure it’s an ugly query. Now read through each step in each section and understand what decisions that you have made in building that query and how they impact the performance. Did you really need all those joins (honest?), really you can’t make the app sort that data in the right order, do you understand the difference in seek and scan, can’t the application validate the input so you don’t have to? Understanding that sometimes there is just nothing you can do about some of these things is also important. To be honest if someone builds a crummy data model working with it will always be crummy.  As an added bonus if you are not familiar with SQL Sentry’s Plan Explorer now would be a good time to look at it. It does more than I will probably ever have the time to learn and use but when I really need to work on something I do enjoy that tool and it is FREE!

Good Luck and happy Plan Exploring!!