![\"TSQL2sDay150x150\"](\"http:\/\/farm6.staticflickr.com\/5112\/7159832136_b5d25d8a17_o.jpg\")
<\/a><\/p>\nThis month\u2019s host is Rob Farley ( BLOG<\/a> | TWITTER<\/a>).\u00a0 And here is his request:<\/p>\n The topic is Plan Operators<\/a><\/strong>. If you ever write T-SQL, you will almost certainly have looked at execution plans (if you haven\u2019t, go look at some now. I mean really \u2013 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\u2019s going on.<\/p>\n That\u2019s 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\u2019t just quote Books Online at me, explain what the operator means to you. You could explore the Compute Scalar<\/a> operator, or the many-to-many feature of a Merge Join<\/a>. The Sequence Project<\/a>, or the Lazy Spool<\/a>. You\u2019re 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!<\/p><\/blockquote>\n 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 \u201cyeah you and 2000 of your friends!\u201d 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 \u201cstupid\u201d.<\/p>\n I fretted over every query plan, looked up every thing that had a query cost that seemed unreasonable. Now don\u2019t 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.\u00a0 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!<\/p>\n 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\u2019t know how to tell him he\u2019 just wrong. So I do what I normally do, nod and walk away letting them try to \u201cfix\u201d it. The next day I am left with my query as is because we know this wasn\u2019t 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?<\/p>\n So I didn\u2019t 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\u2019t looked a query plan like Rob said get one out, make sure it\u2019s 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\u2019t make the app sort that data in the right order, do you understand the difference in seek and scan, can\u2019t the application validate the input so you don\u2019t 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.\u00a0 As an added bonus if you are not familiar with SQL Sentry\u2019s 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!<\/p>\n Good Luck and happy Plan Exploring!!<\/p>\n","protected":false},"excerpt":{"rendered":" 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\u2019t. I was dealing with hurt feelings of having a husband who responds with \u201cI told you not to write in WordPress\u201d and a dad who was a bit eager to sit in front of me and talk while I . . . → Read More: T-SQL Tuesday #43 – Hello, Operator?<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[147,239,178],"tags":[243,233,216,308],"class_list":["post-793","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","category-tsql2sday","tag-sql-server-2","tag-tsql","tag-tsql-tuesdays","tag-tsql2sday","odd"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts\/793","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/comments?post=793"}],"version-history":[{"count":0,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/posts\/793\/revisions"}],"wp:attachment":[{"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/media?parent=793"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/categories?post=793"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/clarkcreations.net\/blog\/wp-json\/wp\/v2\/tags?post=793"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}