Stored proc eating bandwidth on server!

  • ok.. this is pretty simple and i can't understand... we have a stored proc which is called from an application server. the stored proc has started eating bandwidth which intends to bring down the sql server.... COULD some one help me with where could be the possible points of pain?

    USE [ProductCatalog]

    GO

    /****** Object: StoredProcedure [dbo].[ProductCatalog_GetColors] Script Date: 04/26/2011 10:09:09 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER PROCEDURE [dbo].[ProductCatalog_GetColors]

    @ColorId INT = NULL

    AS

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- Retrive the Color information for given Color Id

    IF @ColorId is NULL OR @ColorId <= -1

    BEGIN

    SELECT ColorID,

    ColorCode,

    [Name],

    Color.ColorFamilyID

    FROMColor

    JOIN ColorFamily on Color.ColorFamilyID = ColorFamily.Id

    END

    ELSE

    BEGIN

    SELECT ColorID,

    ColorCode,

    [Name],

    Color.ColorFamilyID

    FROMColor

    JOIN ColorFamily on Color.ColorFamilyID = ColorFamily.Id

    WHEREColorId= @ColorId

    END

  • could you post table schema's as well as estimated number of rows in each table... also indexes if there are any...

  • color has 175000 rows where as colorfamily has 25 rows

    USE [ProductCatalog]

    GO

    /****** Object: Table [dbo].[Color] Script Date: 04/26/2011 14:35:53 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Color](

    [ColorID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [ColorCode] [char](3) NOT NULL,

    [Name] [varchar](255) NULL,

    [CascadeChangeDateModified] [datetime] NULL,

    [ColorFamilyID] [int] NULL,

    CONSTRAINT [Color_PK] PRIMARY KEY CLUSTERED

    (

    [ColorID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[Color] WITH CHECK ADD CONSTRAINT [FK_Color_ColorFamilyID__ColorFamily_ID] FOREIGN KEY([ColorFamilyID])

    REFERENCES [dbo].[ColorFamily] ([ID])

    GO

    ALTER TABLE [dbo].[Color] CHECK CONSTRAINT [FK_Color_ColorFamilyID__ColorFamily_ID]

    GO

    ALTER TABLE [dbo].[Color] WITH CHECK ADD CONSTRAINT [UQ_Color_ColorCode] CHECK (([dbo].[ProductCatalog_fn_Color_ColorCode_CheckConstraint]([ColorCode]) = 1))

    GO

    ALTER TABLE [dbo].[Color] CHECK CONSTRAINT [UQ_Color_ColorCode]

    ======================================================

    USE [ProductCatalog]

    GO

    /****** Object: Table [dbo].[ColorFamily] Script Date: 04/26/2011 14:37:03 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[ColorFamily](

    [ColorFamilyCode] [char](3) NOT NULL,

    [Description] [varchar](255) NOT NULL,

    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    CONSTRAINT [PK_ColorFamily_ID] PRIMARY KEY NONCLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],

    CONSTRAINT [UQ_ColorFamily_ColorFamilyCode] UNIQUE NONCLUSTERED

    (

    [ColorFamilyCode] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

  • When you say bandwidth, I think network.

    The only way this could start eating tons of network is if you're constantly getting polled by the app server on the same proc repeatedly. Each pull of this proc will get 175,000 rows or less depending on the parameter provided. Assuming it's usually pulling with a -1 on the parameter, and if it's constantly polling it, I *might* see some real bandwidth issues... maybe. On old hubs and bad routers. There's just not enough data there to really clog up a 10/100 pipe, nevermind a gigabit/fiber.

    If you having actual network issues, I'd look to hardware failures or bad software code doing constant repetitive calling (in the order of 5-10 times/second). If not, please define exactly what you're seeing as far as a problem.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Please add the index definition for each table as well as the actual execution plan (as sqlplan file) of the sproc for each of the two conditions.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • index definition is there in the create table script.... I am attaching the SQL query execution plans for both conditions..

  • Query plan and index stats don't seem to indicate an issue with the sproc itself.

    Did you try a profiler trace on that specific sproc to verify if the pattern Craig described applies here? Are there any recent changes in the app or at the db side (including increased number of rows)?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks LutzM.. I found that we are using stored proc too often with various frequent calls. we have decided to use only default pass parameter for building the color cache instead of frequently querying the DB for each colorid. This will resolve the issue..

    Cheers to Life!!!

  • nikhil8960 (5/3/2011)


    Thanks LutzM.. I found that we are using stored proc too often with various frequent calls. we have decided to use only default pass parameter for building the color cache instead of frequently querying the DB for each colorid. This will resolve the issue..

    Cheers to Life!!!

    So, yep, you did mean network. Ugh, I hate those. Thanks for posting the resolution you found.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Commong problem: the query plans for BOTH conditionals are compile the FIRST time the sproc is executed -> thereby GUARANTEEING one of them will be BAD. Solution is to put each part of the IF statement into it's OWN sproc and call THAT from inside each IF section. Then each separate sproc gets the OPTIMAL query plan.

    Another solution is to use dynamic SQL. Be sure to guard against SQL Injection if you go that route.

    Gail has some good stuff on this on her blog: http://sqlinthewild.co.za

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 10 posts - 1 through 9 (of 9 total)

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