Posted @ 2/20/2011 9:31 PM By Kimberly L. Tripp
Posted in SQL Server Magazine

Πριν μερικές μέρες είχα δημοσιεύσει ένα άρθρο “Dynamic SQL ή μήπως μπορώ και αλλιώς;”. Σήμερα διάβασα το παρακάτω άρθρο της Kimberly Trip το οποίο καλύπτει μια άλλη πτυχή την οποία δεν είχα καλύψει στο δικό μου. Και τα δύο άρθρα είναι σωστά και έρχονται να επιβεβαιώσουν ότι τίποτα μα τίποτα δεν είναι defacto στον SQL Server. Στην ουσία για μένα είναι ακόμα μια επιβεβαίωση της γνωστής απάντησης που σε αρκετές περιπτώσεις δίνω και δεν είναι άλλη από το ΕΞΑΡΤΑΤΑΙ. Τα πάντα είναι θέμα ποιότητας και ποσότητας δεδομένων.!!!

Question: This is a follow-up question to the Q&A post: Statistics, literals, recompilation… confusion! posted last week.

Would it actually be better to use OPTION(RECOMPILE), rather than dynamic SQL (DSE), as in reality, both options are likely to re-compile each time, and forcing re-compilation (with OPTION(RECOMPILE)) does not have the security implications of dynamic string execution?

Answer: Yes and no. Unfortunately, using only OPTION (RECOMPILE) as a single/uniform solution can be even more problematic. While recompilation can be helpful for some statements, it’s a waste when it’s unnecessary, it may eat up too much CPU and there are some statements that it just cannot help. In fact, if you have a system with a lot of dynamic statements and/or recompilations – you might make things worse. As a general practice I try not to use it and I tend to use it sparingly – only if I can’t come up with a better solution. For example, the optimal execution plan for some statements can vary between table scans and nonclustered index with [bookmark] lookups because of the volatility of the parameters *AND* the fact that a better index doesn’t exist. Sometimes I can create an index (usually a covering index) and make the plan more stable/consistent without requiring recompilation.

And, there are other cases where OPTION (RECOMPILE) doesn’t help the execution plan because it’s the statement itself that is the problem. I see this often. As a generalization, I see this when the supplied parameters vary and a single procedure has been created to try and satisfy all of the possible parameter combinations (some of which might not be supplied at all). Usually the WHERE clause looks something like the following:

WHERE (ColumnX = @variableX OR @variableX IS NULL)
AND (ColumnY = @variableY OR @variableY IS NULL)
AND (ColumnZ = @variableZ OR @variableZ IS NULL)
…

When the procedure ends up looking like this, I call it a multi-purpose procedure. The simple fact is that when the Transact-SQL statement itself is poorly written then often even recompilation can’t help. There are some cases where building the statement dynamically yields better results - especially in cases where the number of supplied parameters (vs. those which are null) changes from execution to execution.

It’s in these cases where I BUILD the exact statement (with only the non-null parameters) instead of one that includes numerous variables that are set to NULL. To execute the constructed statement I’ll use either: dynamic string execution or sp_executesql. Choosing which one to use correctly requires knowledge of your data, some plan analysis and thorough testing.

As a simple rule of thumb, use sp_executesql if the plan chosen by each specific set of parameters is consistent (you’ll only know this through thorough testing). When the distribution of the data for specific parameters causes the plan to change, then use dynamic string execution (EXEC(‘string’)) rather than sp_executesql to execute the statement. And, you’ll need to protect your code from SQL Injection. To do this effectively often requires other features such as: string protection (w/REPLACE and/or QUOTENAME()), EXECUTE AS, and login-less users.

For more information on protecting from SQL Injection, check out this post: Little Bobby Tables, SQL Injection and EXECUTE AS

For more information on multi-purpose parameters, check out this post: Stored Procedure Parameters giving you grief in a multi-purpose procedure?

This is definitely a complicated subject and it requires three key things:

1. Knowing your data
2. Knowing your workload
3. Knowing how SQL Server works

It’s that last one that people aren’t always looking out for. I hope this posts helps you get closer!

Enjoy,
kt