Performance issue with Lengthy stored procedure part 2

Hi Mates,

Yesterday I have written post about performance issue by intentionally not naming the title as 
"Parameter sniffing".

Well, if I would have said this as parameter sniffing I thought you will not go through the details hence I skipped this.

If we google there are so many examples being written for this topic but when you are dealing on production systems and to prove that issue is being caused by parameter sniffing it is little difficult.

It is very difficult to gather the information for these kind of scenarios hence it will be challenging for DBA's to prove this.

In my case I came to the conclusion that this issue is being caused by parameter sniffing only after realizing the fact as explained in my previous post "when I created the same SP with different name the execution has become faster".

Well now I know the issue hence I recommended the solutions as sp_recompile or incorporating option recompile at the problematic statement.

But how will you prove to the customers we can do this by  interpreting the execution plan as XML and then looking for parameter Compiled Value and Parameter run time Value.

As you can see below the compile time value is 724 and run time value is 901.


In my case the country ID is the culprit when we run the data for country France it has less no of rows to fetch for and for the problematic country portugal it has to fetch more rows.

For your information if you want to execute your stored procedure with out marking it for recompile you can wipe out the plan for that Stored procedure and see how the execution plans will look like when executing this with different parameters.

DECLARE @PlanHandle VARBINARY(64);

SELECT  @PlanHandle = deps.plan_handle
FROM    sys.dm_exec_procedure_stats AS deps
WHERE   deps.object_id = OBJECT_ID('SPNAME') AND deps.database_id = DB_ID();
print @PlanHandle
IF @PlanHandle IS NOT NULL
    BEGIN
        DBCC FREEPROCCACHE(@PlanHandle);
    END
GO

 In my case the execution plan was differing from each other for each countries as the data distribution is not one at the same. If you see the picture below there is non clustered index scan above table scan in the first diagram with some what Fat line where as in the below picture it is clustered index scan(above table scan) with thin line





This is how I can now say that the issue has come in to picture because of parameter sniffing. 
Hope you learned something new and please pass on your comments.

Comments