October 8, 2009 at 7:03 am
The following sproc consistently degrades in performance at some variable time after a reindex. Immediately after a reindex it runs in 20 sec. Within a few hours to few days it will degrade to taking 20 to 40 minutes to execute. The physical server has quad processors and 4 GB ram, the SQL Server Version is 2005. The number of concurrent users runs between 20 to 40. Sproc ran fine under 2000. Any ideas?
USE [DatabaseName]
GO
/****** Object: StoredProcedure [dbo].[usp_ReportSPName] Script Date: 10/08/2009 07:58:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[usp_ReportSPName](
@p_strSalesCoID int = NULL,
@p_strSupplierTypeID int = NULL,
@p_strSupplierID int = NULL,
@p_strSupAccountID int = NULL,
@p_strPeriodID int = NULL,
@p_strTransTypeID int = NULL)
AS
SET NOCOUNT ON
declare
@strBase nvarchar(400),
@strtemp nvarchar(400),
@strSQL nvarchar(4000),
@strSQL2 nvarchar(4000)
DECLARE
@tmpTableSQL varchar(1024),
@tmpTableName varchar(128),
@tmpTableWorking varchar(128),
@tmpTableWorkingName varchar(128),
@ProcId varchar(5)
-- Create the temp table
select @tmpTableWorkingName = '##tblWorking' + convert(varchar,@@SPID)
select @strSQL = '
CREATE TABLE '+ @tmpTableWorkingName + ' (
intSalesJournalActivityId int,
intSalesJournalTransactionId int,
vcrSJTDesc varchar(75),
intShipmentID int,
intIndustryShipmentId int,
intShipmentLayerId int,
intJournalEventId int,
intJournalEntryId int,
dtmSJTDate smalldatetime,
intFiscalPeriodId int,
intPartyToId int,
intPartyFromId int,
intJournalPriceClassificationId int,
Tons decimal(14,4),
Amount decimal(14,4),
Freight decimal(14,4),
PrevTons decimal(14,4),
PrevAmount decimal(14,4),
PrevFreight decimal(14,4)
)'
exec(@strSQL)
set @tmpTableName = '##tmpTransactions' + + Convert(varchar,@@SPID)
set @tmpTableSQL = 'CREATE TABLE ' + @tmpTableName + '(
intSalesJournalActivityId int,
intSalesJournalTransactionId int,
intShipmentLayerId int,
intIndustryShipmentId int,
intShipmentId int,
intJournalPriceClassificationId int,
intPartyFromId int,
decSJATons decimal(14,4),
intPartyToId int,
intSJAUnitCount int,
curSJAAmount decimal(14,4),
intFiscalPeriodId int,
intJournalEventId int,
dtmSJTDate smalldatetime,
intJournalEntryId int,
bitSJAIsPassBack bit )'
exec (@tmpTableSQL)
set @tmpTableSQL = 'INSERT INTO ' + @tmpTableName +
' SELECT intSalesJournalActivityId,
intSalesJournalTransactionId,
intShipmentLayerId,
intIndustryShipmentId,
intShipmentId,
intJournalPriceClassificationId,
intPartyFromId,
(CASE WHEN (intJournalEntryId = 4) THEN (decSJATons) ELSE decSJATons END) AS [decSJATons],
intPartyToId,
intSJAUnitCount,
(CASE WHEN (intJournalEntryId = 4) THEN (curSJAAmount) ELSE curSJAAmount END) AS [curSJAAmount],
intFiscalPeriodId,
intJournalEventId,
dtmSJTDate,
intJournalEntryId,bitSJAIsPassBack
FROM dbo.udf_SA106(NULL,' + convert(varchar,@p_strPeriodID) + ') sja'
exec (@tmpTableSQL)
exec('CREATE UNIQUE CLUSTERED INDEX Idx1 ON ' + @tmpTableName + '(intSalesJournalActivityId)')
exec('CREATE INDEX Idx2 ON ' + @tmpTableName + ' (intSalesJournalTransactionId)')
exec('CREATE INDEX Idx3 ON ' + @tmpTableName + ' (intShipmentLayerId)')
exec('CREATE INDEX Idx4 ON ' + @tmpTableName + ' (intIndustryShipmentId)')
exec('CREATE INDEX Idx5 ON ' + @tmpTableName + ' (intShipmentId)')
exec('CREATE INDEX Idx6 ON ' + @tmpTableName + ' (intJournalPriceClassificationId)')
select @strBase = 'INSERT INTO ' + @tmpTableWorkingName + ' exec '
if @p_strPeriodID is NOT NULL
Begin
select @strTemp = 'usp_ReportSA_CA NULL,' + convert(nvarchar,@p_strPeriodID)
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_CW NULL, ' + convert(nvarchar,@p_strPeriodID)
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_DW NULL, ' + convert(nvarchar,@p_strPeriodID)
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_Inv NULL, ' + convert(nvarchar,@p_strPeriodID)
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_SA NULL, ' + convert(nvarchar,@p_strPeriodID)
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_TS NULL, ' + convert(nvarchar,@p_strPeriodID)
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
end
else
begin
select @strTemp = 'usp_ReportSA_CA'
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_CW'
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_DW'
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_Inv'
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_SA'
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
select @strTemp = 'usp_ReportSA_TS'
select @strSQL = @strBase + @strTemp
exec sp_executesql @strSQL
end
select @strSQL2 = 'DELETE FROM ' + @tmpTableWorkingName + '
WHERE ((Tons - PrevTons BETWEEN -.009999 AND .009999) AND
(Amount - PrevAmount BETWEEN -.009999 AND .009999) AND
(Freight - PrevFreight BETWEEN -.009999 AND .009999))'
exec sp_executesql @strSQL2
--add the indexes
exec('CREATE UNIQUE CLUSTERED INDEX Idx1 ON ' + @tmpTableWorkingName + '(intSalesJournalActivityId)')
exec('CREATE INDEX Idx2 ON ' + @tmpTableWorkingName + ' (intSalesJournalTransactionId)')
exec('CREATE INDEX Idx3 ON ' + @tmpTableWorkingName + ' (intShipmentLayerId)')
exec('CREATE INDEX Idx4 ON ' + @tmpTableWorkingName + ' (intIndustryShipmentId)')
exec('CREATE INDEX Idx5 ON ' + @tmpTableWorkingName + ' (intShipmentId)')
exec('CREATE INDEX Idx6 ON ' + @tmpTableWorkingName + ' (intJournalPriceClassificationId)')
Select @strSQL = 'select distinct coalesce(sac.vcrSACName,'''') as [SalesCo],
coalesce(sa.vcrSUPName,'''') as [Sup Acct],
case when (sup.bitSPRCompanyFlag = 0) then ''Independent'' else ''Subsidiary'' end as [SupType],
coalesce(fp.vcrFPDDesc,'''') as [Period],
case when (fp.dtmFPDRoll is NULL) then ''Open'' else ''Closed'' end as [Status],
coalesce(je.vcrJEVDesc,'''') as [Trans Type],
coalesce(uvw.dtmSJTDate,'''') as [Trans Date],
uvw.intSalesJournalTransactionID as [Trans ID],
coalesce(sj.vcrSJTDesc,'''') as [Trans Desc],
coalesce((CASE WHEN uvw.intJournalEntryId = 4 THEN (0-(uvw.Tons - uvw.PrevTons))ELSE (uvw.Tons - uvw.PrevTons)END),0.00) as [Tons],
coalesce((CASE WHEN uvw.intJournalEntryId = 4 THEN (0-(uvw.Amount - uvw.PrevAmount)) ELSE (uvw.Amount - uvw.PrevAmount)END),0.00) as [Coal Amount],
coalesce((CASE WHEN uvw.intJournalEntryId = 4 THEN (0-(uvw.Freight - uvw.PrevFreight)) ELSE (uvw.Freight - uvw.PrevFreight) END),0.00) as [SH Amount],
shp.dtmSHPShipDate as [ShipDate],
shp.intShipmentID as [ShipID],
coalesce(crt.vcrCRTDesc,'''') as [CarrierType],
coalesce(shp.vcrSHPTrainNum,'''') as [TLNum],
coalesce(so.vcrSLOSalesOrderNumber,'''') as [OrderNum],
coalesce(ca.vcrCUAName,'''') as [CustAcct],
coalesce(uvw.vcrSJTDesc,'''') as [TransDesc],
coalesce(uvw.intShipmentLayerID,'''') as [LayerID]
from ' + @tmpTableWorkingName + ' uvw
inner join tblSalesJournalTransaction sj on sj.intSalesJournalTransactionID = uvw.intSalesJournalTransactionID
inner join tblJournalEvent je on je.intJournalEventID = uvw.intJournalEventID
inner join tblPartyFrom pf on pf.intPartyFromID = uvw.intPartyFromID
inner join tblPartyTo pt on pt.intPartyToID = uvw.intPartyToID
inner join tblParty par on par.intPartyID = pf.intPartyID
inner join tblParty par2 on par2.intPartyID = pt.intPartyID
inner join tblSupplierAccountParty sap on sap.intPartyID = par.intPartyID
inner join tblSupplierAccount sa on sa.intSupplierAccountID = sap.intSupplierAccountID
inner join tblSupplier sup on sup.intSupplierID = sa.intSupplierID
inner join tblShipment shp on shp.intShipmentID = uvw.intShipmentID
inner join tblFiscalPeriod fp on fp.intFiscalPeriodId = uvw.intFiscalPeriodId
inner join tblSalesOrderRoute sor on sor.intSalesOrderRouteID = shp.intSalesOrderRouteID
inner join tblSalesOrder so on so.intSalesOrderID = sor.intSalesOrderID
inner join tblSalesCompany sac on sac.intSalesCompanyID = so.intSalesCompanyID
inner join tblCustomerAccount ca on ca.intCustomerAccountID = so.intCustomerAccountID
inner join tblCarrierRoute cr on cr.intCarrierRouteID = sor.intCarrierRouteID
inner join tblCarrier c on c.intCarrierID = cr.intCarrierID
inner join tblCarrierType crt on crt.intCarrierTypeID = c.intCarrierTypeID
WHERE /*uvw.intJournalEntryID <> 4
AND*/ ((uvw.freight - uvw.PrevFreight) <> 0 or (uvw.Tons - uvw.PrevTons) <> 0 or (uvw.Amount - uvw.PrevAmount) <> 0) '
if @p_strSalesCoID is NOT NULL
Select @strSQL = @strsql + ' AND sac.intSalesCompanyID=' + CONVERT(nvarchar, @p_strSalesCoID)
if @p_strSupplierTypeID is NOT NULL
Select @strSQL = @strsql + ' AND sup.bitSPRCompanyFlag=' + CONVERT(nvarchar, @p_strSupplierTypeID)
if @p_strSupplierID is NOT NULL
Select @strSQL = @strsql + ' AND sup.intSupplierID=' + CONVERT(nvarchar, @p_strSupplierID)
if @p_strSupAccountID is NOT NULL
Select @strSQL = @strsql + ' AND sa.intSupplierAccountId=' + CONVERT(nvarchar, @p_strSupAccountID)
if @p_strPeriodID is NOT NULL
Select @strSQL = @strsql + ' AND fp.intFiscalPeriodId=' + CONVERT(nvarchar, @p_strPeriodID)
if @p_strTransTypeID is NOT NULL
Select @strSQL = @strsql + ' AND je.intJournalEventID=' + CONVERT(nvarchar, @p_strTransTypeID)
Select @strSQL = @strSQL + ' Order By [Trans Date]'
exec sp_executesql @strSQL
set @tmpTableSQL = 'DROP TABLE ' + @tmpTableName
exec (@tmpTableSQL)
exec('DROP TABLE ' + @tmpTableWorkingName)
SET NOCOUNT OFF
October 8, 2009 at 10:30 am
have you tried tracing where the poor performance is coming from when you're having the issue? Is other stuff happening at the same time? Is there a large data change which is messing up the stats? Have you checked the execution plan when its ok and when its bad to make sure they're the same?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 8, 2009 at 2:11 pm
No it is not a matter of the number of users at the time the query runs. Once the change takes place it requires a reindex to correct it. Resetting the statistics will not fix it. As for data change, not that much changes over two or three days. That is the longest it has taken for the the issue to occur. Additionally, a reindex is now occuring every night. So it is now occuring in the scope of 24 hours.
The executions plans appear to be identical. Neither are showing table scans or other red flags that I would normally watch for.
October 9, 2009 at 6:21 am
This routine is the only one that has been identified as having this problem after our upgrade to 2005. It is a critical procedure and I would rewrite it except I can't see what area or areas are problems. I've tried using SHOWPLAN_TEXT, SHOWPLAN_ALL, I've ran SHOWPLAN_ALL in profiler. I'm at a loss.
I feel strongly that it has to do with the indexes only because a reindex temporarily corrects the problem. We have a qa environment and the same problem occurs on the qa server and production.
A few of the tables may have 40 to 60records changed or added daily. Other areas of db may have thousands of records added daily. But none of those more active tables are accessed in this query.
October 9, 2009 at 8:58 am
What is happening to tempDB when you start seeing your bad behavior? You're creating an wful lot of temporary objects.
Also since you don't know where the problem is - put in tracer PRINT startements with timestamps so you can figure out where things that a while.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply