November 19, 2014 at 2:32 pm
I am wondering what the difference is between essentially the same code running in a query editor window vs. calling the stored procedure that uses the same code in a query window. I'm suspecting I have a tempdb or an available memory issue.
I am testing a stored procedure task that runs every day from a query window. It will not complete in 10 minutes - same failed result as the scheduled task.
Extract the code, remove return statements, and modify it to run as straight T-SQL, with all the same variables and values, and it runs in the normal time frame (seconds). This code has been in place for nine years, through a couple of SQL migrations. Its a profit report for past 12 months, and 4 quarters.
Change the SQL proc to select TOP 50 instead of all 4500 records, and it completes, though a bit slower than the the full T-SQL version. This is all being run on the SQL box.
I've tried the Recompile option for the procedure without benefit.
I'm wondering if it's a memory issue on the server. It's reporting 97% used physical memory almost all to Sql Server (8GB total).
I'll restart the SQL box tonight, but any suggestions or links to helpful articles would be appreciated.
November 19, 2014 at 3:01 pm
notifications 61467 (11/19/2014)
I am wondering what the difference is between essentially the same code running in a query editor window vs. calling the stored procedure that uses the same code in a query window. I'm suspecting I have a tempdb or an available memory issue.I am testing a stored procedure task that runs every day from a query window. It will not complete in 10 minutes - same failed result as the scheduled task.
Extract the code, remove return statements, and modify it to run as straight T-SQL, with all the same variables and values, and it runs in the normal time frame (seconds). This code has been in place for nine years, through a couple of SQL migrations. Its a profit report for past 12 months, and 4 quarters.
Change the SQL proc to select TOP 50 instead of all 4500 records, and it completes, though a bit slower than the the full T-SQL version. This is all being run on the SQL box.
I've tried the Recompile option for the procedure without benefit.
I'm wondering if it's a memory issue on the server. It's reporting 97% used physical memory almost all to Sql Server (8GB total).
I'll restart the SQL box tonight, but any suggestions or links to helpful articles would be appreciated.
Hi and welcome to the forums. We would at the very least need to see the code. Table definitions and indexes along with the actual execution plans would be very useful too.
Could be stale statistics, index fragmentation or a host of other things.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2014 at 4:12 pm
Sean,
Thanks for your reply.
I located a suggestion for the problem I was experiencing:
I substituted a local variable for the stored procedure parameter used in multiple where clauses.
The SP now completes in 5 seconds.
During testing with the old embedded parameter, and changing the final insert statement to a select, I could watch the rows slowly returned from the procedure.
I'm running tests against the tables in the procedure to determine their state. The slow down is a recent occurrence so something has changed.
Updated proc:
Create Proc [dbo].[spMerchantProfitHistorySum_test]
@dRunDate datetime = NULL
As
SET NOCOUNT ON
-- Add local variable to avoid slow sproc execution
Declare @dLocalRunDate datetime
set @dLocalRunDate = @dRunDate ;
-- Validate the @RunDate parameter.
IF @dLocalRunDate IS NULL
BEGIN
Select @dLocalRunDate = [Settingvalue] from XXXXXXXSettingstbl Where [Setting] = 'LastClosedPeriod'
END
IF @dLocalRunDate IS NULL
BEGIN
raiserror('Unable to supply value for @dLocalRunDate from XXXXXXXSettingstbl',16,10)
RETURN 0
END
-- Get first day of month to tie to period history table format Mon/1/YYYY
Set @dLocalRunDate = DateAdd(d,-Datepart(d,@dLocalRunDate) + 1 ,@dLocalRunDate)
Declare @dStartingDate datetime,-- starting date for lookback period
@dStartingLookback datetime,-- starting date for last lookback period
@dEndingLookback datetime,-- ending lookback period
@dStartingQuarter datetime,-- starting quarter date
@iLookbackPeriods int-- number of periods to lookback
Select @dStartingdate = DateAdd(m,-11,@dLocalRunDate)-- get last twelve periods inclusive
-- Get number of periods for lookback range
Select @iLookBackPeriods = NULL
Select @iLookBackPeriods = cast([SettingValue] as int) from XXXXXXXSettingstbl where [Setting] = 'ProfitLookBackPeriod'
If @iLookBackPeriods IS NULL
Begin
Raiserror('Lookback period not defined in settings table,',16,1)
return 0
End
Else
-- Get lookback starting date and last full year starting dates
-- Using six closed periods from 2 to 7 periods prior to last closed period
Select @dEndingLookback = [Settingvalue] from XXXXXXXSettingstbl Where [Setting] = 'LastClosedPeriod'
Select @dEndingLookback = DateAdd(m,-1,@dEndingLookback)
Select@dStartingLookback = DateAdd(m,1 -@iLookbackPeriods,@dEndingLookback) -- inclusive lookback periods
-- next two lines for testing only
--Select @dStartingLookback [startinglookback],@dEndingLookback [EndingLookback], @dStartingdate [StartingDate]
--Return
-- Get first day of last years next quarter - starting quarter
Declare @year int
Select @Year = datepart(yyyy,@dStartingDate)
Select @dStartingQuarter = Case Datepart(q,@dLocalRunDate)
when 1 then Cast('4/1/' + cast(@year as varchar(4)) as Datetime)
when 2 then Cast('6/1/' + cast(@year as varchar(4)) as Datetime)
when 3 then Cast('9/1/' + cast(@year as varchar(4)) as Datetime)
when 4 then Cast('1/1/' + cast(@year as varchar(4)) as Datetime)
end
-- next line will fail if users in table
-- Truncate Table MerchantProfitSumtbl
delete from MerchantProfitSumtbl
if @@error <> 0 return 0
Insert Into MerchantProfitSumtbl
Select
tLTD.[Merchno], tLTD.[LTD],
tLookBack.[LBAVG],
tMonths.JAN,tMonths.FEB,tMonths.MAR,tMonths.APR,tMonths.MAY,tMonths.JUN,tMonths.JUL,tMonths.AUG,tMonths.SEP,tMonths.OCT,tMonths.NOV,tMonths.[Dec],
tQTRS.[QTR1],tQTRS.[QTR2],tQTRS.[QTR3],tQTRS.[QTR4]
from
-- get data from calculated starting date
-- into columns for last four quarters
(SELECT p.Merchno, -- along with LTD
SUM(CASE Quarter WHEN 1 THEN p.GrossMargin ELSE 0 END) AS 'QTR1',
SUM(CASE Quarter WHEN 2 THEN p.GrossMargin ELSE 0 END) AS 'QTR2',
SUM(CASE Quarter WHEN 3 THEN p.GrossMargin ELSE 0 END) AS 'QTR3',
SUM(CASE Quarter WHEN 4 THEN p.GrossMargin ELSE 0 END) AS 'QTR4'
FROM MerchantProfitHistorytbl p Join Merchanttbl m on
p.Merchno = m.merchno Join luCalendarDatetbl d on
d.CalendarDate = p.Period
Where p.Period >= @dstartingdate and p.Period <= @dLocalRunDate
Group By p.Merchno
) tQTRS
Join (-- along with LTD
Select Merchno,SUM(GrossMargin) as LTD from MerchantProfitHistorytbl Group BY Merchno)
as tLTD(Merchno, LTD)
on tQTRS.Merchno = tLTD.Merchno
Join (
SELECT p.Merchno, -- along with last twelve months
SUM(CASE Month([Period]) WHEN 1 THEN p.GrossMargin ELSE NULL END) AS 'JAN',
SUM(CASE Month([Period]) WHEN 2 THEN p.GrossMargin ELSE NULL END) AS 'FEB',
SUM(CASE Month([Period]) WHEN 3 THEN p.GrossMargin ELSE NULL END) AS 'MAR',
SUM(CASE Month([Period]) WHEN 4 THEN p.GrossMargin ELSE NULL END) AS 'APR',
SUM(CASE Month([Period]) WHEN 5 THEN p.GrossMargin ELSE NULL END) AS 'MAY',
SUM(CASE Month([Period]) WHEN 6 THEN p.GrossMargin ELSE NULL END) AS 'JUN',
SUM(CASE Month([Period]) WHEN 7 THEN p.GrossMargin ELSE NULL END) AS 'JUL',
SUM(CASE Month([Period]) WHEN 8 THEN p.GrossMargin ELSE NULL END) AS 'AUG',
SUM(CASE Month([Period]) WHEN 9 THEN p.GrossMargin ELSE NULL END) AS 'SEP',
SUM(CASE Month([Period]) WHEN 10 THEN p.GrossMargin ELSE NULL END) AS 'OCT',
SUM(CASE Month([Period]) WHEN 11 THEN p.GrossMargin ELSE NULL END) AS 'NOV',
SUM(CASE Month([Period]) WHEN 12 THEN p.GrossMargin ELSE NULL END) AS 'DEC'
--SUM(CASE When ([Period]) >= @dStartingLookback THEN p.GrossMargin ELSE 0 END) AS 'LookBack'
--AVG( p.GrossMargin) AS 'LookBack'
FROM MerchantProfitHistorytbl p
Where p.Period >= @dstartingdate and p.Period <= @dLocalRunDate
Group By p.Merchno
) tMonths
on tMonths.Merchno = tLTD.Merchno
Join
(
Select Merchno, AVG(p.GrossMargin) LBAVG
FROM MerchantProfitHistorytbl p
Where p.Period >= @dstartinglookback and p.Period <= @dEndingLookback
Group By p.Merchno
) tLookBack(Merchno,LBAVG)
on tMonths.merchno = tLookBack.Merchno
Order by tLTD.[Merchno] ;
if @@error <> 0 return 0 else return 1
GO
declare @iRet int
exec @iREt = [spMerchantProfitHistorySum_test] -- with recompile
Select @iRet ;
go
Select * from MerchantProfitSumtbl
November 19, 2014 at 4:25 pm
notifications 61467 (11/19/2014)
Sean,Thanks for your reply.
I located a suggestion for the problem I was experiencing:
I substituted a local variable for the stored procedure parameter used in multiple where clauses.
The SP now completes in 5 seconds.
This means you were experiencing bad parameter sniffing.
If you want to read further and understand this issue further the following set of 3 articles is a must read on this topic.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
[/url]
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/[/url]
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/[/url]
Glad you were able to resolve your issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply