MMartin1 - My next plan is to change the clustered index key to a new Identity field so I can reduce the size of the indexes.This would remove the PieceID...

Shafat - I no not want to change the field used as the primary key at all. In fact, I will create a new non-clustered index for that field. I...

Grant - I am trying to improve performance. Our system has grown nearly 40% this year and there is a noticeable delay in stored procedure performance. We anticipate as much...

JeeTee,

That did the trick. My performance is now the same with or without the new column included. Thanks for all of your help.

Dave

Sry, I posted that before I noticed you had identified the same thing. I am adding the field to the included columns of the NC index you recommended now.

FYI - 84% of the cost in the execution plan is spent in a Key lookup on the PK_Details Clustered index

Here is the execution plan

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.4000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

...

There is also one view index.

CREATE NONCLUSTERED INDEX [IDX_View_ProcessPieceDetails] ON [dbo].[ProcessPiece_Details]

(

[AccountID] ASC,

[CreatedDate] ASC,

[ServiceTypeCode] ASC

)

INCLUDE ( [PieceID],

[ReturnAddressID],

[ProcessClass],

[Name],

[CompanyName],

[Address1],

[Address2],

[City],

[State],

[ZIP],

[EntryPoint],

[Country],

[Cost],

[UnitOfMeasureCode],

[Weight],

[Height],

[Width],

[Depth],

[ProcessingCategory],

[DestinationRateIndicator],

[Zone],

[ValueOfArticle],

[CostAmountDueShipper],

[HandlingCharge],

[SignatureWaiver],

[SurchargeType],

[SurchargeAmount],

[CreationMethod],

[Activated],

[ProcessingStatus],

[Booked],

[Delivered],

[SignatureFileAvailable],

[SignatureFileRequested],

[LastModified],

[RecordCaseNumber],

[Memo],

[EFNumber],

[Type],

[ReturnAddress_CompanyName],

[ReturnAddress_Name],

[ReturnAddress_Address1],

[ReturnAddress_Address2],

[ReturnAddress_City],

[ReturnAddress_State],

[ReturnAddress_ZIP]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY...

Here are the non-clustered indexes

CREATE NONCLUSTERED INDEX [IDX_PieceIDAccounts] ON [dbo].[ProcessPiece_Details]

(

[AccountID] ASC,

[PieceID] ASC,

[CreationMethod] ASC,

[CreatedDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING...

Here is the clustered index code

ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED

(

[PieceID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY =...

Here is the Table code

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ProcessPiece_Details](

[PieceID] [varchar](30) NOT NULL,

[AccountID] [int] NOT NULL,

[ReturnAddressID] [int] NOT NULL,

[ProcessClass] [char](2) NULL,

[Name] [varchar](65) NULL,

[CompanyName] [varchar](65) NULL,

[Address1] [varchar](65) NULL,

[Address2]...

Here is the stored procedure that is causing the delay whenever I include the d.TID field in the SELECT statement

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: DCA, based on original...

Just an update - The issue must be specific to the stored procedure because i can do a simple query of the table in SSMS that includes the newly added...

FYI: Here are the option settings for the clustered index of the table.

Automatically recompute statistics: Checked

Use row locks when accessing the index: Checked

Use page locks when accessing the index: Checked

Set...

The table is the primary table used for item processing. The design of the whole system itself certainly has issues such as the choice of clustering key which leads to...