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»»»

Stored procedure very slow execution Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 2:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:49 AM
Points: 11, Visits: 93
Hello,

Please help me, I'm desperate.

I have an old SQL2000 (SP4) - and no, I can't upgrade it, is in my customer server - on which I have a very, very strange issue which started on 01/01/2013. Until then, this problem never appeared.

I have an data export stored procedure, which fill a temporary table from a quite complicated select (multiple subqueries, with data cast, sums and so on) and export the final content in another table in a specific format. If I execute this s/p, either from calling application code, or from Query Analyzer (exec ...), it take very much time to execute (1.5 - 2 hours). If I check the status with sp_who2 I see that the process appears to be blocked by itself in the "INSERT INTO" temporary table. But if I directly execute the s/p code from Query Analyzer, the result come almost instantly (5-10 seconds).

I tried to simplify the main query, to remove some subqueries and sums, no result 'till now.

Does anybody faced this problem? Is there any solution, except re-writing the whole s/p from scratch?

Interested fact: I have a similar (not exactly the same, but same kind) of stored procedure, which retrieve from exact same tables similar data in a similar way for a report, and this is working fine...

Thanks in advance for any hint that may get me out of this problem.


__________________________________
Is better to look stupid than to be stupid
Post #1411536
Posted Friday, January 25, 2013 4:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
Radu Costescu (1/25/2013)

If I execute this s/p, either from calling application code, or from Query Analyzer (exec ...), it take very much time to execute (1.5 - 2 hours).


But if I directly execute the s/p code from Query Analyzer, the result come almost instantly (5-10 seconds).


What exactly are you checking from query analyzer ? Running the procedure with parameters or the code inside the procedure ?

If the code inside the procedure is running perfectly and procedure not, then try recompiling the procedure and check the result.
Post #1411602
Posted Friday, January 25, 2013 4:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:49 AM
Points: 11, Visits: 93
sqlnaive (1/25/2013)
What exactly are you checking from query analyzer ? Running the procedure with parameters or the code inside the procedure ?

I tried both. Running the procedure with parameters (exec sp_name 'yyyy-mm-dd','xxxxxx') result in slow execution, executing the code itself (declare @P1 datetime, @P2 varchar(10) set @P1='yyyy-mm-dd' set @P2='xxxxxx' select ... etc) give immediate results.

sqlnaive (1/25/2013)
If the code inside the procedure is running perfectly and procedure not, then try recompiling the procedure and check the result.

I tried to run exec sp_name 'yyyy-mm-dd','xxxxxx' with recompile (after I dropped the s/p and re-create it, to be sure) but unfortunately still no improvements


__________________________________
Is better to look stupid than to be stupid
Post #1411611
Posted Friday, January 25, 2013 5:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 953, Visits: 2,626
Sounds like a badplan query plan or a case of Paramater sniffing not selecting the optimal plan for the paramaters.

Theres a simple way to test if its parameter sniffing, create internal paramaters in the SP and use those in the query.

Eg

CREATE PROCEDURE SP_NAME 
@P1 Datetime
,@P2Varchar(100)
AS

DECLARE @Date DateTIme=@Date
DECLARE @Text Varchar(100)=@text

Select *
from
Table
Where Col1=@Date
and Col2=@Text



Hope that give you the jist, as a quick fix in 2008 you could also issue a hint with the OPTION(RECOMPILE) at the end of the query, HOWEVER this short term fix in order to give you breathing space to put something more robust in place.

Sorry the above strike through is 2008 specific just noticed you're on SQL 2000.

I would suggest reading Gail's blogs on Paramater sniffing http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

and Catch all queries http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

hope these help.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1411624
Posted Friday, January 25, 2013 5:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
I agree. It sounds like parameter sniffing is leading to issues. I would suggest updating the statistics on the tables being referenced. In addition to the solutions for parameter sniffing, just getting better statistics frequently solves the issue.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1411638
Posted Friday, January 25, 2013 5:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?
Post #1411649
Posted Friday, January 25, 2013 6:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:49 AM
Points: 11, Visits: 93
Grant Fritchey (1/25/2013)
I agree. It sounds like parameter sniffing is leading to issues. I would suggest updating the statistics on the tables being referenced. In addition to the solutions for parameter sniffing, just getting better statistics frequently solves the issue.

I ruled out the parameter sniffing possibility, while within the code I have no such condition (@P1='yyyy-mm-dd' or @P1 is null). I will take the second suggested path, the update of the statistics (involved tables are really big and the customer does not care very much to maintain the database) and inform you.

Thank you all for the contributions and interest.


__________________________________
Is better to look stupid than to be stupid
Post #1411652
Posted Friday, January 25, 2013 6:31 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 4,611, Visits: 4,067
That last post makes me think of indexes. Since you changed the code to make things simpler and the database isn't maintained, check to see if the procedure is using any of your indexes. If, say, a change resulted in a full table scan, that would explain the performance degradation. It could be that a single covering index isn't being used any more. If it is using the indexes properly, then definitely go with updating the statistics. Just food for thought.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1411671
Posted Friday, January 25, 2013 6:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:49 AM
Points: 11, Visits: 93
sqlnaive (1/25/2013)
Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?

OK, now I will have to admit my lack of knowledge in SQL Server administration, but is better to look stupid than to be stupid. So, admitting that the customer never updated the statistics for that database (which is a large one, and the tables involved are the three largest within it), can I do the update statistics for these tables in live (production) environment without jeopardizing the user activity? Or I need special conditions (like DB in Single User mode or stuff)? Thanks again.


__________________________________
Is better to look stupid than to be stupid
Post #1411673
Posted Friday, January 25, 2013 7:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 953, Visits: 2,626
You can do a targeted Update Statistics on single tables, the BoL should help lookup the UPDATE STATISTICS TSQL page.

You can I beleive run an EXEC sp_updateStats in line as well.

however I would also suggest that a Rebuild of indexes be run on a weekly basis during quiet times, 12-5am local is generally a good window.

There are also targeted index rebuild scripts available on line that will hit the worst offenders on a nightly basis (say over 60% fragmentation).

You can rebuild Nonclustered Indexes on the fly but it will impact perfomance a little using something like

ALTER INDEX <index> ON <Table> REBUILD (WITH ONLINE=ON)

(check the syntax in BOL).

I wouldnt recommend doing this with a clustered index though.



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1411701
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse