Performance Question

Question

Post reply

Performance Question

bridgt01

SSCommitted

Points: 1889
More actions
November 17, 2011 at 1:30 pm

#252100

I'm having an issue with a stored procedure that queries a table valued function which uses a number of table variables. When SQL Server is recycled the stored procedure doesn't complete (I can also recompile the table value function to make it perform poorly also). If I run the stored procedure and its parameters through the database tuning advisor it doesn't recommend any indexes. However, when I execute the stored procedure again afterwards it returns within 2 minutes. I've looked at a profiler trace while the DTA was running but nothing was done, that I can see, other than the create\drop of hypothetical indexes. What may I be overlooking?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Review the spid doing the initial call after reboot, and review its wait stats. Find out WHY that first one is taking so long, you'll have a better idea.

My guess is the cache isn't loaded yet after the reboot so you're running into a ton of PageLatch_IO with a query that's poorly optimized.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

GSquared SSC Guru Points: 260824 More actions · Answer 2

It's pretty usual for queries of many sorts to run slower after a reboot, because of caching issues for both data and execution plans.

This sounds, however, like a good case for code optimization. Can you post the code and the table definitions it impacts? If so, performance improvements can probably be suggested.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bridgt01 SSCommitted Points: 1889 More actions · Answer 3

Here is the stored proc:

CREATE PROCEDURE [dbo].[usp_rpt_InflMon]

@OrganizationID Int=NULL,

@PlantID Int=NULL,

@CommodityManagerGroupID Int=NULL,

@CommodityManagerSubGroupID Int=NULL,

@CommLdrUserID Int=NULL,

@CommodityManagerUserID Int=NULL,

@SupplierCode Varchar(50)=NULL,

@SupplierName Varchar(50)=NULL,

@ContractNumber Varchar(50)=NULL,

@BaseYear Int,

@CurrSnapshotID Int,

@TrackedForInflation BIT = NULL, --SSR 80863 Change

@MaterialCostAgreement BIT = NULL --SSR 81055 Change

AS

SET NOCOUNT ON

DECLARE @AOPSnapshotID Int

DECLARE @JanPeriod Datetime

DECLARE @DecPeriod Datetime

DECLARE @CheckSupplierCount Int

IF NOT @SupplierCode IS NULL SET @SupplierCode = '%' + @SupplierCode + '%'

IF NOT @SupplierName IS NULL SET @SupplierName = '%' + @SupplierName + '%'

IF NOT @ContractNumber IS NULL SET @ContractNumber = '%' + @ContractNumber + '%'

-- SSR 81911 changes Begin

SET @CheckSupplierCount = 0

IF NOT @SupplierCode IS NULL SET @CheckSupplierCount = (SELECT COUNT(SupplierCode) FROM tSupplier WITH (NOLOCK) WHERE suppliercode LIKE @SupplierCode)

-- SSR 81911 changes End

SELECT @AOPSnapshotID = SnapshotID FROM tSnapshot WITH (NOLOCK) WHERE tSnapshot.Official = 1 AND tSnapshot.SnapshotTypeID = 1 AND tSnapshot.SnapshotYear = @BaseYear

SELECT @JanPeriod = Convert(Datetime, Convert(Varchar(4), @BaseYear) + '-01-01' ),

@DecPeriod = Convert(Datetime, Convert(Varchar(4), @BaseYear) + '-12-01' )

IF @CheckSupplierCount = 1 -- SSR 81911 Change

BEGIN -- SSR 81911 Change

SELECT

RepRowDesc,

ISNULL(pDec,0) AS pDec,

ISNULL(pAnn,0) AS pAnn,

ISNULL(Jan,0) AS Jan,

ISNULL(Feb,0) AS Feb,

ISNULL(Mar,0) AS Mar,

ISNULL(Apr,0) AS Apr,

ISNULL(May,0) AS May,

ISNULL(Jun,0) AS Jun,

ISNULL(Jul,0) AS Jul,

