{"id":2767,"date":"2015-04-02T05:29:43","date_gmt":"2015-04-02T12:29:43","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/kimberly\/?p=2767"},"modified":"2015-11-03T10:41:53","modified_gmt":"2015-11-03T18:41:53","slug":"stored-procedure-execution-with-parameters-variables-and-literals","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/stored-procedure-execution-with-parameters-variables-and-literals\/","title":{"rendered":"Stored Procedure Execution with Parameters, Variables, and Literals"},"content":{"rendered":"

In Nov 2014, SQLskills made an offer to user group leaders<\/a> to deliver remote user group sessions in 2015\u00a0and we’ve been having great fun delivering them. So far the team has delivered 34 sessions and we have 72 signed up! At my last session (for the San Diego SQL Server User Group), I delivered a session on stored procedures. It was great fun and I\u00a0had a few questions at the end that made me come up with some new sample code… that’s where this post is coming from!<\/p>\n

The question was\u00a0because I said to make note of something being a parameter and not a variable (during the lecture) and so the question was – what’s the difference and how does that change the behavior?<\/p>\n

Definitions
\n<\/strong><\/span><\/h2>\n

Parameters<\/strong> are values\u00a0that are being passed into<\/em> a stored procedure. Since these are part of the call to execute the procedure; these are “known” during compilation \/ optimization (but, that’s only IF SQL Server has to compile \/ optimize,\u00a0more on that in a minute)<\/p>\n

Variables<\/strong> are\u00a0assigned at runtime and therefore are\u00a0“unknown” during compilation \/ optimization.<\/p>\n

Literals<\/strong> are known at all times as they are exactly that – a simple \/ straightforward value<\/p>\n

Review this heavily commented stored procedure:<\/p>\n

CREATE PROCEDURE ParamVarLit
\n (@p1 varchar(15)) — defining the parameter name @p1 AND the data type
\nAS
\n— Declarations \/ assignments
\nDECLARE @v1 varchar(15); — defining the variable name @v1 AND the data type
\nSELECT @v1 = @p1; — assigning the variable to the parameter input value<\/p>\n

DECLARE @v2 varchar(15); — defining the variable name @v2 AND the data type
\nSELECT @v2 = ‘Tripp’; — assigning the variable to a literal value<\/p>\n

— Note: also acceptible is this format
\n — DECLARE @v2 varchar(15) = ‘Tripp’<\/p>\n

— Statement 1 (using a parameter)
\nSELECT [m].*
\nFROM [dbo].[member] AS [m]
\nWHERE [m].[lastname] = @p1 <\/p>\n

— Statement 2 (using a variable)
\nSELECT [m].*
\nFROM [dbo].[member] AS [m]
\nWHERE [m].[lastname] = @v1; — or @v2, these will work EXACTLY the same way!<\/p>\n

— Statement 3 (using a literal)
\nSELECT [m].*
\nFROM [dbo].[member] AS [m]
\nWHERE [m].[lastname] = ‘Tripp’;
\nGO<\/p>\n

In the stored procedure you can see that there’s one input parameter, we’ll assign that when we call the stored procedure.<\/p>\n

We can assign parameters by name (line two) or we can assign them by position (note, when\u00a0you have a lot of parameters this can be frustrating)<\/p>\n

EXEC [dbo].[ParamVarLit] @p1 = ‘Tripp’ — assignment by name
\nEXEC [dbo].[ParamVarLit] ‘Tripp’ — assignment by position<\/p>\n

Outside of that, everything else is being defined \/\u00a0assigned \u00a0within the stored procedure.<\/p>\n

Setup<\/span><\/h2>\n

Now, we have to see how each of them works from an optimization perspective. To do this, I’m going to use a sample database called Credit. You can download a copy of it from here<\/a>. Restore the 2008 database if you’re working with 2008, 2008R2, 2012, or 2014. Only restore the 2000 database if you’re working with\u00a02000\u00a0or\u00a02005.<\/p>\n

Also, if you’re working with SQL Server 2014, you’ll potentially want to change your compatibility mode to allow for the new cardinality estimation model. However, I often recommend staying with the old CE until you’ve done some testing. To read a bit more on that, check out the section titled:\u00a0Cardinality Estimator Options for SQL Server 2014<\/strong> in this blog post<\/a>.<\/p>\n

So, to setup for this demo – we need to:<\/p>\n

(1) Restore the Credit sample\u00a0database<\/p>\n

(2) Leave the\u00a0compatibility mode\u00a0at the level restored (this will use the legacy CE). Consider testing with the new CE but for this example, the behavior (and all estimates) don’t actually change.<\/p>\n

(3) Update a row and add an index – use this\u00a0code:<\/p>\n

USE Credit;
\nGO<\/p>\n

UPDATE [dbo].[member]
\n SET [lastname] = ‘Tripp’
\n WHERE [member_no] = 1234;
\nGO<\/p>\n

CREATE INDEX [MemberLastName] ON [dbo].[member] ([Lastname]);
\nGO<\/p>\n

Execution<\/strong><\/span><\/h2>\n

To execute this procedure it’s simple – just use one of the execution methods above and make sure that you turn on “Show Actual Execution Plan” from the Query, drop-down menu.<\/p>\n

EXEC [dbo].[ParamVarLit] @p1 = ‘Tripp’ — assignment by name
\nGO<\/p>\n

This is what you’ll see when you execute:<\/p>\n

\"ExecutionPlans\"<\/a><\/p>\n

 <\/p>\n

Notice that the first statement and the third statement both use an index to look up the row but\u00a0statement two does not. Why? It’s all tied to whether or not the value was “known” at the time of optimization. And, most importantly – this procedure\u00a0was the executed when there wasn’t already a plan in cache. Because there wasn’t already a plan in cache, SQL Server was able to optimize this procedure for the parameters passed in at THIS execution. This does NOT happen for subsequent\u00a0executions.<\/p>\n

More specifically, when the statement KNOWS that the value is ‘Tripp’ (statement 1 knows because it’s getting optmized for ‘Tripp’ and statement 3 knows because the value is hard-coded for ‘Tripp’)\u00a0then SQL Server can look to the statistics to determine how much data is going to be processed.\u00a0In this case,\u00a0SQL Server estimates that there are very few rows with a last name of ‘Tripp’ (from the statistics,\u00a0it thinks there’s only 1). As a result, an index would be helpful to find this highly selective result so it chose a\u00a0plan to\u00a0use an index.<\/p>\n

For statement 2 though, SQL Server doesn’t seem to know to use an index. Why? Because here the variable (@v1) was unknown at the time of compilation \/ optimization.\u00a0The variable is not assigned until\u00a0actual execution\u00a0but\u00a0execution only occurs after<\/strong> <\/em>a plan has been generated. So, the problem with variables is that SQL Server doesn’t know their actual values until after<\/strong> <\/em>it’s chosen a plan. This can be both good and bad. Remember,\u00a0SQL Server has to do something<\/em>… So, in this case, SQL Server uses an\u00a0average<\/em>\u00a0to estimate the rows and come up with a plan. This\u00a0average<\/em>\u00a0comes from the density_vector component of a statistic rather than this histogram. If your data is reasonably evenly distributed then this can be good. And, it also means that the plan won’t change change after it’s kicked out of cache and a different execution occurs with different parameters. Some\u00a0have learned this trick and have used it with success – but, only because their data is either evenly distributed OR the executions are using values that all resemble the average.<\/p>\n

NOTE: This is EXACTLY the same behavior as using the OPTION (OPTIMIZE FOR\u00a0UNKNOWN) clause on the statement within the stored procedure.<\/p>\n

In this case, however, ‘Tripp’ is NOT like the average value and so the plan for statement 2 is not ideal for a variable assigned to ‘Tripp’. The data has a lot of duplicates and the average number of rows for most names is quite high (where an index is no longer userful). However, ‘Tripp’ is really not an average\u00a0data value here and so the plan might be good for most other values. But, in this case, it’s not good for the value Tripp.<\/p>\n

Execute the procedure again but supply a different value for @p1:<\/p>\n

EXEC [dbo].[ParamVarLit] @p1 = ‘Anderson’ — assignment by name
\nGO<\/p>\n

This is what you’ll see when you execute with ‘Anderson’ AFTER having created a plan for ‘Tripp’:<\/p>\n

\"ExecutionPlans\"<\/a><\/p>\n

Wait – there’s no difference? <\/strong><\/p>\n

Nope, absolutely none! Really,\u00a0review every aspect of your output \/plan [not the actual\u00a0values] and you’ll see it’s exactly the same! The plan that you see is always the estimated plan and the estimated plan is chosen when the stored procedure is optimized \/ compiled. Optimization \/ compilation occurs only when there isn’t already a plan in cache\u00a0for that procedure.<\/p>\n

Why are they the same?<\/strong><\/p>\n

The parameter – ‘Tripp’ was used on the first execution and this is what was “sniffed” and use for optimization. When I say “sniffed” all that means is that the value was KNOWN such that the optimizer could look at the statistics (and specifically the histogram) to\u00a0estimate how many rows had a last name of ‘Tripp.’ It turns out that the estimate was 1. You can see this by hovering over the Index Seek in the first statement:<\/p>\n

\"Diagrams\"<\/a><\/p>\n

 <\/p>\n

If you were to kick this plan out of cache and re-execute with ‘Anderson’ then something interesting would happen:<\/p>\n

EXEC [sp_recompile] ‘[dbo].[ParamVarLit]’;
\nGO<\/p>\n

EXEC [dbo].[ParamVarLit] @p1 = ‘Anderson’;
\nGO<\/p>\n

Results in this plan:<\/p>\n

\"Statement1Estimate_Anderson\"<\/a><\/p>\n

There’s really one\u00a0incredibly important\u00a0observation here: ONLY the first statement’s plan changed!<\/strong><\/p>\n

The first statement’s plan changed because on this execution SQL Server was able to “sniff” the parameter and optimize \/ compile a plan specific to it.\u00a0More specifically, when the statement KNOWS that the value is ‘Anderson’ (again, only statement 1 knows this)\u00a0then SQL Server can look to the statistics to determine how much data is going to be processed.\u00a0In this case,\u00a0SQL Server estimates that there are numerous\u00a0rows with a last name of ‘Anderson’ (from the statistics, estimates 385 rows). As a result, the data is not selective enough to warrant using an index so in this case, SQL Server uses a table scan. (shown as a clustered index scan solely because the table has a clustered index)<\/p>\n

Bringing It All Together<\/span><\/h2>\n

Parameters are evaluated and\u00a0sniffed ONLY when a plan is being created. This ONLY happens when a plan is NOT already in the cache. And, sniffing is fantastic for THAT specific execution because “sniffing” a parameter lets SQL Server use the histogram component of statistic to determine the estimate. While the histogram is not always perfect, it’s usually a\u00a0more\u00a0accurate way of estimating rows. But, this can also lead to parameter sniffing problems where subsequent executions don’t perform well because the plan in cache wasn’t optimized for their values. There are many solutions to this problem, I covered a few of the options here: Building High Performance Stored Procedures<\/a>.<\/p>\n

Variables are ALWAYS deemed “unknown” and they cannot be sniffed. In this case, SQL Server doesn’t have a value to lookup in a statistic’s histogram. Instead, SQL Server uses an average<\/em> to estimate the rows and come up with a plan. But. as I mentioned – this can be sometimes good and sometimes bad.<\/p>\n

The literal is the easiest of them all. SQL Server knows this value and there’s absolutely nothing that will ever change that value. This can be sniffed and it will use the histogram getting the best estimate to use for optimization.<\/p>\n

Play around with this sample procedure. Review the plans and the estimates v. actuals. Next week I’ll dive more into the statistics themselves and where the specific estimates are coming from!<\/p>\n

Have fun and thanks for reading!<\/strong>
\nk<\/p>\n","protected":false},"excerpt":{"rendered":"

In Nov 2014, SQLskills made an offer to user group leaders to deliver remote user group sessions in 2015\u00a0and we’ve been having great fun delivering them. So far the team has delivered 34 sessions and we have 72 signed up! At my last session (for the San Diego SQL Server User Group), I delivered a […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[50,72],"tags":[],"class_list":["post-2767","post","type-post","status-publish","format-standard","hentry","category-optimizing-procedural-code","category-statistics"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=2767"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2767\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=2767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=2767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=2767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}