stored procedure running slow

  • 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.

  • 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

  • Check data size

    Check indexing in both

    check the execution plans

  • The data in both the databases are in synchronous.

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

  • 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

  • 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 ?

  • 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