sqlschool.gr logo


Articles of SQLschool.gr Team

When dynamic SQL might be the BEST way!

Antonios Chatzipavlis
Wednesday 23 February 2011

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!



03 Aug 2013 @ 12:24 AM



Όλοι έχουμε έναν πίνακα customers και μπορούμε να δούμε ότι στον Profiler τις διαφορές στην ανάλωση των resources που χρησιμοποιεί το query χωρίς το option (recompile) είναι πάρα πολλά σε CPU, Reads και duration.Με όσους συνδυασμούς παραμέτρων και να το δοκίμασα δεν έκανε σε καμία περίπτωση Index Seek αν και τα data το ευνοούν.Με το option (recompile) δεν έχω πάρει ποτέ χειρότερο αποτέλεσμα από 300% γρηγορότερο και με μεγάλη οικονομία στα Resources.Ακόμα κει στην περίπτωση που εκτελεί Index scan τα Reads που κάνει είναι υποπολλαπλάσια αυτού που το αφήνω στην τύχη του.DECLARE @MEMBID INT = nullDECLARE @EPON VARCHAR(50) = 'ΠΑΠΑ%'DECLARE @ONOM VARCHAR(50) = ‘NI%’select MEMBID, ONOM, eponfrom NEWCAMPWHERE ( (@membid is null) or MEMBID = @MEMBID) and ( (@EPON is null) or (EPON like @EPON))and ( (@ONOM is NULL ) or (ONOM like @ONOM))OPTION ( RECOMPILE )Παράδειγμα αναζήτησης τεμπέλη χρήστη σε πίνακα με 3,5 εκατομμύρια records.Results set 4429 records. CPU Reads DurationΜε Recompile 78 457 145Χωρίς 1980 16222 633Για αναζήτηση με ID άστο καλύτεραΤώρα για το sqlexec μη μου πεις ότι ο κώδικας θα βγει πιο καθαρός (σπόντα για τους ASPάδες.)

03 Aug 2013 @ 12:51 AM


Antonios Chatzipavlis

Είναι προφανές ότι θέμα parameter sniffing δες http://www.sqlschool.gr/WebCast/5/Content.aspx

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.




sqlschool.gr © 2010-2024 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.