MS SQL Server - Re: Sporadic Slow Response on Updates and Inserts

Author

Re: Sporadic Slow Response on Updates and Inserts

David Snyder

2005-03-30, 9:40 am

Here's the ScanDensity, LogicalFragmentation
and ExtentFragmentation for all
the tables in that DB. The ones that I added to the DB are EmployeeProcesses,
EmployeeShortcuts, and LIveProduction. They all have the Primary Key from
EmployeeExtended all part or all of their primary key.

ObjectName ScanDensi
ty LogicalFrag Exten
tFrag
sysobjects 25 0 75
sysobjects 50 50 50
sysobjects 100 0 0
sysindexes 16.66666667 33.33333206 83.33333588
sysindexes 42.85714286 97.67441559 92.85713959
syscolumns 16.66666667 46.15384674 85.7142868
syscolumns 25 16.66666603 75
systypes 100 100 0
systypes 100 0 0
syscomments 15.55555556 37.5 54.54545593
sysfiles1 100 0 0
syspermissions 100 1
00 0
sysusers 100 100 0
sysusers 100 0 0
sysusers 100 0 0
sysdepends 25 25 75
sysdepends 33.33333333 33.33333206 66.66666412
sysreferences 100 10
0 0
sysreferences 100 0
0
sysreferences 100 0
0
sysfilegroups 100 10
0 0
sysfilegroups 100 0
0
ProductivityLkp 33.33333333 90 83.33333588
Forecast_ProductionD
ates 100 0 0
EmployeeFilter 50 0
50
Forecast_GoalAndFTE_
Reference 61.53846154 98.2456131 61.53845978
ErrorLog 100 2.380952358 16.66666603
Forecast_Estimated 2
5 25 50
ErrorLogRptg 100 0 0

EmployeeExtended 50
0 50
AccessLevel 100 100
0
EmployeeProcesses 50
50 0
EmployeeShortcuts 10
0 0 0
LiveProduction 100 3
3.33333206 0
LiveProduction 100 0
0
LiveProduction 100 0
0
AllocationAdjustment
100 0 0
dtproperties 100 100
0
dtproperties 50 80 5
0
Production 99.4047619 39.13151932 6.349206448
Production 99.20634921 99.89959717 1.587301612
Production 98.27586207 99.88962555 2.586206913
Production 97.65625 99.79919434 8.59375
LoginTrkg 70 98.03921509 60
ProdDeleted 100 0 0

Also, I scripted out the table with there indexes, etc. , so you can see how
they're all set-up.

