May 4, 2012 at 9:40 am
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.
May 4, 2012 at 9:48 am
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
May 4, 2012 at 9:49 am
Check data size
Check indexing in both
check the execution plans
May 7, 2012 at 4:50 am
The data in both the databases are in synchronous.
May 7, 2012 at 5:11 am
How do I find the execution plan for the stored proc ? The data and indexes of both the databases are in synchronous.
May 7, 2012 at 12:43 pm
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
May 7, 2012 at 1:20 pm
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 ?
May 7, 2012 at 1:43 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply