SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Very long running stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Taragor
Starting Member

46 Posts

Posted - 10/30/2013 :  09:28:27  Show Profile  Reply with Quote

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

India
52325 Posts

Posted - 10/30/2013 :  09:55:57  Show Profile  Reply with Quote
Try updating statistics on new server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/30/2013 :  11:03:45  Show Profile  Reply with Quote
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
Go to Top of Page

Taragor
Starting Member

46 Posts

Posted - 10/30/2013 :  14:27:15  Show Profile  Reply with Quote
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

Edited by - Taragor on 10/30/2013 14:41:17
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/30/2013 :  14:58:36  Show Profile  Reply with Quote
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
Go to Top of Page

Taragor
Starting Member

46 Posts

Posted - 10/30/2013 :  15:33:06  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/30/2013 :  15:54:07  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000