Welcome to PC Teach Me...

Free Computer Training Videos.

Although you do not need to register to access this site. Registering has its benefits. Such as:

  • Access to training material associated with the video you are watching [if applicable]
  • Automatic email updates when new training videos are available.
  • Ask for a training video.
  • Exclusive access to pose questions to me [limited availability as I am doing this for free :-)].
  • Free contribution of your own training topics, this includes referencing your videos outside of PC Teach Me.
  • Its Free!

Member Login

Lost your password?

Not a member yet? Sign Up!

By registering with this blog you are also agreeing to receive email notifications for new posts but you can unsubscribe at anytime.

25425 Registered Users
71 Number Of Articles
Free Video Tutorials (Email) Free Video Tutorials (RSS) Free Video Tutorials (Twitter) Free Video Tutorials (Flickr)

SQL: Parameter Sniffing

SniffParam SQL: Parameter Sniffing

 

sql SQL: Parameter Sniffing SQL: Parameter Sniffing

Below is something of interest if your creating Stored Procedures for SQL Reporting Services.

I had an issue with some of the SQL reports yesterday for a report stakeholder. They were stating that a specific SQL report would time out even though the report works fine running different parameters.

I therefore, had a look at the SP to investigate. My Investigation was as follows:

1. Obtain SQL Stored Procedure from SourceSafe(our code repository).
2. Comment out the “Create Procedure” gubbins at the top of the script.
3. Declared the parameters and set the values to be the same as the report which was failing.
4. Ran the report.

What baffled me was that the SQL results took 17 seconds to return all the 11,000+ records which based on the criteria being set in the SQL query I thought that was okay.  So where do you go from there? Well, my thoughts then went towards the SQL SSRS report. I reviewed the ReportServer “execution log” table and referenced the report and sure enough the data section took around 17 seconds but the rendering was (well cannot remember for definite but lets say) 1800 seconds or half hour. I then started looking for performance issues with SSRS and rendering as the report was doing to grouping (which depending on the amount of records could slow things down).

What I discovered though was something very very interesting…

The SQL report was fine! It was the SQl Stored Procedure that was the culprit. I was talking this over with some people and showed them what I had done.
1. Ran the SP as a script (as the above points) which ran at 17 seconds.
2. I then did “Exec” Stored Procedure and saw that it DID NOT return any values for 50 mins!

So, what the heck is this happening? I knew at that point it was to do with the SQL execution plan (SQLs game plan of attack for each script or Stored Procedure). So I told the SP to recompile (which basically resets the execution plan). I thought that would have solved the issue, but it didn’t….why?

SQL Solution

Well, its down to something called “Parameter Sniffing” in SQL. What happens is when you create or alter an SQL Stored Procedure it will empty the execution plan and only store a execution plan once it has run for the first time. Now lets think about this for a second…. When you run for an SQL Stored Procedure with 10+ parameters what is stored in the Execution plan is specific criteria for that first run of the SQL Stored Procedure.

Example

I created the Stored Procedure, I run the Stored Procedure for company “1234”, the execution plan is stored with that criteria. If I then run for the Stored procedure for the company “ABCD”. Potentially, the internal logic may drive the report in a different way. I.e. the changing of the parameter could change the where clause, the joins, some altered calculations etc.

Which in my case was the problem. “Parameter Sniffing” causes the execution plan to store how the parameters are being used and optimally stores how to get to the tables/pages quickly for that run ONLY. So in the case of SP calling for a web/report development we cannot (unless it is a basic list off one table) rely on the execution plan in its current state.

How do we fix? It is a pain, but I can tell you for a fact it fixes the issue! What you need to do is create another set of parameters inside the SP and assign them to the parameters which you call from the Stored Procedure.

Example SQL

Exec SPU_test ‘Hello’,’Goodbye’

Anatomy of the SQL Stored Procedure

Create Procedure SPU_TEST
     @strGreeting  varchar(1000),
     @strFarewell varchar(1000)
As
Declare
     @strRevisedGreeting varchar(1000),
     @strRevisedFarewell varchar(1000)
Select
     @strRevisedGreeting = @strGreeting,
     @strRevisedFarewell = @strFarewell
Select
     Column1,
     Column2
From TableA
     Where
     Salutation = @strRevisedGreeting
     AND Departure = @strRevisedFarewell

The above code will fix the issue. Notice that I assign the parameters feeding the SQL Stored Procedure to locally defined parameters in the script and use them to complete the script.

How does it work? Simply when the execution plan is being written for the first time it tracks the parameters into the report but because they are assigned to local parameters the execution plan does not know what to do with them, therefore the plan focuses on the tables and joins apposed to the columns/indexes used in the where clause (which causes the problem in the first place).

What we now have is an execution plan that is generalised for ALL eventualities of parameter.

I STRONGLY recommend that this is made a matter of course for all new SQL Stored Procedures where multiple parameters are used as this is a BIG performance winner.

Let me know if you have any questions.

This video is one of many SQL tutorials on this site click here for more 

Related Posts

Leave a Comment

You must be logged in to post a comment.

Valid XHTML 1.0 Transitional website security