Query tuning issue..

  • Can somebody help me tune this stored proc? It is erratic and average time is between 500 and 1000 milliseconds. Sometimes it goes up to 13 seconds... we get about 10/15 hits per second, and it is creating a bottleneck...

    I've attached txt files for

    1. The stored proc

    2. The table and their SP_HELP

    3. Screen shot of the query plan

    4. Sample of the query call.

    The rowcounts are as follows:

    CompanysPrograms - 2000

    NodesActivationCodes - 1.5 million

    ActivationCodes - 1 million

    ActivationCodesProducts - 1 million

    Products - 60

    BundlesProducts - 300

    NodesActivationCodes_ISP - 800K

    ActivationCodes_ISP - 500K

    ActivationCodesProducts_ISP - 500K

    I'm not sure how to export the execution plan out in text so you can look at the details...

  • parameter sniffing: search here on SSC for some examples and solutions;

    parameter sniffing happens when an execution plan is built that does not reflect the real way to get the data; it is often due to having optional parameters... for example, your proc has a parameter like this:

    @componentType varchar(10) = null,

    so the engine decides that since you provided the value, NULL, it must be the default / most common value to use, and creates an execution plan based on that value being null; if you look at the code, the first thing you do is test for a value other than that.

    i reckon if you change it around so that local variables are used inside the proc, the perforance will get better due to a better execution plan.

    change the very top of your proc to this:

    CREATE PROCEDURE [dbo].[GetEntitlementResponse](

    @locbios nvarchar(300),

    @loccomputerName nvarchar(300),

    @locdiskVolume nvarchar(300),

    @locguid uniqueidentifier,

    @locmacAddress nvarchar(300),

    @locmotherboard nvarchar(300),

    @locnodePKID uniqueIdentifier,

    @loccomponentType varchar(10) = null,

    @loclang varchar(10) = null,

    @locissVersion varchar(10) = null,

    @locentitlementSuite varchar(10) = null,

    @locplatform varchar(10) = null,

    @locmetaUpdate int = 0)

    AS

    DECLARE

    @bios nvarchar(300),

    @computerName nvarchar(300),

    @diskVolume nvarchar(300),

    @GUID uniqueidentifier,

    @macAddress nvarchar(300),

    @motherboard nvarchar(300),

    @nodePKID uniqueIdentifier,

    @componentType varchar(10) ,

    @lang varchar(10) ,

    @issVersion varchar(10) ,

    @entitlementSuite varchar(10) ,

    @platform varchar(10) ,

    @metaUpdate int

    SET @bios = @locbios

    SET @computerName = @loccomputerName

    SET @diskVolume = @locdiskVolume

    SET @GUID = @locguid

    SET @macAddress = @locmacAddress

    SET @motherboard = @locmotherboard

    SET @nodePKID = @locnodePKID

    SET @componentType = @loccomponentType

    SET @lang = @loclang

    SET @issVersion = @locissVersion

    SET @entitlementSuite = @locentitlementSuite

    SET @platform = @locplatform

    SET @metaUpdate = @locmetaUpdate

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • See this article for posting the exec plan for SQL 2000. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • @Gail - I tried the instructions from the post, but no additional grid was returned..

    SET STATISTICS PROFILE ON

    GO

    exec GetEntitlementResponse @bios = N'None', @computerName = N'OWNER-B23156214', @diskVolume = N'4472755e', @GUID = '96FB784E-BE48-4378-BFAF-D568F6AC7E0A', @macAddress = N'00-24-1d-2c-a2-f0', @motherboard = N'None', @nodePKID = '150FB556-707C-4194-B605-ED07FC747242', @lang = 'en-US', @issVersion = '5.0.0.581', @entitlementSuite = 'ISS', @platform = 'x86', @metaUpdate = 1

    @lowell - I had tried this previously, and I tried it again now - it doesn't seem to help, because most of the parameters are not being used. If you see the query, there is really 1 parameter being used really - @nodePKID - this joins amongst the other tables, and the rest are all just based on indexes and such.

  • yeah i was looking at that;

    really what you want to do is have your original be a "master" procedure, and depending on parameters, call other, more refined procedures for specific data instead.

    It might be a pretty big rewrite of the procedure, so you avoid the code like below, if you knew

    @componentType IS NULL, you could call subproc1, and if it is not null, call subproc2;

    that would fix that WHERE statemtn that has the OR in it:

    and ((vp.IsBundle = 1 and vp.ComponentType_BP = @componentType) OR (vp.ComponentType = @componentType))

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I can break it up if need be, but majority of my calls are in the 'else' part - basically, the @componentType is null in most of the cases...

  • wouldn't changing the defaults on these to empty strings improve your proc a bit?

    @componentType varchar(10) = null,
    @lang varchar(10) = null,
    @issVersion varchar(10) = null,
    @entitlementSuite varchar(10) = null,
    @platform varchar(10) = null,
    

    comparing SomeColumn = NULL makes that portion of your WHERE statements incorrect, i think? does that sound right?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I can try it, but I've tried it previously. It doesn't help because they aren't actually used in the where statements....

  • Tried - no dice 🙁

  • MR (8/7/2009)


    I can break it up if need be, but majority of my calls are in the 'else' part - basically, the @componentType is null in most of the cases...

    It's still worth breaking it up, at the very least because it will mean that it can be optimised in pieces.

    Try breaking the proc up so that the if and else blocks just call other procs, then test and see where the performance problem is and then we can work on the smaller pieces.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I also see that perhaps we could build you some better indexes. As there are 10 bookmark lookups in the first query and bookmark lookups are normally very expensive.

    I also noticed that the code is written using the old style(non ansi standard), where you put you join criteria in the where clause, although this should not have an impact on the speed of there query, you need to make sure that perhaps you haven't created a cross join...

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply