stored procedure timing out

Question

Post reply

stored procedure timing out

kavita shende

Say Hey Kid

Points: 713
More actions
July 19, 2006 at 10:39 am

#91666

Hi,

I have a stored procedure. When this stored procedure was run in production database, it ran for 15 minutes. I have restored the copy of the backup of the same production database (exact copy of production) on my desktop. When I ran this stored procedure on this instance it took 15 hours.
Nothing has been changed as far as data is concern. I checked the indexes. They are also same as production. What else should I check for? Why is it taking so long?

Thanks i nadvance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Hopefully your Production Hardware is better than your desktop

* Noel

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

Try:

EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''

Andy

kavita shende Say Hey Kid Points: 713 More actions · Answer 3

What is the purpose of this statement?

EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''

Thanks

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

If hardware is similar then you may need to do a little maintenance. It may alleviate your performance disparity.

Here's my 'short list' for tuning:

Round I

DBCC UPDATEUSAGE

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round II

DBCC DBREINDEX

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round III

Profiler

Query Hints

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

barsuk SSChampion Points: 12280 More actions · Answer 5

check/compare Estimated Execution Plan on both Prod and Local box...

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 6

That big a discrepancy and that way around, I immediately would think of lack of physical memory or, perhaps, an anti-virus or other process on the desktop interfering.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 7

Noeld probably hit the nail on the head... the server should be much faster than your desktop machine for multiple reasons...

1. Memory... chances are your desktop computer has between 512Mb and 2GB... your server could easily have between 8 and 64Gb.

2. Disk system... chances are the disk system on the server is a san or fast wide scsi system with a gazillion more r/w heads than your desktop system... your desktop is most likely a limited bandwidth IDE.

3. Setup... there could be a lot of differences but chances are, your server has some pre-sized DBs with logs that have already been grown. This includes TempDB which is very important to performance. Chances are, your DBA pregrows it to something between 8 and 16GB (or the system grew it and hasn't been rebooted in a while). Your desktop system is probably setup to start at 1Mb with 10% growth which takes something like 73 growths to make anything really useable in TempDB. Your machine will freeze during each growth. And then, you have a horribly fragmented disk and database which also affect performance.

4. Cpu's... chances are, your server has between 4 and 16 cpus. Your desktop computer probably only has one or maybe two. Your desktop computer has no chance at performing parallel tasks.

You're comparing grape seeds to coconuts... Why do you think severs cost so much?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)