/****** Object: Table [dbo].[AccessLevel] Script Date: 3/29/2005 3:20:58
PM ******/
CREATE TABLE [dbo].[AccessLevel] (
[AccLvlID] [int] NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo]. [AllocationAdjustmen
t] Script Date:
3/29/2005 3:21:00 PM ******/
CREATE TABLE [dbo]. [AllocationAdjustmen
t] (
[CalMonth] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL ,
[AdjFactor] [decimal](6, 2) NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ErrorLog] Script Date: 3/29/2005 3:21:00 PM
******/
CREATE TABLE [dbo].[ErrorLog] (
[ErrorID] [int] IDENTITY (1, 1) NOT NULL ,
[ErrMessage] [varchar] (1000) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[ErrSite] [varchar] (500) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[ErrSource] [varchar] (500) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[ErrLink] [varchar] (500) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[SysDate] [datetime] NULL ,
[UserID] [varchar] (10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[Comments] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[Reviewed] [binary] (1) NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ErrorLogRptg] Script Date: 3/29/2005
3:21:02 PM ******/
CREATE TABLE [dbo].[ErrorLogRptg] (
[ErrorID] [int] NULL ,
[ErrorNo] [int] NULL ,
[ErrorDesc] [varchar] (1000) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[SysDate] [datetime] NULL ,
[ErrorLoc] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[UserID] [varchar] (10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[Comments] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[Reviewed] [binary] (1) NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo]. [Forecast_Estimated]
Script Date: 3/29/2005
3:21:02 PM ******/
CREATE TABLE [dbo]. [Forecast_Estimated]
(
[ProcID] [int] NOT NULL ,
[MonthDate] [smalldatetime] NOT NULL ,
[Estimated_Items] [decimal](18, 2) NOT NULL ,
[Required_FTE] [decimal](18, 2) NOT NULL ,
[OverShort_FTE] [decimal](18, 2) NOT NULL ,
[OverShort_Item] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo]. [Forecast_GoalAndFTE
_Reference] Script Date:
3/29/2005 3:21:02 PM ******/
CREATE TABLE [dbo]. [Forecast_GoalAndFTE
_Reference] (
[ProcID] [int] NOT NULL ,
[MonthDate] [smalldatetime] NOT NULL ,
[BusinessDays] [int] NOT NULL ,
[TargetGoal] [decimal](18, 2) NOT NULL ,
[FTE] [decimal](18, 2) NOT NULL ,
[TotalITems] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo]. [Forecast_Production
Dates] Script Date:
3/29/2005 3:21:03 PM ******/
CREATE TABLE [dbo]. [Forecast_Production
Dates] (
[ProductionDate] [smalldatetime] NOT NULL ,
[BusinessDays] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[LoginTrkg] Script Date: 3/29/2005 3:21:03
PM ******/
CREATE TABLE [dbo].[LoginTrkg] (
[PreferredID] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[LoginDate] [datetime] NULL ,
[Valid] [bit] NULL ,
[PrjID] [int] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ProdDeleted] Script Date: 3/29/2005 3:21:04
PM ******/
CREATE TABLE [dbo].[ProdDeleted] (
[ProdID] [int] NULL ,
[EmpID] [int] NULL ,
[ProcID] [int] NULL ,
[ProdDate] [datetime] NULL ,
[Hours] [decimal](12, 2) NULL ,
[Items] [int] NULL ,
[Comment] [varchar] (500) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[ProductivityLkp] Script Date: 3/29/2005
3:21:04 PM ******/
CREATE TABLE [dbo].[ProductivityLkp] (
[OpGrpID] [int] NULL ,
[OpSubGrpID] [int] NULL ,
[DeptID] [int] NULL ,
[CstCntrID] [int] NULL ,
[Period] [datetime] NULL ,
[TotalHours] [decimal](12, 2) NULL ,
[TotalFTE] [decimal](12, 2) NULL ,
[TotalReqFTE] [decimal](12, 2) NULL ,
[ProdRatio] [decimal](12, 2) NULL ,
[FTESavings] [decimal](12, 2) NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[EmployeeExtended] Script Date: 3/29/2005
3:21:04 PM ******/
CREATE TABLE [dbo].[EmployeeExtended] (
[EmpID] [int] NOT NULL ,
[AccLvlID] [int] NULL ,
[StdDay] [decimal](6, 2) NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[EmployeeFilter] Script Date: 3/29/2005
3:21:05 PM ******/
CREATE TABLE [dbo].[EmployeeFilter] (
[EmpID] [int] NOT NULL ,
[CstCntrID] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[EmployeeProcesses] Script Date: 3/29/2005
3:21:05 PM ******/
CREATE TABLE [dbo].[EmployeeProcesses] (
[EmpID] [int] NOT NULL ,
[ProcID] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[EmployeeShortcuts] Script Date: 3/29/2005
3:21:06 PM ******/
CREATE TABLE [dbo].[EmployeeShortcuts] (
[EmpID] [int] NOT NULL ,
[ToggleKey] [int] NULL ,
[ToggleAlt] [bit] NULL ,
[ToggleCtrl] [bit] NULL ,
[ToggleShift] [bit] NULL ,
[TickKey] [int] NULL ,
[TickAlt] [bit] NULL ,
[TickCtrl] [bit] NULL ,
[TickShift] [bit] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[LiveProduction] Script Date: 3/29/2005
3:21:07 PM ******/
CREATE TABLE [dbo].[LiveProduction] (
[LiveProdID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpID] [int] NOT NULL ,
[ProcID] [int] NOT NULL ,
[Hours] [decimal](12, 2) NULL ,
[Items] [int] NULL ,
[Comment] [varchar] (500) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[CreationDate] [datetime] NOT NULL ,
[ModificationDate] [datetime] NOT NULL ,
[FinalizationDate] [datetime] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Production] Script Date: 3/29/2005 3:21:07
PM ******/
CREATE TABLE [dbo].[Production] (
[ProdID] [int] IDENTITY (193006, 1) NOT NULL ,
[EmpID] [int] NOT NULL ,
[ProcID] [int] NOT NULL ,
[ProdDate] [datetime] NOT NULL ,
[Hours] [decimal](12, 2) NULL ,
[Items] [int] NULL ,
[Comment] [varchar] (500) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AccessLevel] WITH NOCHECK ADD
CONSTRAINT [PK_AccessLevel] PRIMARY KEY CLUSTERED
(
[AccLvlID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[ErrorLog] WITH NOCHECK ADD
CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
[ErrorID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo]. [Forecast_Estimated]
WITH NOCHECK ADD
CONSTRAINT [PK_Forecast_Estimat
edItems] PRIMARY KEY CLUSTERED
(
[ProcID],
[MonthDate]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeExtended] WITH NOCHECK ADD
CONSTRAINT [PK_EmployeeExtended
] PRIMARY KEY CLUSTERED
(
[EmpID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeFilter] WITH NOCHECK ADD
CONSTRAINT [PK_EmployeeFilter] PRIMARY KEY CLUSTERED
(
[EmpID],
[CstCntrID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeProcesses] WITH NOCHECK ADD
CONSTRAINT [PK_EmployeeProcesse
s] PRIMARY KEY CLUSTERED
(
[EmpID],
[ProcID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeShortcuts] WITH NOCHECK ADD
CONSTRAINT [PK_EmployeeShortcut
s] PRIMARY KEY CLUSTERED
(
[EmpID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LiveProduction] WITH NOCHECK ADD
CONSTRAINT [PK_LiveProduction] PRIMARY KEY CLUSTERED
(
[LiveProdID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Production] WITH NOCHECK ADD
CONSTRAINT [PK_Production] PRIMARY KEY CLUSTERED
(
[ProdID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo]. [Forecast_Estimated]
ADD
CONSTRAINT [DF_Forecast_Estimat
ed_Required_FTE] DEFAULT (0) FOR
[Required_FTE],
CONSTRAINT [DF_Forecast_Estimat
ed_OverShort_FTE] DEFAULT (0) FOR
[OverShort_FTE],
CONSTRAINT [DF_Forecast_Estimat
ed_OverShort_Item] DEFAULT (0) FOR
[OverShort_Item]
GO

ALTER TABLE [dbo]. [Forecast_GoalAndFTE
_Reference] ADD
CONSTRAINT [DF_Forecast_GoalAnd
FTE_Reference_Busine
ssDays] DEFAULT (0) FOR
[BusinessDays],
CONSTRAINT [DF_Forecast_GoalAnd
FTE_Reference_FTE] DEFAULT (0) FOR [FTE]
GO

ALTER TABLE [dbo]. [Forecast_Production
Dates] ADD
CONSTRAINT [DF_Forecast_Product
ionDates_BusinessDay
s] DEFAULT (0) FOR
[BusinessDays]
GO

ALTER TABLE [dbo].[LiveProduction] ADD
CONSTRAINT [UC_LiveProduction] UNIQUE NONCLUSTERED
(
[EmpID],
[ProcID],
[CreationDate]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Employee] ON [dbo]. [LiveProduction]([Em
pID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Production] ADD
CONSTRAINT [cnProductionEmpID] CHECK ([EmpID] > 0)
GO

CREATE INDEX [IX_Production_ProcI
D] ON [dbo]. [Production]([ProcID
]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Production_ProdD
ate] ON [dbo]. [Production]([ProdID
]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Production_EmpID
] ON [dbo]. [Production]([EmpID]
) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeExtended] ADD
CONSTRAINT [FK_EmployeeExtended
_AccessLevel] FOREIGN KEY
(
[AccLvlID]
) REFERENCES [dbo].[AccessLevel] (
[AccLvlID]
)
GO

ALTER TABLE [dbo].[EmployeeFilter] ADD
CONSTRAINT [FK_EmployeeFilter_E
mployeeExtended] FOREIGN KEY
(
[EmpID]
) REFERENCES [dbo].[EmployeeExtended] (
[EmpID]
)
GO

ALTER TABLE [dbo].[EmployeeProcesses] ADD
CONSTRAINT [FK_EmployeeProcesse
s_EmployeeExtended] FOREIGN KEY
(
[EmpID]
) REFERENCES [dbo].[EmployeeExtended] (
[EmpID]
)
GO

ALTER TABLE [dbo].[EmployeeShortcuts] ADD
CONSTRAINT [FK_EmployeeShortcut
s_EmployeeExtended] FOREIGN KEY
(
[EmpID]
) REFERENCES [dbo].[EmployeeExtended] (
[EmpID]
)
GO

ALTER TABLE [dbo].[LiveProduction] ADD
CONSTRAINT [FK_LiveProduction_E
mployeeExtended] FOREIGN KEY
(
[EmpID]
) REFERENCES [dbo].[EmployeeExtended] (
[EmpID]
)
GO

ALTER TABLE [dbo].[Production] ADD
CONSTRAINT [FK_Production_Emplo
yeeExtended] FOREIGN KEY
(
[EmpID]
) REFERENCES [dbo].[EmployeeExtended] (
[EmpID]
)
GO

There are no triggers on any of the tables.

I think that server has RAID 5, but I'm not sure.

The indexes are rebuilt as part of a maintenance plan, and as such use the
extended stored procedure in master xp_sqlmaint.

"pdxJaxon" wrote:

> something is not right.
>
> in showcontig, what does the "ScanDensity" number show (can you paste it in
> here) ?
>
> are the Primary Keys where your clustered index is placed Integers or GUIDS
> or something else ?
>
> Do you have triggers that fire on Insert and\or update ?
>
> What does your IO subSystem look like ? RAID 5, RAID 1+0, A SAN, etc etc etc
> ?
>
> How are indexes rebuilt nightly ? Dbcc DbReindex ? dbcc IndexDefrag ? Etc ?
>
> And are you "Certain" that your Primary Keys are Clustered ?
>
> The reason I ask, is this behavior sure sounds like behavior of a "Heap" to
> me.
>
>
> Greg Jackson
> PDX, Oregon
>
>
>

Sophie Guo [MSFT]

2005-03-31, 7:01 am

Hello,

I have created the tables on my side and didn't reproduce the issue. Can
you post here the detailed steps to reproduce the issue?

I suggest that you change Fill factors to 60% to test. This might improve
performance with frequent inserts.

Also, you can refer to the following article to troubleshoot the issue:

298475 HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475

Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Product Support Services and open a support incident and work
with a dedicated Support Professional.

Please be advised that contacting phone support will be a charged call.
However, if you are simply requesting a hotfix be sent to you and no other
support then charges are usually refunded or waived.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.

http://support.microsoft.com/defaul...US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.

Sophie Guo

Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

David Snyder

2005-03-31, 8:01 pm

I can't tell you what steps to do to reproduce the issue, as I haven't been
able to figure that out.

I'm going to follow the HOWTO mentioned below and see where that gets me. If
that doesn't fix the issue, I think my MSDN Universal Subscription includes
some support calls.

"Sophie Guo [MSFT]" wrote:

> Hello,
>
> I have created the tables on my side and didn't reproduce the issue. Can
> you post here the detailed steps to reproduce the issue?
>
> I suggest that you change Fill factors to 60% to test. This might improve
> performance with frequent inserts.
>
> Also, you can refer to the following article to troubleshoot the issue:
>
> 298475 HOW TO: Troubleshoot Application Performance Issues
> http://support.microsoft.com/?id=298475
>
> Generally, performance issues can be caused by various factors, and it is
> difficult to locate the root cause in a newsgroup thread. If the issue
> still exists after you have used the troubleshooting steps above, to
> efficiently troubleshoot a performance issue, we recommend that you contact
> Microsoft Product Support Services and open a support incident and work
> with a dedicated Support Professional.
>
> Please be advised that contacting phone support will be a charged call.
> However, if you are simply requesting a hotfix be sent to you and no other
> support then charges are usually refunded or waived.
>
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
>
> http://support.microsoft.com/defaul...US;PHONENUMBERS
>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
>
> Sophie Guo
>
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> ====================
====================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>

Sponsored Links