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
IX_PpeID_PeID
index may or may not be optimal. AnOPTION(RECOMPILE)
query hint will help avoid parameter sniffing issues.SELECT
as the cause of the extremely bad performance. This isn't to suggest your indexing doesn't need improvement.