SQL server--Technical Details You may not know: Impact of stored procedure parameter passing

Preface many people think that the database is actually very simple, there is no big in-depth details to study, but the real details of the question determines whether you are experts. This article mainly describes the stored procedure parameter passing some small details, many people know that parameter sniffing, this example can also be understood as the power of parameter sniffing enhanced version + + Small Example

---Create a test table Select IDENTITY (INT,1,1) as RID,*Into tb1from Sys.all_columnsgo---simulating large amounts of data insert into Tb1select*From Sys.all_columnsgo ---Creating an index in the user_type_id column create nonclustered index [Nonclusteredindex-20160625-164531] on [dbo]. [TB1] ([user_type_id] ASC) With (Pad_index= off, Statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, ONLINE = off, Allow_row_locks = ON, Allow_ Page_locks =On ) On [Primary]go--turn on IO statisticsSetStatistics IO on--test Query Execution PlanSelect* fromTb1whereuser_type_id =Ten

Note: In this example, the execution of the statement should go through the index seek + key look up test one

-- Test 1: Pass the parameter value to the variable create PROCEDURE dbo using the defined variable. Usp_getdata (  @PID INT) asbegindeclare @ID intset @ID=*= @IDENDGOEXEC dbo. Usp_getdata @PID=Ten

Conclusion: If you define a variable in a stored procedure and assign a value to a variable set, the value of the variable cannot provide a reference to the execution plan (that is, the execution plan does not consider the variable), and the estimated number of rows and the actual number of rows is too large to cause the execution plan to be suboptimal.

Test Two

--- Test 2: Operations on Parameters create PROCEDURE dbo. Usp_getdata2 (  @PID INT) asbeginset @PID[email protected]1SELECT*= @ PIDENDGOEXEC dbo. Usp_getdata2 @PID= One

Conclusion: If you use set to reassign stored procedure parameters in a stored procedure, the execution plan still takes the values passed in at execution time to generate the execution plan.

Test Three

-- Test 3: Stitching on the parameters create PROCEDURE dbo. USP_GETDATA3 (@PID int) asbegindeclare @ID intset2= @ID +*= @ PIDENDGOEXEC dbo. Usp_getdata3 @PID8

Conclusion: If the newly defined variable is re-assigned with the incoming parameter stitching in the stored procedure, the execution plan still takes the value passed in at execution time to generate the execution plan.

Test Four

-- Test 4: Operations on variables create PROCEDURE dbo. USP_GETDATA4 (  *2endgoexec dbo. USP_GETDATA4 @PID=8

Conclusion: Although incoming parameters are modified after they are passed in, the values passed in when the execution plan is generated are still used

Test Five

--* = @PID + CAST (RAND () * as INT) endgoexec dbo. Usp_getdata5 @PID=8GO

Conclusion: It is not possible to accurately estimate the number of rows and not to generate a reasonable execution plan because of the complicated operation of the parameters.

Test Six

-- Test 6: Complex operation using variable stitching create PROCEDURE dbo. USP_GETDATA6 (@PID INT) asbegindeclare @ID intset @ID = CAST (RAND () *= @ID +*  = @PIDENDGOEXEC dbo. USP_GETDATA6 @PID=8GO

Conclusion: Parameter stitching can be used for test five to accurately estimate the number of rows and use the correct execution plan

Summarize

Technical support to do a relatively long time, met a lot of pits, in these pits constantly reflect, slowly grow! Do not say what the database is better, do not say that our massive database needs what high-end technology, in fact, the key to solve the problem is only a little bit of basic knowledge.

Note: In this case there is another situation is that the query data volume is very large, then the full table scan itself is the optimal plan, and because of the problem of parameter passing error, the index seek + key look up is the same.

　　

--------------Blog Address-----------------------------------------------------------------------------

Original address: http://www.cnblogs.com/double-K/

If there is reprint please keep the original address!

----------------------------------------------------------------------------------------------------

Note: This article is original, welcome reprint, please in the article page obvious location give this article link!

SQL server--Technical Details You may not know: Impact of stored procedure parameter passing