Very long running stored procedure

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Very long running stored procedure

Author

Topic

Taragor
Starting Member

46 Posts

Posted - 2013-10-30 : 09:28:27

Hi All,

I'm wondering if someone may be able to shed some light on an issue I'm having.

I've recently migrated one of our db's from SQL 2000 to SQL 2008. I have a set of stored procedures that run on this db on a monthly basis. Now on SQL 2000 (which is running on an older server with less cpu power and half the memory) 3 of the stored procedures run in under 1 min.

The problem is those same 3 procedures run from 3hrs for 1 to over 10hrs each for the other 2 on the new server.

(There are a total of 73 procedures that run during the monthly production run, all procedures run faster on the new server except for the 3 problematic ones)

Here is one of the problematic procedures

---
insert into tblProcessedSpecRecords (mrn, accessionnumber)
select distinct mrn, accessionnumber
from tblCreateBioSpecRecords
where mrn + accessionnumber not in
(select mrn + accessionnumber
from tblProcessedSpecRecords)
---

Neither table is indexed on the old or new server (Will be eventually just hadn't done it during testing phase)

Can anyone explain to me where the issue lies?

Sincerely,

Tar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 09:55:57

Try updating statistics on new server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-30 : 11:03:45

Besides the update statistics -
If you shrunk the database files you may have some extreme index fragmentation so check for that.

also, because it is a new server:

compare the output from this between the two servers. Not that they should be identical but differences could point to you a potential setup problems.


sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure

Be One with the Optimizer
TG

Taragor
Starting Member

46 Posts

Posted - 2013-10-30 : 14:27:15

Visakh: I tried updating the statistics on the table and on the whole DB no change.

TG: I can try running a shrink before I leave work tonight (the DB is over 100GB so it takes quite a long time). Will this help as there are no indexes on the table?

I also ran the sp_configure and both db's are relatively identical with 2008 showing a few more statistics types than 2000.

UPDATE: Ok I tried running just the select statement without the insert and the select statement appears to be the issue.

Tar

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-30 : 14:58:36

I am NOT suggesting that you shrink the databases.

The things to compare is not the list of options but the [config_value] values.
Things like:
max degree of parallelism
max server memory (MB)

If it is just the 3 procs that are slower than perhaps it is worth rewriting them.
Perhaps this will perform better on the new version:


select bs.mrn, bs.accessionnumber
from   tblCreateBioSpecRecords bs
left outer join tblProcessedSpecRecords ps
       on ps.mrn = bs.mrn
       and ps.accessionnumber = bs.accessionnumber
where  ps.mrn is null
group by bs.mrn, bs.accessionnumber

Be One with the Optimizer
TG

Taragor
Starting Member

46 Posts

Posted - 2013-10-30 : 15:33:06

TG: Ok I changed all 3 procedures to use joins rather than the not ins and they are running normally once again. Thanks for the reminder. Being self taught for some reason I got used to using in / not in and forego using the joins.

On a side note though, why would SQL 2008 react so differently to SQL 2000 because of this? I mean I can understand a slight change in time but 14hours?

Tar

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-30 : 15:54:07

Nothing wrong with self taught. Glad its working better.

A bad execution plan with a lot of data can be devastating to performance and the server can get totally bogged down. There are always going to be differences between sql versions. One of the risks of upgrading.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources