Performance issue

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Cray
    #1

    Performance issue

    Hello,



    On the database we have stored procedure that loads couple thousands
    rows with information, it has to be done sequentially because during
    this load various variables and values has to be changed and modified.
    We have about 10000 rows to process every day - usually it takes about 1
    hour.

    Some time ago I have noticed a big decrease in performance of this
    process (it takes about 10 to 20 more time to process 10K), profiler
    showed me that duration of each read from some of the tables is huge and
    this is the reason why this process slows down. I have also a
    maintenance plan that updates statistics. When I run this job during
    processing usually everything goes back to normal and reads are very
    fast, performance is ok.



    Could someone explain why this is happening or give me a hint where I
    could find the explanation/answer.



    Greetings Cray
    ............::: ::::: [ @ ] ::::::::....... ....
    Cray at o2.pl



    Tags: None
    • Asarum
      #2
      Re: Performance issue


      On Tue, 13 Jun 2006 23:37:37 +0200, Cray wrote:
      [color=blue]
      > Hello,
      >
      >
      >
      > On the database we have stored procedure that loads couple thousands
      > rows with information, it has to be done sequentially because during
      > this load various variables and values has to be changed and modified.
      > We have about 10000 rows to process every day - usually it takes about 1
      > hour.
      >
      > Some time ago I have noticed a big decrease in performance of this
      > process (it takes about 10 to 20 more time to process 10K), profiler
      > showed me that duration of each read from some of the tables is huge and
      > this is the reason why this process slows down. I have also a
      > maintenance plan that updates statistics. When I run this job during
      > processing usually everything goes back to normal and reads are very
      > fast, performance is ok.
      >
      >
      >
      > Could someone explain why this is happening or give me a hint where I
      > could find the explanation/answer.
      >
      >
      >
      > Greetings Cray
      > ...........:::: :::: [ @ ] ::::::::....... ....
      > Cray at o2.pl[/color]

      I would take a closer look to indexes - if you make inserts into a table
      and if it grows substancially, then if it necessary to scan the whole
      table to insert a single row, then performance can significantly drop down.

        Comment

        • Jack Vamvas
          #3
          Re: Performance issue

          Ideally you should maintain statistics on a daily basis . Are you relying on
          AUTO UPDATE?

          --
          ----
          Jack Vamvas
          _______________ _______________ _____
          Receive free SQL tips - www.ciquery.com/sqlserver.htm
          _______________ _______________ _____


          "Cray" <cray@nospam.o2 .pl> wrote in message
          news:e6nb6k$anb $1@inews.gazeta .pl...[color=blue]
          > Hello,
          >
          >
          >
          > On the database we have stored procedure that loads couple thousands
          > rows with information, it has to be done sequentially because during
          > this load various variables and values has to be changed and modified.
          > We have about 10000 rows to process every day - usually it takes about 1
          > hour.
          >
          > Some time ago I have noticed a big decrease in performance of this
          > process (it takes about 10 to 20 more time to process 10K), profiler
          > showed me that duration of each read from some of the tables is huge and
          > this is the reason why this process slows down. I have also a
          > maintenance plan that updates statistics. When I run this job during
          > processing usually everything goes back to normal and reads are very
          > fast, performance is ok.
          >
          >
          >
          > Could someone explain why this is happening or give me a hint where I
          > could find the explanation/answer.
          >
          >
          >
          > Greetings Cray
          > ...........:::: :::: [ @ ] ::::::::....... ....
          > Cray at o2.pl
          >
          >
          >[/color]


            Comment

            • Cray
              #4
              Re: Performance issue

              "Jack Vamvas" <DEL_TO_REPLYte chsupport@cique ry.com> wrote in message
              news:WtydnZ97Io pJYBLZRVnyhQ@bt .com...[color=blue]
              > Ideally you should maintain statistics on a daily basis . Are you relying
              > on
              > AUTO UPDATE?[/color]
              No, im also doing mainenance job that updates statistics - after backup is
              done. I have maintenance hours between 1AM and 3AM.

              Greetings Cray
              ............::: ::::: [ @ ] ::::::::....... ....
              Cray at o2.pl


                Comment

                • Erland Sommarskog
                  #5
                  Re: Performance issue

                  Cray (cray@nospam.o2 .pl) writes:[color=blue]
                  > On the database we have stored procedure that loads couple thousands
                  > rows with information, it has to be done sequentially because during
                  > this load various variables and values has to be changed and modified.
                  > We have about 10000 rows to process every day - usually it takes about 1
                  > hour.
                  >
                  > Some time ago I have noticed a big decrease in performance of this
                  > process (it takes about 10 to 20 more time to process 10K), profiler
                  > showed me that duration of each read from some of the tables is huge and
                  > this is the reason why this process slows down. I have also a
                  > maintenance plan that updates statistics. When I run this job during
                  > processing usually everything goes back to normal and reads are very
                  > fast, performance is ok.
                  >
                  > Could someone explain why this is happening or give me a hint where I
                  > could find the explanation/answer.[/color]

                  If something triggers a recompile, and the statistics are out of date
                  this can lead to a bad plan.

                  A typical example of situation where you quickly can run into bad
                  statistics is with monotonically growing keys, were queries are on
                  the most recenly inserted rows. The statistics may make it seem than
                  an interval is almost empty, when there are in fact plenty of rows.

                  It's difficult to give suggestions for how to address this without more
                  information about the application. Possible methods involve query
                  hints, trying to prevent recompiles, and running UPDATE STATISTCS on
                  selected indexes more frequently.

                  --
                  Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                  Books Online for SQL Server 2005 at
                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                  Books Online for SQL Server 2000 at
                  http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                    Comment

                    Previous template Next
                    Working...