April 26, 2011 at 11:34 am
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
April 26, 2011 at 12:07 pm
could you post table schema's as well as estimated number of rows in each table... also indexes if there are any...
April 26, 2011 at 3:38 pm
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
April 26, 2011 at 3:45 pm
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.
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
April 26, 2011 at 3:46 pm
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.
April 26, 2011 at 3:59 pm
index definition is there in the create table script.... I am attaching the SQL query execution plans for both conditions..
April 26, 2011 at 4:11 pm
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)?
May 3, 2011 at 5:24 pm
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!!!
May 3, 2011 at 5:34 pm
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.
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
May 4, 2011 at 10:02 am
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