SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sp runs from 7 sec to 10 min (UnitPricingTrans)


Sp runs from 7 sec to 10 min (UnitPricingTrans)

Author
Message
RVO
RVO
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 517
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26506 Visits: 17557
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/

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2972 Visits: 3637
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.
RVO
RVO
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 517
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26506 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
RVO
RVO
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 517
Looks like it was cached with @P_UNIT_TYPE = 'VEH' (runs 7 seconds).
With all other parameter values it runs 2.5 minutes now.
I tried "WITH RECOMPILE" to clear cache but nothing changed.
7 seconds with 'VEH' - 2.5 min with all other values.

I attached all indexes from [prj_detail] table.
Attachments
prj_detail_indexes.xls (3 views, 24.00 KB)
RVO
RVO
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 517
I am attaching "UnitPricingTrans.sqlplan" file
Attachments
UnitPricingTrans.sqlplan (23 views, 155.00 KB)
RVO
RVO
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 517
I compared execution plans
with parameter 'VEH' and 'COM'
and in both cases it is this step that is taking up all the resources:

Index Scan (NonClustered)
[prj_detail].[IXN_prj_detail__per_end_date__detail_type_ind__evc_code__evc_type_code]
Cost: 91%


Another pattern I noticed is that execution time heavily depends on the number on records returned.
It runs from 2 to 12 seconds when 5-100 records returned.
It runs 2.5 minutes when 7,000 are being returned.
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2972 Visits: 3637
RVO (11/4/2013)
I compared execution plans
with parameter 'VEH' and 'COM'
and in both cases it is this step that is taking up all the resources:

Index Scan (NonClustered)
[prj_detail].[IXN_prj_detail__per_end_date__detail_type_ind__evc_code__evc_type_code]
Cost: 91%


Another pattern I noticed is that execution time heavily depends on the number on records returned.
It runs from 2 to 12 seconds when 5-100 records returned.
It runs 2.5 minutes when 7,000 are being returned.


Looks like stale statistics is one of the issues, you are fetching 1million records and query is estimating only 1 record. try updating the stats with fullscan if possible.

There is also a missing index, try to create a covering index with all the columns in the query
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8070 Visits: 7163
Please post the DDL, including all indexes, for the "prj_detail" table.

If the clustered index is on an identity column, you should almost certainly change it to "accounting_period" instead. This is especially true if, as is extremely likely, you (almost) always specify an accounting_period / period range in your queries on this table.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search