TIL #4 – Five Execution Plan Patterns to Watch For w. Erin Stellato

Posted on

Having a clear afternoon, I decided to check out another Summit Session for my TIL Holiday Learning Series. This time, I chose Erin Stellato’s (b|t) session – Five Execution Plan Patterns to Watch For.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • In SQL 2012 & higher, if no rows have changed and you UPDATE STATISTICS, cached plans are NOT invalidated!
  • Session was a good reminder to think more about residual predicates/probe residual. Use F4 on an operator to see for sure if Residual exists, because residuals may not always appear in graphical plan output. Or just always use Plan Explorer (come on – it’s FREE!).
  • TF 9130 – This is useful to use while dev/test/tuning (NOT FOR PRODUCTION). It pushes residual out to a discrete operator – an extra filter.

In this session, Erin discusses five different patterns (or anti-patterns). Of those, residual predicates was of the most interest to me. When query tuning, I don’t always look for these, but this session reinforced their potential impact when evaluating query performance. It was also validating to see the other anti-patterns Erin spoke of, are already part of my mental checklist. This was definitely an awesome session for any intermediate to advanced query tuner!

TIL #3 – SQL Server in Azure VMs w. Buck Woody

Posted on

Life got in the way of yesterday’s TIL Holiday SQL Learning series, but I’m back today! Today, I watched Buck Woody’s (b|tSQL Server in Azure VMs session. Having an MSDN subscription, I’ve casually dabbled with Azure for maybe an hour or two at most, but that’s about it. I recognize that VMs are super easy to spin up, but beyond that, have not explored how it can work for me.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • IaaS vs PaaS vs SaaS
    IaaS: Infrastructure as a Service = OS on up is yours to control. Analogy – Rent a car/lease a car. You must patch & maintain. Can reuse on-premise infrastructure (ex: Active Directory).
    PaaS: Platform as a Service = Code level. You get a DB but stay off server. Analogy – Take a bus or train. Microsoft will patch & maintain.
    SaaS: Software as a Service = Ex: Office 365
  • Great walk-through Demo to set up Azure
  • Slide-deck chock full of notes – great summaries there.
  • Usage Scenarios – Dev & Test. Lift & Shift. Hybrid. Latter is interesting – Availability Group & other DR situations. Reporting.  Think about how you can mix & match in your environment.
  • Perf Whitepaper – http://go.microsoft.com/fwlink/?LinkId=306266 – Read.

This was a really informative session for me. Before, I’d always wondered how Azure could really “replace” SQL Server. But Buck makes a fantastic point – Azure isn’t necessarily there to replace an on-premise SQL Server solution. Instead, one should start thinking about how Azure can supplement/complement an existing on-premise set up. As mentioned in the Usage Scenarios bullet point – there’s many useful ways that Azure can be employed to strengthen your current enterprise. I’ll definitely be keeping these things in mind, in the future!

TIL #2 – T-SQL Refactoring w. Mike Donnelly

Posted on Updated on

Today for my TIL Holiday Learning Series selection, I decided to watch Mike Donnelly’s (b|t) Summit 2014 Session – Twice in a Lifetime: T-SQL Refactoring 101. He’s become a good friend over the years, but I’ve yet to see one of his sessions at a SQL Saturday/User Group.  So I thought watching his Summit Session would be fun. As a hardcore T-SQL developer myself, I was really curious to see what insights he had to offer.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • Mike opens by telling a personal story, which frames the remaider of the session. A story that many of us can relate to.  He then shares the process of what he had to go through.  Great technique as a presenter!
  • While discussing T-SQL anti-patterns, Mike brings up Table Variables. As an addendum, he mentions Trace Flag 2453, which aids in addressing what I argue is the biggest weakess of Table Variables – nonsense cardinality estimation of 1. There are trade-offs to using this Trace Flag, which has been blogged about elsewhere.
  • Mike presents a list of common anti-patterns.  Idea came to me, to write up a Quick Reference/Cheat Sheet, the next time I am faced with auditing a large codebase for refactoring.  While many of the anti-patterns are now ones I recognize immediately, others are more uncommon/obscure, that I don’t always remember immediately.  A cheat sheet or checklist would be a useful personal tool, or one to share with a team.  And one which I’ll add to my personal backlog of things to do (and probably blog about).
  • Mike discusses Deprecated Features and one I wasn’t aware of was Three (or more) part COLUMN naming.
    Example:

    SELECT
    schemaName.tableName.columnOne,
    schemaName.tableName.columnTwo,
    schemaName.tableName.columnThree
    FROM databaseName.schemaName.tableName

    In the above example, the column references will be limited to two parts only: object name & column name. But in non-column references, multi-part naming is still allowed.  I find this one interesting in particular, because a former colleague was using a refactoring tool, which was reformatting his code into the 3 part column name format!  Whoops!

I really enjoyed Mike’s presentation.  Instead of taking the angle of refactoring existing code, it could easily also be presented in the context of practices when developing new code.  It is definitely a solid session that I’d recommend to anyone who finds themselves writing, auditing, and/or refactoring T-SQL code.

TIL #1 – Dynamic SQL w. Jeremiah Peschka

Posted on Updated on

To kick off my TIL Holiday SQL Learning Series, I decided to watch Jeremiah Peschka’s (b|t) Summit 2014 SessionDynamic SQL: Build Fast, Flexible Queries. I’ve been using Dynamic SQL for a terribly long time now, so wanted to refresh & validate what I already knew.

NOTE: In the interest of not resummarizing each presenter’s entire session, I’m not going into great detail or documenting everything I learned. After all, I want to encourage everyone to view the session for themselves.

Interesting notes & tidbits:

  • Knew about QUOTENAME() but never about PARSENAME(). The latter is pretty neat and can be useful for other object validation scenarios.
  • Never thought of using Dynamic SQL to add/omit JOINs, based on the existence of data. This can aid in elimination of LEFT OUTER JOINs & reduce unnecessary I/O & streamline an execution plan.
  • Use a @NewLine variable to format your Dynamic SQL. CHAR(13) & CHAR(10). After all, we format our normal code (hopefully), for readability.
  • Validation – I’ve used a @Debug variable flag for a while, which is a tip Jeremiah recommended too.
  • Troubleshooting Tip – Start using comments INSIDE of your Dynamic SQL strings, particularly listing the “source.” Also use slightly different variable names inside Dynamic SQL vs outside to aid diagnosis. Ex: @MyVariableOne vs @my_variable_one.
  • In the final demo, Jeremiah shares an awesome approach to making PIVOT much less painful. Watching the entire presentation is worth this demo alone!

I really enjoyed Jeremiah’s presentation.  Thrilled that I was able to glean some new insights, while validating techniques I’ve already been using.  If you ever use Dynamic SQL, be sure to check it out!

TIL – My SQL Server Holiday Training Challenge

Posted on Updated on

While I am between jobs, I have three weeks off! Whoo woo, when was the last time you had that much time off? Some of it will be consumed by Christmas & New Year’s holiday related shennanigans, but there’s still tons of time to fill. In addition to video games and house projects, I also thought about what I can do that’s SQL Server related. Conveniently, PASS released the Summit 2014 Session Recordings last night! And that gave me an idea.

Starting today, I am challenging myself watch a Summit session, then try to write a short TIL (Today I Learned) blog about it – even if it’s just a few short notes and thoughts. I know that I realistically cannot watch one EVERY single day, so I am challenging myself to reach a goal of watching 15 sessions. I have 23 calendar days to accomplish this.

And if you missed the announcement, PASS has made the Summit Session Recordings available for purchase, regardless of whether you attended Summit or not (though costs differ if you did/didn’t). Check it out here! And if you ask me, $495 is a BARGAIN for all of the content you get access to!

Time to get my SQL Learning On!

  1. TIL #1 – Dynamic SQL w. Jeremiah Peschka
  2. TIL #2 – T-SQL Refactoring w. Mike Donnelly
  3. TIL #3 – SQL Server in Azure VMs w. Buck Woody
  4. TIL #4 – Five Execution Plan Patterns to Watch For w. Erin Stellato

T-SQL Tuesday #61: Giving Back

Posted on

https://sqlbek.files.wordpress.com/2014/05/tsql2sday150x150.jpg?w=705

Welcome back for another edition of T-SQL Tuesday!

This month, Wayne Sheffield (b|t) is our host! His chosen topic is Giving Back. More specifically, he asked each of us to think & blog about what we plan to do in 2015.

Chicago Suburban User Group

Coincidentally, Lowry Kozlowski & I toured our new meeting location at DeVry last night. Then over dinner, we started making various plans for the first half of 2015. We brainstormed about new ideas and how to better serve our User Group audience.

SQL Saturday Chicago

Volunteering at SQL Saturday Chicago a few years ago, wound up being a pivotal decision, which lead to many other awesome opportunities within the SQL Server Community. I am happy to count myself amongst the Organizing & Planning staff, and looking forward to a fantastic event in 2015!

Speaking

I’ve had a phenomenal time speaking at SQL Saturdays and User Groups in 2014, and have no intention of slowing down. My first presentation, Every Byte Counts, has far more interest than I ever could have imagined! I’m looking forward to presenting it to others throughout 2015. In fact, I’ll be starting 2015 with Nashville’s SQL Saturday in January! I have ideas for my next two presentations that I intend to develop & debut in 2015.

That’s a brief outline of my plans to give back to the SQL Server community in 2015. Thank you to everyone in the community, who have given something to me. I encourage all of you to give back in some way. You don’t have to do anything extravagant. Consider volunteering at a SQL Saturday or User Group… or hang out on Twitter & chime in on #sqlhelp questions that you can answer! We all benefit and grow by being a part of this community!

New Opportunities

Posted on

It’s not quite the end of 2014, but I must say that it’s been a year full of new opportunities. In March, I began Presenting. In the summer, I rebooted the Chicago Suburban SQL Server User Group. And now, I’ll be embarking on a new opportunity.

I am thrilled to share that I’ll be Rise Health/Best Doctors as a Senior Database Developer, starting in January. I’m really excited to face new challenges & help them leverage SQL Server to its fullest. And I’m really excited to work with new colleagues, and to introduce them to the SQL Server community and our knowledge-sharing resources.

I noticed in the past few weeks that I’ve seen a number of “new job” announcements from other #SQLFamily. As excited as I am for myself, I am excited for all of us and our industry in general. It is a good time to be a data professional and I strongly believe that our SQL Server community has helped all of us take our game to the next level.

Here’s to a great 2015!