I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long
I know there are lots of possibilities for instance Disk, CPU, Memory, indexes and triggers
How do I find Clustered and NON-Clustered indexes are not the reason of this slowness?
Insert Statement:
INSERT INTO PEDetail with(rowLock) (CID,CustID,PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID)
SELECT CID,@PID,@PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID
FROM PEDetail with(nolock)
where PpeID = @OldPpeid
Table Structure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PEDetail](
[CID] [int] NOT NULL,
[CustID] [int] NOT NULL,
[PpeID] [int] NOT NULL,
[PeID] [int] NOT NULL,
[SubSys1ID] [int] NOT NULL,
[SubSys2ID] [int] NOT NULL,
[CharacteristicID] [int] NOT NULL,
[cmbID] [int] NOT NULL,
[PETemplateID] [int] NOT NULL,
CONSTRAINT [PK_PEDetail] PRIMARY KEY CLUSTERED
(
[CID] ASC,
[CustID] ASC,
[PpeID] ASC,
[PeID] ASC,
[SubSys1ID] ASC,
[SubSys2ID] ASC,
[CharacteristicID] ASC,
[cmbID] ASC,
[PETemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PEDetail] WITH CHECK ADD CONSTRAINT [FK_PEDetail_PEHeader] FOREIGN KEY([PpeID], [PeID])
REFERENCES [dbo].[PEHeader] ([PpeID], [PeID])
GO
ALTER TABLE [dbo].[PEDetail] CHECK CONSTRAINT [FK_PEDetail_PEHeader]
GO
ALTER TABLE [dbo].[PEDetail] ADD CONSTRAINT [DF_PEDetail_cmbID] DEFAULT ((0)) FOR [cmbID]
GO
NON Clustered Indexes:
CREATE NONCLUSTERED INDEX [ix_PEDetail_CID_PeID_6] ON [dbo].[PEDetail]
(
[CID] ASC,
[PeID] ASC,
[SubSys1ID] ASC,
[SubSys2ID] ASC,
[CharacteristicID] ASC,
[PETemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CID_CharacteristicID_CID_CharacteristicID] ON [dbo].[PEDetail]
(
[CID] ASC,
[CharacteristicID] ASC
)
INCLUDE ( [PpeID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PpeID_PeID] ON [dbo].[PEDetail]
(
[PpeID] ASC,
[PeID] ASC
)
INCLUDE ( [CID],
[CustID],
[SubSys1ID],
[SubSys2ID],
[CharacteristicID],
[cmbID],
[PETemplateID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
Best Answer
The performance problem it's because of your clustered index. When you use clustered, and you add a new record, it needs to physically rearrange all records in the table to make space for it.
Drop the clustered index and create an unique non-clustered index instead. That should fix your performance problem.
As a general rule, I'd also recommend having a clustered index, but with only a numerical autoincremental id.