Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ksr39
Posting Yak Master
193 Posts |
 Posted - 2012-02-14 : 14:38:16
|
| Hi All,Please suggest me aboutThe stored procedure sp_extract_XXX in the XXX database (XXX\YYYY) is having performance issues. This procedure runs in a DTSX in our overnight process. Since we implemented the XXX database in SQL Server 2008 this procedure has been running longer and longer. It is now running for about 5 hours. To help me diagnose the problem I made a copy of the procedure sp_extract_XXXTEST and the table it stores data in tblXXXTEST. When I ran my test procedure and table it ran in about 30 minutes. So I figured it was something wrong with the existing table or procedure.First I tried updating statistics on tbXXX and that did nothing to shorten the time. I then renamed the original table and renamed my test table to the original name. That did nothing as well.Next I tried to recompile the original stored procedure, no change. I then renamed the original procedure and renamed my test procedure to the original name. That did nothing.I'm out of ideas now. Does anyone have any ideas what the issue is and how I can fix it?Thank's in advance.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-14 : 14:54:41
|
| did you try running original procedure itself directly in sql management studio rather than through dtsx package? ALso is it pulling data from same db or is it using some distributed quries like OPENROWSET,linked server etc?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ksr39
Posting Yak Master
193 Posts |
Posted - 2012-02-14 : 15:13:32
|
| No I hadn't tried running the original in sql management studio. I'm doing that right now.All the data in this procedure comes from the same database on the same server. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-14 : 15:20:49
|
| ok...let us know whether it was any better?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ksr39
Posting Yak Master
193 Posts |
Posted - 2012-02-14 : 15:58:34
|
| It ran in about 17 minutes. So any idea why it runs so slow when executed from a DTSX? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-14 : 16:03:21
|
| how much data is it dealing with?did you check execution plan for procedure?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ksr39
Posting Yak Master
193 Posts |
Posted - 2012-02-14 : 16:56:17
|
| It's less than 30,000 rows.I check the execution plan it cost 100% |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-14 : 17:06:36
|
that will be 100%i was asking you to check costly steps inside that  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ksr39
Posting Yak Master
193 Posts |
Posted - 2012-02-15 : 07:49:08
|
| Sorry for the late reply,I'm not able to get an execution plan because the stored procedure creates a temp table. |
|
|
|
|
|
|
Stored procedure