Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored procedure running slow Expand / Collapse
Author
Message
Posted Friday, May 4, 2012 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, May 4, 2012 9:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Posted Friday, May 4, 2012 9:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:46 PM
Points: 31,081, Visits: 15,527
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
Posted Monday, May 7, 2012 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, May 7, 2012 5:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, May 7, 2012 12:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:46 PM
Points: 31,081, Visits: 15,527
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
Posted Monday, May 7, 2012 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, May 7, 2012 1:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:46 PM
Points: 31,081, Visits: 15,527
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 »

Add to briefcase

Permissions Expand / Collapse