ISNULL(Aug,0) AS Aug,

ISNULL(Sep,0) AS Sep,

ISNULL(Oct,0) AS Oct,

ISNULL(Nov,0) AS Nov,

ISNULL([Dec],0) AS [Dec],

ISNULL(ExchangeRateDate,'') AS ExchangeRateDate, --SSR 80705 Change - Add a column for ExchangeRateDate

ISNULL(SupplierName,'') AS SupplierName ---SSR 81911 Change - Add a column to get Supplier Name for given Supplier Code.

FROM

( --SSR - 80698 Changes Begin

SELECT 1 AS RepRowID, 'AOP Volume' AS RepRowDesc

UNION ALL

SELECT 2, 'AOP Spend'

UNION ALL

SELECT 3, 'AOP Annual FC'

UNION ALL

SELECT 4, 'AOP Annual FX'

UNION ALL

SELECT 5, 'AOP Annual FO'

UNION ALL

SELECT 6, 'AOP Annual FR'

UNION ALL

SELECT 7, 'AOP Fiscal FC'

UNION ALL

SELECT 8, 'AOP Fiscal FX'

UNION ALL

SELECT 9, 'AOP Fiscal FO'

UNION ALL

SELECT 10, 'AOP Fiscal FR'

UNION ALL

SELECT 11, 'Curr Volume'

UNION ALL

SELECT 12, 'Curr Spend'

UNION ALL

SELECT 13, 'Curr Annual FC'

UNION ALL

SELECT 14, 'Curr Annual FX'

UNION ALL

SELECT 15, 'Curr Annual FO'

UNION ALL

SELECT 16, 'Curr Annual FR'

UNION ALL

SELECT 21, 'Backdated MPCAs Curr Annual'

UNION ALL

SELECT 17, 'Curr Fiscal FC'

UNION ALL

SELECT 18, 'Curr Fiscal FX'

UNION ALL

SELECT 19, 'Curr Fiscal FO'

UNION ALL

SELECT 20, 'Curr Fiscal FR'

--SSR - 82281 Changes Begin

UNION ALL

SELECT 22, 'Backdated MPCAs Curr Fiscal'

--SSR - 82281 Changes End

) AS RepRow --SSR - 80698 Changes End

LEFT OUTER JOIN

(

SELECT

RepRowID,

SUM(pDec) as pDec,

SUM(pAnn) as pAnn,

SUM(Jan) as Jan,

SUM(Feb) as Feb,

SUM(Mar) as Mar,

SUM(Apr) as Apr,

SUM(May) as May,

SUM(Jun) as Jun,

SUM(Jul) as Jul,

SUM(Aug) as Aug,

SUM(Sep) as Sep,

SUM(Oct) as Oct,

SUM(Nov) as Nov,

SUM([Dec]) as [Dec],

ExchangeRateDate, --SSR 80705 Change

SupplierName ---SSR 81911 Change - Add a column to get Supplier Name for given Supplier Code.

FROM

(

SELECT DISTINCT

ContractID

FROM

--SSR 82210 -Changes Begin

-- tSnapshotSummary WITH (NOLOCK)

-- WHERE

-- SnapshotID in (@AOPSnapshotID, @CurrSnapshotID) AND

-- Period BETWEEN @JanPeriod AND @DecPeriod

((SELECT DISTINCT ContractID FROM tSnapshotsummary WHERE SnapshotID in (@AOPSnapshotID, @CurrSnapshotID))

UNION

(SELECT DISTINCT ContractID FROM tHistorysummary WHERE Period >= Convert(Datetime, Convert(Varchar(4), (@BaseYear-1)) + '-12-01' )))AS Contracts

--SSR 82210 -Changes End

) AS SnapshotContract

INNER JOIN

tContract WITH (NOLOCK) ON SnapshotContract.ContractID = tContract.ContractID

INNER JOIN

tSupplier WITH (NOLOCK) ON tSupplier.SupplierID = tContract.SupplierID

INNER JOIN

tPlant WITH (NOLOCK) ON tContract.PlantID = tPlant.PlantID

LEFT OUTER JOIN

tSupplierOrganization WITH (NOLOCK) on tSupplier.SupplierID = tSupplierOrganization.SupplierID AND tPlant.OrganizationID = tSupplierOrganization.OrganizationID

INNER JOIN

tOrganization WITH (NOLOCK) ON tSupplierOrganization.OrganizationID = tOrganization.OrganizationID

LEFT OUTER JOIN

tCommodityManagerSubGroup WITH (NOLOCK) ON tSupplier.CommodityManagerSubGroupID = tCommodityManagerSubGroup.CommodityManagerSubGroupID

LEFT OUTER JOIN

tCommodityManagerGroup WITH (NOLOCK) ON tCommodityManagerSubGroup.CommodityManagerGroupID = tCommodityManagerGroup.CommodityManagerGroupID

LEFT OUTER JOIN

(

SELECT CommodityManagerSubGroupID, UserID AS CommLdrUserID

FROM tCommoditySubGroupTeamMember WITH (NOLOCK)

WHERE (CommodityTeamMemberTypeID = 2)

) AS CommLdr ON tSupplier.CommodityManagerSubGroupID = CommLdr.CommodityManagerSubGroupID

LEFT OUTER JOIN

(

SELECT

[CommodityManagerSubGroupID],

[tCommodityTeamMemberType].OrganizationID,

tCommoditySubGroupTeamMember.[UserID] as SubRegionalBuyerUserID

FROM

[tCommoditySubGroupTeamMember] WITH (NOLOCK)

INNER JOIN

[tCommodityTeamMemberType] ON [tCommoditySubGroupTeamMember].CommodityTeamMemberTypeID = tCommodityTeamMemberType.CommodityTeamMemberTypeID

WHERE

[tCommoditySubGroupTeamMember].[CommodityTeamMemberTypeID] in(4,5)

) AS SubRegionalBuyer

ON tCommodityManagerSubGroup.CommodityManagerSubGroupID = SubRegionalBuyer.CommodityManagerSubGroupID

AND tSupplierOrganization.OrganizationID = SubRegionalBuyer.OrganizationID

LEFT OUTER JOIN

(

SELECT

RepRowID,

ContractID,

pDec,

pAnn,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

[Dec],

IsNull(ExchangeRateDate,'') as ExchangeRateDate --SSR 80705 Change

FROM

dbo.fnInflationMonitorData(@AOPSnapshotID,'A',@BaseYear)

UNION ALL

SELECT

RepRowID,

ContractID,

pDec,

pAnn,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

[Dec],

IsNull(ExchangeRateDate,'') as ExchangeRateDate --SSR 80705 Change

FROM

dbo.fnInflationMonitorData(@CurrSnapshotID,'C',@BaseYear)

) AS RptData ON SnapshotContract.ContractID = RptData.ContractID

WHERE

(tSupplierOrganization.OrganizationID = @OrganizationID or @OrganizationID IS NULL)

AND (tContract.PlantID = @PlantID OR @PlantID IS NULL)

AND (tCommodityManagerSubGroup.CommodityManagerGroupID = @CommodityManagerGroupID OR @CommodityManagerGroupID IS NULL)

AND (tSupplier.CommodityManagerSubGroupID = @CommodityManagerSubGroupID OR @CommodityManagerSubGroupID IS NULL)

AND (CommLdr.CommLdrUserID = @CommLdrUserID OR @CommLdrUserID IS NULL)

AND (SubRegionalBuyer.SubRegionalBuyerUserID = @CommodityManagerUserID OR @CommodityManagerUserID IS NULL)

AND (tSupplier.SupplierCode Like @SupplierCode OR @SupplierCode IS NULL)

AND (tSupplier.SupplierName Like @SupplierName OR @SupplierName IS NULL)

AND (tContract.ContractNumber Like @ContractNumber OR @ContractNumber IS NULL)

AND (tSupplier.InflationMonitorTracked = @TrackedForInflation OR @TrackedForInflation IS NULL) --SSR 80863 Change - Filter the Records using InflactionMonitorTracked

AND (tSupplier.MaterialCostAgreeementExist = @MaterialCostAgreement OR @MaterialCostAgreement IS NULL) --SSR 81055 Change

AND tsupplier.IMTSupplier <> 1 --SSR 82026 Change

GROUP BY

RepRowID,ExchangeRateDate,SupplierName

) AS RepRowData ON RepRow.RepRowID = RepRowData.RepRowID

-- ORDER BY

-- RepRow.RepRowID

-- SSR 81911 Changes Begin

END

ELSE

BEGIN

-- SSR 81911 Changes End

SELECT

RepRowDesc,

ISNULL(pDec,0) AS pDec,

ISNULL(pAnn,0) AS pAnn,

ISNULL(Jan,0) AS Jan,

ISNULL(Feb,0) AS Feb,

ISNULL(Mar,0) AS Mar,

ISNULL(Apr,0) AS Apr,

ISNULL(May,0) AS May,

ISNULL(Jun,0) AS Jun,

ISNULL(Jul,0) AS Jul,

ISNULL(Aug,0) AS Aug,

ISNULL(Sep,0) AS Sep,

ISNULL(Oct,0) AS Oct,

ISNULL(Nov,0) AS Nov,

ISNULL([Dec],0) AS [Dec],

ISNULL(ExchangeRateDate,'') AS ExchangeRateDate, --SSR 80705 Change - Add a column for ExchangeRateDate

'' AS SupplierName ---SSR 81911 Change

FROM

( --SSR - 80698 Changes Begin

SELECT 1 AS RepRowID, 'AOP Volume' AS RepRowDesc

UNION ALL

SELECT 2, 'AOP Spend'

UNION ALL

SELECT 3, 'AOP Annual FC'

UNION ALL

SELECT 4, 'AOP Annual FX'

UNION ALL

SELECT 5, 'AOP Annual FO'

UNION ALL

SELECT 6, 'AOP Annual FR'

UNION ALL

SELECT 7, 'AOP Fiscal FC'

UNION ALL

SELECT 8, 'AOP Fiscal FX'

UNION ALL

SELECT 9, 'AOP Fiscal FO'

UNION ALL

SELECT 10, 'AOP Fiscal FR'

UNION ALL

SELECT 11, 'Curr Volume'

UNION ALL

SELECT 12, 'Curr Spend'

UNION ALL

SELECT 13, 'Curr Annual FC'

UNION ALL

SELECT 14, 'Curr Annual FX'

UNION ALL

SELECT 15, 'Curr Annual FO'

UNION ALL

SELECT 16, 'Curr Annual FR'

UNION ALL

SELECT 21, 'Backdated MPCAs Curr Annual'

UNION ALL

SELECT 17, 'Curr Fiscal FC'

UNION ALL

SELECT 18, 'Curr Fiscal FX'

UNION ALL

SELECT 19, 'Curr Fiscal FO'

UNION ALL

SELECT 20, 'Curr Fiscal FR'

--SSR - 82281 Changes Begin

UNION ALL

SELECT 22, 'Backdated MPCAs Curr Fiscal'

--SSR - 82281 Changes End

) AS RepRow --SSR - 80698 Changes End

LEFT OUTER JOIN

(

SELECT

RepRowID,

SUM(pDec) as pDec,

SUM(pAnn) as pAnn,

SUM(Jan) as Jan,

SUM(Feb) as Feb,

SUM(Mar) as Mar,

SUM(Apr) as Apr,

SUM(May) as May,

SUM(Jun) as Jun,

SUM(Jul) as Jul,

SUM(Aug) as Aug,

SUM(Sep) as Sep,

SUM(Oct) as Oct,

SUM(Nov) as Nov,

SUM([Dec]) as [Dec],

ExchangeRateDate --SSR 80705 Change