4 Replies Latest reply: Feb 4, 2013 1:37 AM by Nikolay Savvinov RSS

    Perfermance Issue

    852595
      Hi All,
      I have one procedure is running in one database which is taking 2 urs. But same procedure (sama data volume,same execution plan) is taking 4 hours in another database.
      Both system are different and it is in window OS.

      Could some please clarify what shall i check?

      Thanks in advance.
        • 1. Re: Perfermance Issue
          Karan
          How many statements are there in the procedure, every statement in the procedure will have a different plan, Buy the way good thing will be to check the CPU time because 1 system could be having sufficient CPU resources while the other would be starving for the same. So check the same. Even the I/O cost could be an added factor. Having execution plan same doesnt mean the elapsed time would be same on different systems.
          • 2. Re: Perfermance Issue
            sb92075
            849592 wrote:
            Hi All,
            I have one procedure is running in one database which is taking 2 urs. But same procedure (sama data volume,same execution plan) is taking 4 hours in another database.
            Both system are different and it is in window OS.

            Could some please clarify what shall i check?

            Thanks in advance.
            on both system do as below

            ALTER SESSION SET SQL_TRACE=TRUE;

            process both trace files using TKPROF & post results back here
            • 3. Re: Perfermance Issue
              marksmithusa
              Stats, probably. But with nothing to go on, that's just a wild guess...
              • 4. Re: Perfermance Issue
                Nikolay Savvinov
                Hi

                it would be surprising if two different databases had same performance, so it is only natural that performance differs. The reason could be different I/O speed, caching efficiency, optimizer settings, etc. If you really think that you should have more similar performance, then do the following:

                1) use profiler to determine which statement(s) is (are) taking most of the time
                2) use rowsource statistics to understand why

                Best regards,
                Nikolay