Simple insert statements are taking so long

Question

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

Answer 1

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.