Month: October 2013

Everyone loves reading a good book, especially about SQL Server.  I’ve been on a training kick lately and the timing of this book club was perfect.  Denis Gobo (b|t) has experience starting a book club where he works and decided to bring it to the SQL Server community.  The goal of the club is to read a chapter each week and discuss things you learned, liked and disliked.  You can ready Denis’s original post here.  The first book chosen was written by Grant Fritchey (b|t) and is titled “SQL Server Execution Plans, Second Edition”.  You can get a free electronic copy or purchase a print copy from simple-talk.com.

Continuing with my review of Chapter 4, here are my thoughts on Chapter 5.  The optimizer is very good at what it does,  but no one is perfect.  There may be rare cases where the optimizer needs a hint to force a certain type of behavior.  Although this may bring immediate gains in query performance, over time, the new plan generated may cease to function better than the what the optimizer can compile on the fly.  The reason for this is data change.  As the data changes, statistics are updated and the selectivity of the data changes which causes indexes to become more or less useful.  This is why using hints is dangerous and should be used sparingly.

Things I learned

Besides using the occasional evil hint ‘WITH (NO LOCK)’, I don’t use hints.  Partly because I didn’t understand the query plans that well before reading this book and I had lots of respect for the optimizer.  Now that I understand how execution plans are generated, I can see a useful need for hints, testing.  To be able to change the behavior of the optimizer by using a Query, Join or Table hint is a powerful learning tool.  You can start from the optimizer’s auto generated plan and start poking it with hints to see how the I/O and query time changs.

If I were to start using hints, I would definitely see a need for a tracking document or metadata query to reevaluate the execution plans.  If the tables involved are static in nature, you may not need to bother with reworking the query plan hints but once a year.  On the flip side though, a monthly review may be necessary if the query is pulling data from volatile tables.

Likes

Even though controlling execution plans with hints is frowned upon, Grant did a great job going through examples of each hint and how you can use them to better understand the optimizer.  Grant may it very clear throughout the chapter to use hints at your own risk, but if you find a select operator has timed out looking for the best plan you may have an opportunity to use hints.

Dislikes

As with most chapters thus far, no complaints on the material presented.

On to chapter 6!

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]

Everyone loves reading a good book, especially about SQL Server.  I’ve been on a training kick lately and the timing of this book club was perfect.  Denis Gobo (b|t) has experience starting a book club where he works and decided to bring it to the SQL Server community.  The goal of the club is to read a chapter each week and discuss things you learned, liked and disliked.  You can ready Denis’s original post here.  The first book chosen was written by Grant Fritchey (b|t) and is titled “SQL Server Execution Plans, Second Edition”.  You can get a free electronic copy or purchase a print copy from simple-talk.com.

Continuing with my review of Chapter 3, here are my thoughts on Chapter 4.  This chapter was a deeper dive into some of the more complex plans that can be generated from T-SQL statements like APPLY, CTEs, MERGE, views, as well as how indexes are chosen.

Things I learned

Writing queries is an art form, you need to understand all the T-SQL statements and how they affect the query plan.  Don’t get stuck on always using one technique over another.  For example, adding a WHERE clause to a sub-select may out perform an APPLY statement (based on the data set).  The point here is by understanding how each T-SQL statement will interact with your data, you can try different approaches to solving your problem and reach the results in the most efficient way.

Using ROLLBACK can be a useful approach when needing to view the actual execution plan for UPDATE, INSERT, DELETE, or MERGE statements.  This allows the actual query plan to be generated but the DML is not committed to the database allowing you to repeatedly change the query to generate the best quality plan.

Likes

Grant did a great job of teaching how to dissect large query plans.  Just like when you are trying to each an elephant, you need to approach large query plans one operator at a time.  I also thought the way Grant explained views and indexes was very thorough.  I now have a better understand of when a view might be expanded to the table level or when an index might be passed over because of bad statistics.

The other thing I noticed is how Grant was able to sharpen our understanding of T-SQL and database fundamentals in the process of this chapter.  Being able to learn how the query optimizer generates plans while learning better uses of sub-queries, APPLY, CTEs, MERGE, views, and indexes is a great thing.

Dislikes

As with most chapters thus far, no complaints on the material presented.  Just a minor typo on the T-SQL Listing 4.7.  The parameter being passed to the SP should be @BusinessEntityID and not @EmployeeID.

On to chapter 5!

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]