T / F: using IF statements in a procedure gives several plans

Question

In response to this question KM said

if you are on SQL Server 2005 or higher, you can use IF to have multiple queries in the same procedure, and each of them will have a query plan stored for it (equivalent to the procedure for each of the older versions), see article in my answer or this link in the appropriate section: sommarskog.se/dyn-search-2005.html#IF

Added by HLGEM

You can do this in earlier versions of SQL Server.

I read this section of the Sommarskog, but did not see anything about several plans.

In later studies, I read a quote here from Gert Drapers:

Because SQL Server allows only one execution plan for each stored procedure ...

I do not know the date of this original article or the version of SQL Server to which it refers.

Does anyone have a reliable link that discusses this, or better yet, a test that proves that this is true?

Answer 1

We are all right :-)

• "Request Plan" has no more than 2 entries in the cache: one sequential and one parallel

• Each user has his own "execution context" that implements the plan

• Plans differ if facilities are not qualified.

So, you might think that this is not a plan because the tables do not match the schema (which is the same in SQL Server 2000, 2005, and 2008)

From MSDN / BOL " Caching and Reusing Execution Plan "

Edit:

"Getting Query Level Queries" from MS Blog

Answer 2

Update # 2:

I have one more step to add, which should make everything clearer. After generating the plan information, run the following statement (with the correct plan descriptor) to view the ShowPlan XML.

DECLARE @val as VARBINARY(64) -- NOTE: Replace the Hex string with the current plan_handle ! SET @val = CONVERT(VARBINARY(64), 0x05001300045A3D02B801BE11000000000000000000000000) SELECT * FROM sys.dm_exec_query_plan(@val) 

Looking at the generated XML, you will find that there are two QueryPlan elements, 2 or more StmtSimple / StmtCond elements, and only 1 package in total. As stated in gbn , there is a difference between “Execution Plans” and “Request Plans”. This seems to clearly show which parts we are actually looking at in all sys.dm _ requests.

sys.dm_exec_query_stats in MSDN, SQL 2008

sys.dm_exec_query_plan in MSDN, SQL 2008

Thus, with all this information, the returned plan_handle is the Execution Plan; and parts are elements of a query plan.

-

Update:

After Andrew commented, I re-tested, and indeed, the query plan handlers match. And to be sure, I duplicated the stored procedure, changing only the name and repeated my test against this stored procedure. It also caused the creation of a new set of query parts that shared the same plan descriptor.

So gbn's answer seems to be correct, at least in what I tested here. Interesting stuff.

-

The last quote from Gert Drapers seems false - here is my test. I am using SQL 2005 here. In my test, I see 2 query plans generated for different parts of the same stored procedure.

First I created two tables: tblTag1 and tblTagWithGUID . I made them somewhat similar so that my stored procedure could alternate between tables and return results with the same layout of the result table.

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO -- Table #1, tblTag1 CREATE TABLE [dbo].[tblTag1]( [id] [int] IDENTITY(1,1) NOT NULL, [createDate] [datetime] NOT NULL CONSTRAINT [DF_tblTag1_createDate] DEFAULT (getdate()), [someTag] [varchar](100) NOT NULL, CONSTRAINT [PK_tblTag1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO -- Table #2, tblTagWithGUID CREATE TABLE [dbo].[tblTagWithGUID]( [id] [int] IDENTITY(1,1) NOT NULL, [createDate] [datetime] NOT NULL CONSTRAINT [DF_tblTagWithGUID_createDate] DEFAULT (getdate()), [someTag] [varchar](100) NOT NULL, [someGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblTagWithGUID_someGUID] DEFAULT (newid()), CONSTRAINT [PK_tblTagWithGUID] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO 

Secondly, the stored procedure. In a stored procedure, I select one or the other table depending on the argument.

 CREATE PROCEDURE spLoadTags @Pick AS BIT = NULL AS BEGIN IF @Pick = 0 SELECT id, createDate, someTag FROM tblTag1 IF @Pick = 1 SELECT id, createDate, someTag FROM tblTagWithGUID END 

I added some data to each table, and then ran the stored procedure several dozen times with 0 or 1 as an argument.

Then I ran this query to check the generated query plans. Sorry, if someone is offended by my sloppiness here, of course, this is not a production code.

 WITH PlanData AS ( SELECT (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle) WHERE [text] like '%SELECT id, createDate, someTag FROM tblTag%') AS query_text, plan_handle FROM sys.dm_exec_query_stats ) SELECT DISTINCT execution_count, PlanData.query_text, sys.dm_exec_query_stats.plan_handle FROM sys.dm_exec_query_stats, PlanData WHERE sys.dm_exec_query_stats.plan_handle = PlanData.plan_handle and PlanData.query_text IS NOT NULL ORDER BY execution_count DESC 

When I run this query, I see a bunch of plans, but since I started the stored procedure several dozen times, the individual parts end at the top.

 execution_count query_text plan_handle 96 SELECT id, createDate, someTag FROM tblTag1 0x05001200045A3D02B8613E13000000000000000000000000 96 SELECT id, createDate, someTag FROM tblTagWithGUID 0x05001200045A3D02B8613E13000000000000000000000000 

I have included only these two lines, but hopefully it is simple enough when someone else can check my results. You can see other lines if you use an SQL management tool like me; allegedly caused by viewing tables or other activities.

Answer 3

I believe that what is mentioned is that the stored procedure has a common query plan, which is composed of query plans for individual operators. In other words, there is a plan associated with each statement. (And for AFAIK you do not need to use EXEC (..) or sp_ExecuteSQL () to get this).

So, if you use IF to branch into different query requests, then yes, you can use different plans. However, if you simply use your IF to set different values ​​for the variables, which are then all executed by the same SQL statement using these variables, then you do not have only one query plan.

Answer 4

From my knowledge, I would say that each stored procedure or general query generates one plan cached based on the SQL hash and retrieved from the cache on this basis.

What it seems to me to be mentioned is when you write a proc that contains calls using sp_executesql or exec, since then it will be an additional request that generated its own plan.

On this basis, you get several plans. The text in the article was as follows:

 Each subprocedure has its own plan in the cache, and for search_orders_4a_sub1 and sub2 that is a plan that is based on good input values from the first call. The catch-all search_orders_4a_sub3, still has a WITH RECOMPILE at it serves a mix of conditions.