December 6, 2014 at 9:12 pm
I have a table with about 17 million rows. I have a stored procedure that creates a record set from this table for records that need processing. The stored procedure has a SELECT clause for this table, listing about 70 of the table's fields plus about 40 CAST elements to add more fields (fabricated) so that the resulting data set matches the data object calling the stored procedure.
Normally, this stored procedure may take a couple of minutes. However, I added one new varchar(30) field to the table and, subsequently to the list of field in the select statement and the as a result, now the store procedure takes from 10 to 20 minutes to run. If I comment out the single varchar field that I added, the stored procedure goes back to the couple of minutes to run.
The newly added varchar field is not part of any index or constraint.
Any ideas as to why this could be happening?
Thanks in advance,
Dave
December 6, 2014 at 10:35 pm
You have to remember that EVERY column is a part of the leaf level of the clustered index or a part of the heap. If you added data to the new column, there's likely been massive page splits. Check for both fragmentation and % of page used for this table. It's likely that you'll have to rebuild at least the clustered index. If it's a heap, then you'll need to build a clustered index on it to defrag it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2014 at 1:19 pm
In looking at the clustered index for the table in question, in the Fragmentation section, I have the following data;
Page Fullness: 81.87%
Total Fragmentation: 0.05%
Average Row Size: 299
Depth: 4
Forwarded Records: 0
Ghost Rows: 0
Index Type: Clustered Index
Leaf-level Rows: 17251500
Maximum Row Size: 682
Minimum Row Size: 213
Pages: 782373
Partition ID: 1
Version Ghost Rows: 0
Note: We have a maintenance plan called "Full Database Tables Index Rebuild 1st Saturday of Month"
From what I can tell, the fragmentation is very low and the page is not full. Is that your assessment as well?
If so, do you have any other ideas?
Thanks,
Dave
December 7, 2014 at 1:27 pm
I had added 4 other new fields after I had added the one in question in this post. In my testing, If I include any one of the new fields in the SELECT portion of the query, I get the same problem. These fields have been added to the table for several weeks, but most of the records will have NULL in their values.
So, to recap; If I include any single one of the newly created fields in the SELECT portion of the stored procedure as a test, I get the huge time delay in the stored procedure's execution. None of the newly added fields are part of any non-clustered index. The clustered index does not appear to be badly fragmented.
If there are any system SPs or DMs you would like to see that may help in troubleshooting this issue, I will be happy to provide them.
Thanks,
Dave
December 7, 2014 at 2:13 pm
The first thought on this problem is not the addition of a column but the design itself, which prompts questions on the role of the table, the practice of adding columns and (relatively) large data type conversions in normal retrieval operations.
😎
December 7, 2014 at 3:24 pm
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 the relatively large amount of index data for the non-clustered. The design is being addressed in a re-design that is in progress, but this specific issue was a needed set of fields to comply with a time sensitive mandate that was out of my control.
However, the immediate problem is the one I am tasked with and must be addressed without requiring any changes that would impact the API, and as a result the many client applications that depend on it.
It seems that there must be a reason why I am seeing this performance issue, specifically in this one stored procedure, that only happens if I add any of the newly created fields to the SELECT statement of the stored procedure. The fields are not used in any non-clustered indexes. The row size is not near the 8060 byte limit. The clustered index is not greatly fragmented and the page fill percentage is high but not 100%.
What additional information can I provide to help troubleshoot this issue?
Thanks,
Dave
December 7, 2014 at 3:28 pm
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 fill factor: Checked, set at 80%
Use Index: Checked
December 8, 2014 at 1:56 am
Hi,
The best thing you can provide is the create script for the table itself (including indexes) and possibly stored procedure that you have problems with. Maybe triggers if any.
December 8, 2014 at 6:26 am
Examing the Execution Plans of the query both with and without the column in question might provide a clue as to what is going on.
Just a thought.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
December 8, 2014 at 9:52 am
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 fields and I do not see the delay of 10-20 minutes.
I will post the table creation script, stored procedure and execution plan this afternoon.
Thanks,
Dave
December 8, 2014 at 11:44 am
Maybe this is a dumb question, but was there a covering index on your query before you added the new columns? You saying that including any of the new columns causes the same delay; maybe before you were getting a pure index seek, but now it has to do an index lookup?
If so, including the new field(s) in the index would probably get the behavior back to the old plan.
December 8, 2014 at 1:13 pm
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 stored procedure by WTS
-- Create date: 12/06/14
-- Description:Gets unprocessed pieces. This is a test version of the GetUnprocessedPieces
-- stored procedure that I am using to test ways to improve performance
-- =============================================
CREATE PROCEDURE [dbo].[GetUnprocessedPieces_TEST]
@AccessId UniqueIdentifier,
@AdditionalAccountsToProcess AS VARCHAR(MAX) -- This is an optional parameter
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AccountId INT
DECLARE @AccountIDs TABLE (ID INT)
DECLARE @IncludedAccounts TABLE (ID INT)
DECLARE @ProcessWithStatus TABLE ([Status] INT)
-- Look up the AccountID vaule un the Users table using the AccessID value and return the AccountID
SET @AccountId = dbo.GetAccountIdFromAccessId(@AccessId)
-- If any additional AccountIDs are passed in to the procedure as a comma delimited string via the @AdditionalAccountsToProcess parameter,
-- split the string, convert the values to INT and INSERT them to the @IncludedAccounts table
INSERT INTO @IncludedAccounts
SELECT CAST(REPLACE(Items, '''', '') AS INT) FROM dbo.FUNCT_SplitStringToTable(@AdditionalAccountsToManifest,',')
IF @AccountId = 1 -- Primary account in hiararchy
BEGIN
-- Primarily used for Automated Processing that is triggerted every 15 minutes via a windows service
-- Only gets pieces that are designated as needing Auto Processing (ProcessingStatus = 0 or 1)
INSERT INTO @ProcessWithStatus ([Status]) VALUES (0)
INSERT INTO @ProcessWithStatus ([Status]) VALUES (1)
-- The GetAssociatedAccountIDs function iteratively scans the User table for any user accounts that have their main account value set to
-- an account that ultimately derives from the @AccountID (primary) account.
INSERT INTO @AccountIDs
SELECT * FROM FUNCT_GetAssociatedAccountIDs(@AccountID)
END
ELSE
BEGIN
-- Used for calls from API by client applications
-- Only gets pieces that are designated as needing Manual Processing (ProcessingStatus = 10 or 11)
INSERT INTO @ProcessWithStatus ([Status]) VALUES (10)
INSERT INTO @ProcessWithStatus ([Status]) VALUES (11)
-- The GetAssociatedAccountIDs function scans the User table for any user accounts that have their main account value set to
-- an accountID that exists in the @IncludedAccounts table.
-- .
INSERT INTO @AccountIDs
SELECT * FROM FUNCT_GetAssociatedAccountIDs(@AccountID) aid WHERE
EXISTS(SELECT NULL FROM @IncludedAccounts e WHERE e.id = aid.id)
-- Make sure to add the Primary account passed into the procedure
INSERT INTO @AccountIds (ID) VALUES (@AccountId)
END
--Get all unprocessed pieces that belong to the user accounts now listed in the AccountIDs table
SELECT
CAST(IsNULL(sub.ChildPieceID,d.PieceID) AS VARCHAR(30)) AS PieceID,
CAST(SUBSTRING(CASE
WHEN LEN(RTRIM(LTRIM(d.EntryPoint))) = 0 THEN d.ReturnAddress_ZIP
ELSE ISNULL(d.EntryPoint,'')
END, 1, 5) AS VARCHAR(50)) AS ProcessingEntryPoint,
d.EntryPoint,
d.AccountID,
d.ReturnAddressID,
d.ProcessClass,
d.Name,
d.CompanyName,
d.Address1,
d.Address2,
d.City,
d.State,
d.ZIP,
CAST(ISNULL(d.Country, ' ') AS CHAR(2)) AS Country,
d.Cost,
d.UnitOfMeasureCode,
d.Weight,
d.Height,
d.Width,
d.Depth,
d.ProcessingCategory,
ISNULL(d.DestinationCostIndicator, 'N') AS DestinationCostIndicator,
ISNULL(d.Zone, '') AS Zone,
d.ValueOfArticle,
d.CostAmountDueShipper,
d.HandlingCharge,
d.SignatureWaiver,
ISNULL(d.SurchargeType, '') AS SurchargeType,
d.SurchargeAmount,
d.CreationMethod,
d.Activated,
d.ProcessingStatus,
d.Booked,
d.Delivered,
d.SignatureFileAvailable,
d.SignatureFileRequested,
d.LastModified,
d.CreatedDate,
d.RecordCaseNumber,
d.Memo,
d.LastProcessingEventCheck,
d.EFNumber,
d.eVS,
d.IsDemo,
d.PermitID,
d.UnitOfMeasurement_Dimensions,
d.ServiceTypeCode,
d.[Type],
d.BarcodeConstructCode,
d.OD_ContainerID1,
d.OD_ContainerType1,
d.OD_ContainerID2,
d.OD_ContainerType2,
d.OD_ContainerID3,
d.OD_ContainerType3,
d.OD_ContentsIndicator,
d.OD_MailClassEnclosed,
d.ReturnAddress_CompanyName,
d.ReturnAddress_Name,
d.ReturnAddress_Address1,
d.ReturnAddress_Address2,
d.ReturnAddress_City,
d.ReturnAddress_State,
d.ReturnAddress_ZIP,
d.IsCubic,
u.UserName,
u.Name AS CreatorName,
u.CompanyName As CreatorCompanyName,
-- -----------------------------------------------------------------------------------------------------------
-- The TID field was added on 11/21/14 as required by the policy change mandate and is now required to be part of
-- the data set for the calling client application
-- -----------------------------------------------------------------------------------------------------------
d.TID,
-- -----------------------------------------------------------------------------------------------------------
-- The VendorPieceID,VendorMachineID, PermitNumber, PrintedIMpb, PostOfficeOfAccountZip and DestinationDeliveryPoint fields
-- were added on 11/21/14 as required by the policy change mandate but are not required to be part of
-- the data set for the calling client application. I have them here to test is any new field added will cause the same 10-20 min
-- processing delay for the stored procedure
-- -----------------------------------------------------------------------------------------------------------
--VendorPieceID,
--VendorMachineID,
--PermitNumber,
--PrintedPieceID,
-- -----------------------------------------------------------------------------------------------------------
-- The Alias fields below are added to the SELECT statement so that the result set
-- matches the data object fields in the client application that is calling this stord procedure
-- -----------------------------------------------------------------------------------------------------------
CAST(' ' AS CHAR(1)) AS DestinationFacilityType,
CAST(' ' AS CHAR(5)) AS CarrierRouteCode,
CAST(' ' AS CHAR(11)) AS ForeignPostalCode,
CAST('' AS VARCHAR(9)) AS LogisticsMID,
CAST('' AS VARCHAR(9)) AS OwnerMID,
CAST('' AS VARCHAR(15)) AS CustomerRegistrationID,
CAST('' AS CHAR(15)) AS FASTReservationNumber,
CAST(NULL AS SMALLDATETIME) AS FASTInductionDate,
CAST(2 AS TINYINT) AS ElectronicFileType,
CAST('00000' AS CHAR(5)) AS PostOfficeOfAccountZIP,
CAST('' AS VARCHAR(20)) AS MeterSerialNumber,
CAST('' AS VARCHAR(6)) AS ChargeBackCode,
CAST('' AS CHAR(1)) AS PostageType,
CAST('' AS VARCHAR(22)) AS ShippingServicesContractNumber,
CAST('' AS VARCHAR(14)) AS ShippingServicesContractsProductId,
CAST('' AS CHAR(2)) AS DiscountType,
CAST(NULL AS DECIMAL) AS DiscountAmount,
CAST('' AS CHAR(2)) AS NonIncidentalEnclosureRateIndicator,
CAST('' AS CHAR(2)) AS NonIncidentalEnclosureClass,
CAST(NULL AS DECIMAL) AS NonIncidentalEnclosurePostage,
CAST(NULL AS DECIMAL) AS NonIncidentalEnclosureWeight,
CAST('' AS CHAR(1)) AS NonIncidentalEnclosureProcessingCategory,
CAST('' AS CHAR(1)) AS PostalRoutingBarcode,
CAST('' AS CHAR(2)) AS OpenAndDistributeContentsIndicator,
CAST('N' AS CHAR(1)) AS POBoxIndicator,
CAST('N' AS CHAR(1)) AS WaiverOfSignature,
CAST('1' AS CHAR(1)) AS DeliveryOptionIndicator,
CAST('00' AS CHAR(2)) AS DestinationDeliveryPoint,
CAST('' AS VARCHAR(64)) AS RecipientEmailAddress,
CAST('' AS VARCHAR(64)) AS RecipientSMSNumber,
CAST('' AS VARCHAR(48)) AS SenderName,
CAST('' AS VARCHAR(64)) AS SenderEmailAddress,
CAST('' AS VARCHAR(64)) AS SenderSMSNumber,
CAST(0 AS BIT) AS RequiresTracking
FROM
@AccountIDs tmpAccounts
INNER JOIN dbo.ProcessPiece_Details d WITH (NOLOCK) On tmpAccounts.Id = d.UserId
INNER JOIN dbo.Users u WITH (NOLOCK) ON u.ID = d.AccountID
LEFT JOIN dbo.ProcessPiece_AssociatedPieceIDs sub WITH (NOLOCK) ON d.PieceID = sub.ParentPieceID AND sub.PieceIDAssociationType = 'substitution'
LEFT JOIN dbo.ProcessPiece_Details d2 WITH (NOLOCK) ON sub.ChildPieceID = d2.PieceID
WHERE
d.CreatedDate > '1/1/2012'
AND
d.Type = 'PV2'
AND
d.eVs = 0
AND
d.Activated = 1
AND
EXISTS (SELECT NULL FROM @ProcessWithStatus WHERE [Status] = d.ProcessingStatus)
AND
d.IsDemo = 0
AND
LEN(RTRIM(LTRIM(d.ZIP))) > 0
AND
(d2.Type <> 'LEGACYV2' OR d2.Type IS NULL)
END
GO
December 8, 2014 at 1:15 pm
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] [varchar](65) NULL,
[City] [varchar](50) NULL,
[State] [char](2) NULL,
[ZIP] [varchar](10) NULL,
[EntryPoint] [char](5) NOT NULL,
[Country] [char](2) NULL,
[Cost] [int] NOT NULL,
[UnitOfMeasureCode] [tinyint] NULL,
[Weight] [int] NULL,
[Height] [decimal](18, 4) NULL,
[Width] [decimal](18, 4) NULL,
[Depth] [decimal](18, 4) NULL,
[ProcessingCategory] [tinyint] NULL,
[DestinationRateIndicator] [char](1) NULL,
[Zone] [varchar](2) NULL,
[ValueOfArticle] [int] NULL,
[CostAmountDueShipper] [int] NULL,
[HandlingCharge] [int] NULL,
[SignatureWaiver] [bit] NOT NULL,
[SurchargeType] [varchar](2) NULL,
[SurchargeAmount] [int] NULL,
[CreationMethod] [varchar](50) NULL,
[Activated] [bit] NOT NULL,
[ProcessingStatus] [int] NOT NULL,
[Booked] [bit] NULL,
[Delivered] [bit] NOT NULL,
[SignatureFileAvailable] [bit] NOT NULL,
[SignatureFileRequested] [bit] NOT NULL,
[LastModified] [datetime] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[RecordCaseNumber] [varchar](50) NOT NULL,
[Memo] [varchar](1024) NULL,
[LastProcessingEventCheck] [datetime] NULL,
[EFNumber] [varchar](30) NULL,
[eVS] [bit] NOT NULL,
[IsDemo] [bit] NOT NULL,
[PermitID] [int] NULL,
[UnitOfMeasurement_Dimensions] [tinyint] NULL,
[ServiceTypeCode] [char](3) NULL,
[Type] [varchar](20) NULL,
[BarcodeConstructCode] [char](3) NULL,
[OD_ContainerID1] [varchar](30) NULL,
[OD_ContainerType1] [char](2) NULL,
[OD_ContainerID2] [varchar](30) NULL,
[OD_ContainerType2] [char](2) NULL,
[OD_ContainerID3] [varchar](30) NULL,
[OD_ContainerType3] [char](2) NULL,
[OD_ContentsIndicator] [char](2) NULL,
[OD_MailClassEnclosed] [char](2) NULL,
[ReturnAddress_CompanyName] [varchar](65) NULL,
[ReturnAddress_Name] [varchar](65) NULL,
[ReturnAddress_Address1] [varchar](65) NULL,
[ReturnAddress_Address2] [varchar](65) NULL,
[ReturnAddress_City] [varchar](50) NULL,
[ReturnAddress_State] [char](2) NULL,
[ReturnAddress_ZIP] [varchar](10) NULL,
[IsCubic] [bit] NULL,
[AssociatedPieceID] [varchar](30) NULL,
[TrackingRequested] [bit] NULL,
[TID] [varchar](30) NULL,
[VendorPieceID] [varchar](30) NULL,
[VendorMachineID] [varchar](10) NULL,
[PermitNumber] [int] NULL,
[PrintedPieceID] [varchar](40) NULL,
[PostOfficeOfAccountZip] [varchar](5) NULL,
[DestinationDeliveryPoint] [char](2) NULL,
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[PieceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ProcessPiece_Details] WITH NOCHECK ADD CONSTRAINT [FK_ProcessPiece_Details_Users] FOREIGN KEY([AccountID])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[ProcessPiece_Details] CHECK CONSTRAINT [FK_ProcessPiece_Details_Users]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_Cost] DEFAULT ((0)) FOR [Cost]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_SignatureWaiver] DEFAULT ((0)) FOR [SignatureWaiver]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_Detail_Activated] DEFAULT ((0)) FOR [Activated]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_Detail_ProcessingStatus] DEFAULT ((0)) FOR [ProcessingStatus]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_Booked] DEFAULT ((0)) FOR [Booked]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_Delivered] DEFAULT ((0)) FOR [Delivered]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_Detail_SignatureFileAvailable] DEFAULT ((0)) FOR [SignatureFileAvailable]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_SignatureFileRequested] DEFAULT ((0)) FOR [SignatureFileRequested]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_Detail_LastModified] DEFAULT (getdate()) FOR [LastModified]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_Detail_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_RecordCaseNumber] DEFAULT ('') FOR [RecordCaseNumber]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_eVS] DEFAULT ((0)) FOR [eVS]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_IsDemo] DEFAULT ((0)) FOR [IsDemo]
GO
ALTER TABLE [dbo].[ProcessPiece_Details] ADD CONSTRAINT [DF_ProcessPiece_Details_TrackingRequested] DEFAULT ((1)) FOR [TrackingRequested]
GO
December 8, 2014 at 1:15 pm
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 = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
December 8, 2014 at 1:18 pm
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply