Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Sp runs from 7 sec to 10 min (UnitPricingTrans) Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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?
Post #1511159
Posted Monday, November 4, 2013 9:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,110, Visits: 11,940
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 Moden's 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)
Post #1511163
Posted Monday, November 4, 2013 9:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
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.
Post #1511167
Posted Monday, November 4, 2013 9:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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
Post #1511171
Posted Monday, November 4, 2013 9:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,110, Visits: 11,940
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 Moden's 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)
Post #1511175
Posted Monday, November 4, 2013 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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.





  Post Attachments 
prj_detail_indexes.xls (0 views, 24.00 KB)
Post #1511176
Posted Monday, November 4, 2013 10:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
I am attaching "UnitPricingTrans.sqlplan" file

  Post Attachments 
UnitPricingTrans.sqlplan (13 views, 155.57 KB)
Post #1511192
Posted Monday, November 4, 2013 10:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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.
Post #1511195
Posted Monday, November 4, 2013 2:15 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
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

Post #1511300
Posted Monday, November 4, 2013 2:39 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,970, Visits: 2,909
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1511308
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse