November 4, 2013 at 7:46 am
I compiled a stored procedure (UnitPricingTrans) 3 days ago.
The first execution took 10 min.
When I ran it second time it took 7 sec.
All other runs on that day took from 7 to 11 seconds.
Today I ran exactly the same procedure.
It returns exactly the same number of rows (75).
But it took 4 min.
SQL Server was last restarted on October 16th.
Today is November 4th.
This is development server. Not too busy.
I wonder why I have such huge inconsistency in terms of execution times.
Thanks,
Robert
November 4, 2013 at 7:52 am
RVO (11/4/2013)
I compiled a stored procedure (UnitPricingTrans) 3 days ago.The first execution took 10 min.
When I ran it second time it took 7 sec.
All other runs on that day took from 7 to 11 seconds.
Today I ran exactly the same procedure.
It returns exactly the same number of rows (75).
But it took 4 min.
SQL Server was last restarted on October 16th.
Today is November 4th.
This is development server. Not too busy.
I wonder why I have such huge inconsistency in terms of execution times.
Thanks,
Robert
Not much in the way of details here. Are the stats current? Are you passing parameters to your proc, if so it might be parameter sniffing.
_______________________________________________________________
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 4, 2013 at 8:00 am
Thank you for a quick response.
This is how I call procedure:
EXEC UnitPricingTrans
@P_PER_START = 201308,
@P_PER_END = 201309,
@P_CO_CODE = '7001',
@P_UNIT_TYPE = 'VEH',
@P_UNIT = 'AUSMV'
And I always run it with the same parameters.
What is parameter sniffing?
November 4, 2013 at 8:02 am
RVO (11/4/2013)
Thank you for a quick response.This is how I call procedure:
EXEC UnitPricingTrans@P_PER_START = 201308,
@P_PER_END = 201309,
@P_CO_CODE = '7001',
@P_UNIT_TYPE = 'VEH',
@P_UNIT = 'AUSMV'
And I always run it with the same parameters.
What is parameter sniffing?
Gail has an excellent article about parameter sniffing here. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
_______________________________________________________________
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 4, 2013 at 8:03 am
This is procedure code.
The biggest table is prj_detail ( 53,000,000 records)
ALTER PROCEDURE UnitPricingTrans
(
@P_PER_START int,
@P_PER_END int,
@P_CO_CODE varchar(1000),
@P_UNIT_TYPE varchar(1000),
@P_UNIT varchar(1000)
)
AS
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#UnitType')) DROP TABLE #UnitType;
DECLARE @XML XML
SET @XML = '<list><i>' + Replace(@P_UNIT_TYPE, ',', '</i><i>') + '</i></list>'
SELECT x.y.value('.', 'varchar(1000)') AS Value INTO #UnitType FROM @XML.nodes('list/i/text()') AS x(y)
--select Value from #UnitType
--return
select
prj_detail.accounting_period,
prj_detail.co_code,
prj_detail.acct_currency_code,
equip_type.equip_type_code + ' - ' + equip_type.equip_type_name as UnitType,
equip_code.equip_code + ' - ' + equip_code.equip_name as Unit,
prj_info.prj_code + ' - ' + prj_info.prj_name as Project,
equip_code.equip_code + ' - ' + equip_code.equip_name as Equipment,
prj_detail.qty_amt as Quantity,
measure_code.measure_name as Measure,
prj_detail.goal_multiplier as Multiplier,
up_sched.up_sched_name as UPSchedule,
prj_detail.cost_cc AS Cost,
prj_detail.eff_cc AS Effort,
prj_detail.prj_code,
per_end_date
from
equip_type
inner join
equip_code
on equip_type.equip_type_code = equip_code.equip_type_code
inner join
prj_detail
on equip_code.equip_code = prj_detail.equip_code
inner join
doc_type
on prj_detail.sys_doc_type_code = doc_type.sys_doc_type_code
inner join
prj_info
on prj_detail.prj_code = prj_info.prj_code
inner join
unit_code
on prj_detail.prj_detail_code = unit_code.unit_code
inner join
measure_code
on unit_code.measure_code = measure_code.measure_code
inner join
up_sched_units
on unit_code.unit_code = up_sched_units.unit_code
inner join
up_sched
on up_sched_units.up_sched_code = up_sched.up_sched_code
where
doc_type.sys_doc_type_code = 'UP'
and
prj_detail.co_code IN (@P_CO_CODE)
and
equip_type.equip_type_code IN (select Value from #UnitType)
and
equip_code.equip_code IN (@P_UNIT)
and
accounting_period between @P_PER_START and @P_PER_END
November 4, 2013 at 8:10 am
While not huge, 53 million rows isn't exactly a small table. While it certainly could be parameter sniffing, it could also be that the data was driven out of memory by other requests and needed to be reloaded. That's probably why it took 10 minutes to run the first time and just seconds to run the second (for example).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2013 at 8:50 am
Guys, I am so thankful for your help.
I think parameter sniffing is definitely might be the root cause.
I am rewriting procedure now to use local variables.
Will let you know on the outcome results soon.
November 4, 2013 at 9:02 am
RVO (11/4/2013)
Guys, I am so thankful for your help.I think parameter sniffing is definitely might be the root cause.
I am rewriting procedure now to use local variables.
Will let you know on the outcome results soon.
Please be aware that symptoms might look like parameter sniffing but most likely it isn't. Parameter sniffing would be my last option to look at.
Your sproc takes 7 secs to load for every consecutive run since the first one because most likely data and plan is cached. Have you looked at indices and stats before changing the code?
November 4, 2013 at 9:07 am
SSChampion,
I re-compiled procedure using local variables.
But... first run with ( @P_UNIT_TYPE = 'VEH' )
took 11 seconds.
Next run ( @P_UNIT_TYPE = 'COM' )
is taking more than 2 min... and still running.
Still parameter sniffing occurring?...
November 4, 2013 at 9:10 am
SSChampion,
How do I get execution plan in XML?
I would like to see what Gail is talking about in her article about parameter sniffing.
Especially this part - ColumnReference Column="@StringVar" ParameterCompiledValue="'zzz'"
November 4, 2013 at 9:13 am
Ten Centuries,
"...Have you looked at indices and stats before changing the code?..."
No I have not done this.
I am not a big guru when it comes to TSQL performance tuning.
Can you tell how to do that and how to analyze that data?
November 4, 2013 at 9:20 am
RVO (11/4/2013)
SSChampion,I re-compiled procedure using local variables.
But... first run with ( @P_UNIT_TYPE = 'VEH' )
took 11 seconds.
Next run ( @P_UNIT_TYPE = 'COM' )
is taking more than 2 min... and still running.
Still parameter sniffing occurring?...
Probably wasn't to begin with. It was a shot in the dark because you hadn't posted any real details yet. We need some details here before we can offer any real advice.
This article explains the types of information we need to see in order to help. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
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 4, 2013 at 9:25 am
RVO (11/4/2013)
Ten Centuries,
"...Have you looked at indices and stats before changing the code?..."No I have not done this.
I am not a big guru when it comes to TSQL performance tuning.
Can you tell how to do that and how to analyze that data?
Execute your query with actual exec plan option enabled, save the plan and post it here.
November 4, 2013 at 9:26 am
Ten Centuries,
Is this something you're looking for?
I used sys.dm_exec_procedure_stats
object_id: 965578478
database_id: 55
proc name: UnitPricingTrans
cached_time: 2013-11-04 07:53:43.937
last_execution_time: 2013-11-04 08:08:29.953
total_elapsed_time: 541171953
avg_elapsed_time: 700999
last_elapsed_time: 161245222
execution_count: 772
November 4, 2013 at 9:38 am
RVO (11/4/2013)
Ten Centuries,Is this something you're looking for?
I used sys.dm_exec_procedure_stats
object_id: 965578478
database_id: 55
proc name: UnitPricingTrans
cached_time: 2013-11-04 07:53:43.937
last_execution_time: 2013-11-04 08:08:29.953
total_elapsed_time: 541171953
avg_elapsed_time: 700999
last_elapsed_time: 161245222
execution_count: 772
No, post the actual execution plan.
_______________________________________________________________
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 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply