stored procedure running slow

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » stored procedure running slow

stored procedure running slow

Rate Topic

Display Mode

Topic Options

Author

Message

karthik srinivasan

Posted Friday, May 04, 2012 9:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 1:11 AM
Points: 9, Visits: 21

Hi

My development and test databases are hosted on the same box. A stored procedure that runs for about 10 minutes in development database is running for about 4 hours in the test database.

Could you tell me how to root cause this performance issue and fix?

Thanks in advance.

Post #1295357

MVDBA

Posted Friday, May 04, 2012 9:48 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

you will need to supply more info on the stored procedure for anyone to be able to really help you - but my first question

is there the same amount of data in both databases?

MVDBA

Post #1295365

Steve Jones - SSC Editor

Posted Friday, May 04, 2012 9:49 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

Check data size
Check indexing in both
check the execution plans

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1295367

karthik srinivasan

Posted Monday, May 07, 2012 4:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 1:11 AM
Points: 9, Visits: 21

The data in both the databases are in synchronous.

Post #1295868

karthik srinivasan

Posted Monday, May 07, 2012 5:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 1:11 AM
Points: 9, Visits: 21

How do I find the execution plan for the stored proc ? The data and indexes of both the databases are in synchronous.

Post #1295875

Steve Jones - SSC Editor

Posted Monday, May 07, 2012 12:43 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

In sync, are you sure? Have you actually done a sync recently or a restore? It's easy to assume this and be wrong.

To get the execution plan, you can easily select this in an SSMS menu and then run the stored procedure. Use the actual execution plan from both and compare

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1296040

karthik srinivasan

Posted Monday, May 07, 2012 1:20 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 1:11 AM
Points: 9, Visits: 21

Thanks... I'm sure that both of my database data is in synchronous. I'm using sql server 2000, how do I still see the execution plan for stored proc ?

Post #1296063

Steve Jones - SSC Editor

Posted Monday, May 07, 2012 1:43 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

Sorry, didn't realize this was SQL 2000. (Doh!)

Query Analyzer has a menu item for this. You select it before you run the query.

Don't be sure your schema and data are the same (indexing, etc.). Use a tool, or run queries and try to be sure. At least for the objects that you are using in the stored procedure. Assuming they are and not checking can end up wasting a lot of time.

Even if they were when you sync'd things, unless you are the only person that could possibly make changes on the dev server, you should check.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1296080

« Prev Topic | Next Topic »

Permissions