I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it. Lastly, we’ll create a stored procedure to use for testing that is created with RECOMPILE and then completely free procedure cache. Dynamic Search Conditions in T-SQL (Erland also).The Curse and Blessings of Dynamic SQL (Erland Sommarskog).Protecting Yourself From SQL Injection - Part 2.Protecting Yourself From SQL Injection - Part 1.This is pretty safe from SQL injection, since you don't have to worry about concatenating user input into SQL strings (all parameters are strongly typed), but it can't hurt to read these topics on dynamic SQL: (Yes, with OPTION (RECOMPILE), the point is moot however, the server setting can't hurt for the rest of your ad hoc query workload, and I've never come across a downside to having it on.) Essentially what this does is prevents your plan cache from filling up with all these slight plan variations, unless they are used more than once. This typically works best with the server setting optimize for ad hoc workloads, which you can read about here and here. The OPTION (RECOMPILE) at the end protects you from plans that can vary greatly based on the values of the same parameters from execution to execution (for example, WHERE name LIKE N'%s%' should yield a different plan shape than WHERE name LIKE N'Q%'). This approach of only adding clauses for parameters that are actually supplied protects you from caching plans based on different sets of parameters (for example, if I supply on first execution, the seek plan on that column that gets cached isn't going to help when I ask for LIKE N'%s%'). PRINT sys.sp_executesql VARCHAR(20), VARCHAR(50), INT. SET += N' AND verifier_id = + N' OPTION (RECOMPILE) ' SET += N' AND instance_name = IS NOT NULL You didn't show the rest of your code, only that you were already using the local variables trick, but it will essentially look like this: DECLARE NVARCHAR(MAX) = N'SELECT. (For a whole lot more on this topic, see this great post by Paul White.)Īlso, if many parameters are optional (so the query has things like WHERE col = or IS NULL), this is what I call "the kitchen sink" - sometimes dynamic SQL can be a much more effective solution.
#Sql server recompile stored procedure code#
And I wouldn't recommend the local variables "trick" - it just makes the code messier better to use OPTIMIZE FOR UNKNOWN on modern versions if that's the method that works best in your scenario. You'll want to use OPTION (RECOMPILE) on the statement(s) with issues, not WITH RECOMPILE on the procedure.
Michael Green is right: the devs are trying to thwart parameter sniffing, which happens when SQL Server compiles a plan that is great for one set of parameter values, but horrible for others.
0 kommentar(er)
