Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question Unanswered: Inconsistent SP performance on different Servers

    One of my developers recently installed a backup of the production database onto his test site. His test server has the same configuration as the production server.

    One of the Stored Procedures that is called takes 1:45 to run on his machine, but only 2 seconds on the production server. This same SP takes only 2 seconds on my development database.

    The SP is called iteratively, up to 10 times... to run against 10 separate fields. Depending on a value for a parameter called @CriteriaClassID, depends on which portion of the SP runs.

    The significant difference in processing time in itself is baffling (since the servers are same specs / configuration, as far as I can tell, and the data is identical, since he has a backup of the most recent production data).

    But more baffling: if, in his data, I switch the values from field 1 to field 2, and vice versa, his results take 2 seconds (switching the values in field 1 to field 2 switches the value in @CriteriaClassID which is passed through to this SP).

    It's exactly the same SP; the only difference is that field 1 is processed first, field 2 second, field 3 third etc. On the production site and my development site, it doesn't make a difference in the order they are processed. On his machine it does.

    Any ideas? I though perhaps his Indexes were corrupted in the rebuild, but we ran a SQL Server maintenance schedule to clean it up, and no improvement.

    This is the SP, if it is of any help:

    CREATE procedure [dbo].[st_pull_model_data] @ModelID as integer, @CriteriaID as integer
    as

    declare @ClientID as integer, @CriteriaClassId as char(1)

    /*Procedure to pull data from org_model_data and postalcode_model_data for modeling and media analysis */
    /*Need to have table #temp_data created outside of SP with fields org_id and zip_code */
    /*This procedure is used by SP st_model_data */

    If @CriteriaID is not null
    begin

    set @CriteriaClassId = (Select model_criteria_type from model_criteria where model_criteria_id = @CriteriaID)
    if @CriteriaClassID = 'G' -- changes client_id from specific to general, if General is required.
    begin
    set @ClientID = 0
    end
    else
    begin
    set @ClientID = (Select client_id from model where model_id = @ModelID)
    end

    If @CriteriaClassId in ('G','P')
    Begin
    update #temp_data
    set data1 = postal_criteria_value
    from #temp_data t
    left outer join
    (select postalcode, postal_criteria_value
    from postalcode_model_data pmd
    join model_org_trade_area mota on mota.zip_code = pmd.postalcode
    join model_org mo on mo.model_org_id = mota.model_org_id
    where model_criteria_id = @CriteriaID
    and client_id = @ClientID
    and mo.model_id = @ModelID) as PMD
    on PMD.postalcode = t.zip_code
    end
    else
    Begin
    update #temp_data
    set data1 = org_criteria_value
    from #temp_data t
    left outer join
    (select distinct postalcode, org_criteria_value, omd.org_id
    from org_model_data omd
    join org o on o.org_id = omd.org_id
    join model_org_trade_area mota on mota.zip_code = omd.postalcode
    join model_org mo on mo.model_org_id = mota.model_org_id and mo.org_id = o.org_id
    where model_criteria_id = @CriteriaID and o.client_id = @ClientID and mo.model_id = @ModelID) as OMD
    on OMD.postalcode = t.zip_code and omd.org_id = t.org_id
    end
    end

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    Just a thought on something to try...

    Declare two local variables that are similar to the two passed in parameters, copy the parameters to these local variables at the top of your sporc and then use the local variables only within the code.

    If this has the effect of solving your issue, you have a "parameter sniffing" issue.
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One other thought, is his tempdb on the same physical disk as the database data files? It won't explain the whole performance problem, but it will explain a part.

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Thumbs up Parameter Sniffing was cause

    Chopin,

    Thanks a ton. The inclusion of the two local variables (copying over the SP variables) solved the problem for performance.

    Another lesson learned.

  5. #5
    Join Date
    Dec 2004
    Posts
    46
    In that case does that mean backup restore do not restore query plans for the stored proc in the database??? Any idea?

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    NO ... backup does not save the procedure cache.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •