Month: January 2018

Improving query performance in SQL Server with recompile

Jan 24, 2018 | |

This week I ran across a new-to-me option in some T-SQL code I was reviewing: OPTION (RECOMPILE). It turns out that this option improves performance by forcing SQL Server to recompile the query rather than using the plan it already generated. Wait, what? Don’t we save time by saving a query plan and reusing it? Generally, yes. When you first run a stored procedure, the SQL engine will figure out a plan for that procedure and cache the plan for reuse, thus saving the time of recomputing the it each time the procedure is run. The problem comes in when a plan is generated that makes sense for the current database state and parameters, but doesn’t make sense when those change. For example, suppose you have a table with ten million rows; different approaches are required if you want to retrieve ten of those rows vs seven million of them. Generating the query plan When the stored procedure is executed, SQL Server looks at the distribution statistics it has about the data in the tables the procedure references and uses this data to guess at the best way to run the query. Assuming that the statistics are up to date, it can generally make a fairly good guess; for example, it may choose an index seek if it expects to return a small number of rows, but a table...

Read More

Goals for 2018

Jan 4, 2018 | |

I’ve never been a believer in New Year’s resolutions; there’s a reason it’s a cliche to start a new exercise program in January and abandon it by February. Resolutions tend to be things that people would like to do…and by the end of the year, they become things that people would have liked to have done. At the same time, goals can be worthwhile – especially if they’re SMART goals. A SMART goal is one that is specific, measurable, actionable, realistic, and time-bound. SMART Goals Specific: It is clear exactly what the goal is. Measurable: It is clear whether the goal has been achieved. Actionable: It’s clear what actions need to be taken to achieve the goal. “I will be a millionaire in a year” isn’t actionable; “I will spend at least two hours per week writing” is. Realistic: The goal is something you can realistically achieve within the given time bound. Time-bound: There is a deadline for when the goal will be accomplished. For 2018, I’ve decided to set New Year’s commitments. Why commitments instead of resolutions or goals? Because I’m committing that these are things I will get done this year (and I’ve actually set up a penalty for if I fail to accomplish any of them). In 2018, I am committing that: I will publish at least one book – this will probably be either my...

Read More