Loading ...
Sorry, an error occurred while loading the content.
 

Re: : Query Optimization

Expand Messages
  • Babu
    I have been working on SP Tuning / Optimization quite some time. I haven t used the Index Tuning Wizard, since it is not giving the required output or output
    Message 1 of 11 , Nov 22 6:00 AM
      I have been working on SP Tuning / Optimization quite some time. I
      haven't used the Index Tuning Wizard, since it is not giving the
      required output or output in useful/easy to understand format.

      I suggest you to do the tuning manually, since it is fast and more
      reliable. All you need is the SQL Server Management Studio.

      First and foremost I would like to tell you that, adding an index may
      not solve your problem. I hope your table design include a Clustered
      Index, possibly on Unique Column(s) and other Non-clustered indexes.

      1. Execute your query with actual parameters from Management Studio,
      by setting the "Include Actual Execution Plan" On (this button is
      visible on Toolbar)

      2. Execution plan will be shown for each statementin the SP, in
      blocks.

      3. For each block, the percentage of elapsed time will be shown.
      From this you can find which statement is consuming most of the
      execution time. Eg. if there are 5 statements in your procedure, you
      may see that 3rd statement is consuming 90% of the execution time and
      the rest 4 altogether only 10%.

      4. Now you can consider the 3rd statement as candidate first-hand.

      5. Check the block (here 3rd one) in your execution plan.

      6. In this 3rd statement if you are joining 3 tables, say T1, T2 and
      T3, and the time consumed on each table will be shown. Eg. T1 (7%),
      T2 (86%) and T3 (7%).

      7. From this we can understand that the main bottle-neck in you SP is
      the table T2 which is consuming 86% of the time for this statement,
      and you may need to act on this table first.

      Now consider your 3rd statement in the SP.

      1. Consider the way you joined the tables, not the order in which you
      have joined, but,

      (a) Do you really need all the three tables in your join
      condition ? Check whether the data is available within the other
      two tables itself.

      2. Check the "Arrow" mark indicating the join of each table. Is it
      very thick ?

      (a) A thick arrow indicates that a huge number of rows are being
      retrieved from this table.

      (b) Do you really need all these rows. Is it possible to reduce
      the number of rows by adding some more filters (conditions in where
      clause) ?

      3. Now see the type of operation done on each table. Most preferred
      action is an Index Seek.

      (a) If it is a Table Scan or Clustered Index Scan I suggest you may
      need an index here.

      (b) Even though there is an index available, and the index is not
      being used or it is doing an index scan instead of index seek,
      consider

      (i) Check the possiblity of adding additional column filters as
      mentioned in 2(b) above. If the volume of data is high, query
      optimizer may choose to go for Index Scan instead of index seek.

      (ii) Is your index columns are in the same order which you are
      selecting, and whether the columns in where condition is included in
      your index.

      This case you may need to add a new index. If you are trying to
      alter the existing one, ensure that it is not being used some where
      else.

      If this procedure is used much frequently, consider creating a
      Covering Index.


      (This document is a draft one and incomplete. You may see a detailed
      one in some blogs sooner. Request you all, your inputs and
      suggestions to make this a document)


      HTH,

      Babu



      --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@...> wrote:
      >
      > Guys,
      >
      > i have used index tuning wizard in SQL 2000 a couple of times. as
      i remember i recommends us on a table where should we create a index.
      i need to do it for SQL 2005, DTA is there, i tried it but in
      recommendation...the grid is empty...might be im not using it
      properly or if there is any other way to check and recommend index on
      a table please let me know.
      >
      > as i have been involved in SP tuning, any quick tips would also
      be helpful.
      > a quick response would be highly appreciated.
      >
      > Thanks,
      > deepak
      >
      >
      > ---------------------------------
      > Never miss a thing. Make Yahoo your homepage.
      >
      > [Non-text portions of this message have been removed]
      >
    • Roji P Thomas
      To answer deepaks question, the following query will give you the information regarding missing indexes based on the workload since last restart. select d.* ,
      Message 2 of 11 , Nov 22 9:05 AM
        To answer deepaks question, the following query will give you the information regarding missing indexes based on the workload since last restart.

        select d.*

        , s.avg_total_user_cost

        , s.avg_user_impact

        , s.last_user_seek

        ,s.unique_compiles

        from sys.dm_db_missing_index_group_stats s

        ,sys.dm_db_missing_index_groups g

        ,sys.dm_db_missing_index_details d

        where s.group_handle = g.index_group_handle

        and d.index_handle = g.index_handle

        order by s.avg_user_impact desc

        go

        Regards

        Roji P Thomas




        [Non-text portions of this message have been removed]
      • Deepak Garg
        Babu, I have few doubts here: 1) For optimize stored procedure : should all the errow in all queries be thin.(not thik) 2) The query cost lets say is 60% for
        Message 3 of 11 , Nov 27 5:05 AM
          Babu,

          I have few doubts here:

          1) For optimize stored procedure : should "all" the errow in all queries be thin.(not thik)
          2) The query cost lets say is 60% for one query and maximum is on one table. how much is shold reduce. because if i am not wrong the overall query cose (for all the queries in the SP) would be 100% is any case?

          Please assist.
          and thanks a lot again for your below valuable suggations.

          regards
          Deepak


          Babu <babupp@...> wrote:
          I have been working on SP Tuning / Optimization quite some time. I
          haven't used the Index Tuning Wizard, since it is not giving the
          required output or output in useful/easy to understand format.

          I suggest you to do the tuning manually, since it is fast and more
          reliable. All you need is the SQL Server Management Studio.

          First and foremost I would like to tell you that, adding an index may
          not solve your problem. I hope your table design include a Clustered
          Index, possibly on Unique Column(s) and other Non-clustered indexes.

          1. Execute your query with actual parameters from Management Studio,
          by setting the "Include Actual Execution Plan" On (this button is
          visible on Toolbar)

          2. Execution plan will be shown for each statementin the SP, in
          blocks.

          3. For each block, the percentage of elapsed time will be shown.
          From this you can find which statement is consuming most of the
          execution time. Eg. if there are 5 statements in your procedure, you
          may see that 3rd statement is consuming 90% of the execution time and
          the rest 4 altogether only 10%.

          4. Now you can consider the 3rd statement as candidate first-hand.

          5. Check the block (here 3rd one) in your execution plan.

          6. In this 3rd statement if you are joining 3 tables, say T1, T2 and
          T3, and the time consumed on each table will be shown. Eg. T1 (7%),
          T2 (86%) and T3 (7%).

          7. From this we can understand that the main bottle-neck in you SP is
          the table T2 which is consuming 86% of the time for this statement,
          and you may need to act on this table first.

          Now consider your 3rd statement in the SP.

          1. Consider the way you joined the tables, not the order in which you
          have joined, but,

          (a) Do you really need all the three tables in your join
          condition ? Check whether the data is available within the other
          two tables itself.

          2. Check the "Arrow" mark indicating the join of each table. Is it
          very thick ?

          (a) A thick arrow indicates that a huge number of rows are being
          retrieved from this table.

          (b) Do you really need all these rows. Is it possible to reduce
          the number of rows by adding some more filters (conditions in where
          clause) ?

          3. Now see the type of operation done on each table. Most preferred
          action is an Index Seek.

          (a) If it is a Table Scan or Clustered Index Scan I suggest you may
          need an index here.

          (b) Even though there is an index available, and the index is not
          being used or it is doing an index scan instead of index seek,
          consider

          (i) Check the possiblity of adding additional column filters as
          mentioned in 2(b) above. If the volume of data is high, query
          optimizer may choose to go for Index Scan instead of index seek.

          (ii) Is your index columns are in the same order which you are
          selecting, and whether the columns in where condition is included in
          your index.

          This case you may need to add a new index. If you are trying to
          alter the existing one, ensure that it is not being used some where
          else.

          If this procedure is used much frequently, consider creating a
          Covering Index.

          (This document is a draft one and incomplete. You may see a detailed
          one in some blogs sooner. Request you all, your inputs and
          suggestions to make this a document)

          HTH,

          Babu

          --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@...> wrote:
          >
          > Guys,
          >
          > i have used index tuning wizard in SQL 2000 a couple of times. as
          i remember i recommends us on a table where should we create a index.
          i need to do it for SQL 2005, DTA is there, i tried it but in
          recommendation...the grid is empty...might be im not using it
          properly or if there is any other way to check and recommend index on
          a table please let me know.
          >
          > as i have been involved in SP tuning, any quick tips would also
          be helpful.
          > a quick response would be highly appreciated.
          >
          > Thanks,
          > deepak
          >
          >
          > ---------------------------------
          > Never miss a thing. Make Yahoo your homepage.
          >
          > [Non-text portions of this message have been removed]
          >






          ---------------------------------
          Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how.

          [Non-text portions of this message have been removed]
        • Roji P Thomas
          Reducing IO is the fundamental of query optimization. Of all the four performance components (CPU, Memory, IO and the DB Engine) IO is the costliest. So make
          Message 4 of 11 , Nov 27 5:14 AM
            Reducing IO is the fundamental of query optimization.
            Of all the four performance components (CPU, Memory, IO and the DB Engine)
            IO is the costliest.

            So make sure you are reading/writing only the rows you actually need.

            The execution plan can give you an idea which statement and which physical operation is the costliest.
            The unit there is not time but execution cost.

            use the SET STATISTICS IO ON and execute the query and look for the physical reads.

            ----- Original Message -----
            From: Deepak Garg
            To: sqlcon@yahoogroups.com
            Cc: babupp@...
            Sent: Tuesday, November 27, 2007 6:35 PM
            Subject: Re: MUGH-SQLCON Re: : Query Optimization


            Babu,

            I have few doubts here:

            1) For optimize stored procedure : should "all" the errow in all queries be thin.(not thik)
            2) The query cost lets say is 60% for one query and maximum is on one table. how much is shold reduce. because if i am not wrong the overall query cose (for all the queries in the SP) would be 100% is any case?

            Please assist.
            and thanks a lot again for your below valuable suggations.

            regards
            Deepak

            Babu <babupp@...> wrote:
            I have been working on SP Tuning / Optimization quite some time. I
            haven't used the Index Tuning Wizard, since it is not giving the
            required output or output in useful/easy to understand format.

            I suggest you to do the tuning manually, since it is fast and more
            reliable. All you need is the SQL Server Management Studio.

            First and foremost I would like to tell you that, adding an index may
            not solve your problem. I hope your table design include a Clustered
            Index, possibly on Unique Column(s) and other Non-clustered indexes.

            1. Execute your query with actual parameters from Management Studio,
            by setting the "Include Actual Execution Plan" On (this button is
            visible on Toolbar)

            2. Execution plan will be shown for each statementin the SP, in
            blocks.

            3. For each block, the percentage of elapsed time will be shown.
            From this you can find which statement is consuming most of the
            execution time. Eg. if there are 5 statements in your procedure, you
            may see that 3rd statement is consuming 90% of the execution time and
            the rest 4 altogether only 10%.

            4. Now you can consider the 3rd statement as candidate first-hand.

            5. Check the block (here 3rd one) in your execution plan.

            6. In this 3rd statement if you are joining 3 tables, say T1, T2 and
            T3, and the time consumed on each table will be shown. Eg. T1 (7%),
            T2 (86%) and T3 (7%).

            7. From this we can understand that the main bottle-neck in you SP is
            the table T2 which is consuming 86% of the time for this statement,
            and you may need to act on this table first.

            Now consider your 3rd statement in the SP.

            1. Consider the way you joined the tables, not the order in which you
            have joined, but,

            (a) Do you really need all the three tables in your join
            condition ? Check whether the data is available within the other
            two tables itself.

            2. Check the "Arrow" mark indicating the join of each table. Is it
            very thick ?

            (a) A thick arrow indicates that a huge number of rows are being
            retrieved from this table.

            (b) Do you really need all these rows. Is it possible to reduce
            the number of rows by adding some more filters (conditions in where
            clause) ?

            3. Now see the type of operation done on each table. Most preferred
            action is an Index Seek.

            (a) If it is a Table Scan or Clustered Index Scan I suggest you may
            need an index here.

            (b) Even though there is an index available, and the index is not
            being used or it is doing an index scan instead of index seek,
            consider

            (i) Check the possiblity of adding additional column filters as
            mentioned in 2(b) above. If the volume of data is high, query
            optimizer may choose to go for Index Scan instead of index seek.

            (ii) Is your index columns are in the same order which you are
            selecting, and whether the columns in where condition is included in
            your index.

            This case you may need to add a new index. If you are trying to
            alter the existing one, ensure that it is not being used some where
            else.

            If this procedure is used much frequently, consider creating a
            Covering Index.

            (This document is a draft one and incomplete. You may see a detailed
            one in some blogs sooner. Request you all, your inputs and
            suggestions to make this a document)

            HTH,

            Babu

            --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@...> wrote:
            >
            > Guys,
            >
            > i have used index tuning wizard in SQL 2000 a couple of times. as
            i remember i recommends us on a table where should we create a index.
            i need to do it for SQL 2005, DTA is there, i tried it but in
            recommendation...the grid is empty...might be im not using it
            properly or if there is any other way to check and recommend index on
            a table please let me know.
            >
            > as i have been involved in SP tuning, any quick tips would also
            be helpful.
            > a quick response would be highly appreciated.
            >
            > Thanks,
            > deepak
            >
            >
            > ---------------------------------
            > Never miss a thing. Make Yahoo your homepage.
            >
            > [Non-text portions of this message have been removed]
            >

            ---------------------------------
            Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how.

            [Non-text portions of this message have been removed]





            [Non-text portions of this message have been removed]
          • Babu
            1) For optimize stored procedure : should all the errow in all queries be thin.(not thik) It depends on the volume of data you are processing in your query.
            Message 5 of 11 , Nov 27 5:58 AM
              1) For optimize stored procedure : should "all" the errow in all
              queries be thin.(not thik)

              It depends on the volume of data you are processing in your query.
              Make sure you are fetching only the required rows and not more than
              that.

              Many cases I found that due to missing filters or wrong indexes, it
              is processing the entire data from tables. Pointing your mouse on
              this arrow will tell you the number of rows read from this table.
              Check whether it is reading more data than necessary. Always try to
              make the arrows as thin as possible.

              2) The query cost lets say is 60% for one query and maximum is on
              one table. how much is shold reduce. because if i am not wrong the
              overall query cose (for all the queries in the SP) would be 100% is
              any case?

              This also depends. There is no bench mark for this, only I can say.
              The execution time 100% is for the whole SP, and when you reduce the
              cost of one statement, you can see other statement cost increases,
              but the overall execution time will be reduced. Your ultimate aim is
              to reduce the overall execution time.

              Run SQL Profiler and see the "Reads", "CPU" and "Duration" while
              executing procedure (ensure to disable Show Exeuction plan, otherwise
              results vary significantly). See the initial values i.e., before
              tuning. I suggest you to keep an initial target of Reads < 10,000,
              and Duration < 1 Sec (1000 ms). But for a procedure which process
              large volume data the reads could vary to a great extend.

              Compare the "Reads", "CPU" and "Duration" before and after your
              modifications.

              Note:
              1. Execution plan vary on different machines, depending on number of
              CPUs, volume of memory, Disk Storage and also on volume of data in
              each tables.

              2. Removing an index also could give you improved performance. Index
              on a bit column can lead processing huge volume data than necessary.

              3. Try to eliminate Temp. tables as much as possible. Use CTE
              instead.

              HTH,

              Babu

              --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@...> wrote:
              >
              > Babu,
              >
              > I have few doubts here:
              >
              > 1) For optimize stored procedure : should "all" the errow in all
              queries be thin.(not thik)
              > 2) The query cost lets say is 60% for one query and maximum is on
              one table. how much is shold reduce. because if i am not wrong the
              overall query cose (for all the queries in the SP) would be 100% is
              any case?
              >
              > Please assist.
              > and thanks a lot again for your below valuable suggations.
              >
              > regards
              > Deepak
              >
              >
              > Babu <babupp@...> wrote:
              > I have been working on SP Tuning / Optimization quite
              some time. I
              > haven't used the Index Tuning Wizard, since it is not giving the
              > required output or output in useful/easy to understand format.
              >
              > I suggest you to do the tuning manually, since it is fast and more
              > reliable. All you need is the SQL Server Management Studio.
              >
              > First and foremost I would like to tell you that, adding an index
              may
              > not solve your problem. I hope your table design include a
              Clustered
              > Index, possibly on Unique Column(s) and other Non-clustered indexes.
              >
              > 1. Execute your query with actual parameters from Management
              Studio,
              > by setting the "Include Actual Execution Plan" On (this button is
              > visible on Toolbar)
              >
              > 2. Execution plan will be shown for each statementin the SP, in
              > blocks.
              >
              > 3. For each block, the percentage of elapsed time will be shown.
              > From this you can find which statement is consuming most of the
              > execution time. Eg. if there are 5 statements in your procedure,
              you
              > may see that 3rd statement is consuming 90% of the execution time
              and
              > the rest 4 altogether only 10%.
              >
              > 4. Now you can consider the 3rd statement as candidate first-hand.
              >
              > 5. Check the block (here 3rd one) in your execution plan.
              >
              > 6. In this 3rd statement if you are joining 3 tables, say T1, T2
              and
              > T3, and the time consumed on each table will be shown. Eg. T1 (7%),
              > T2 (86%) and T3 (7%).
              >
              > 7. From this we can understand that the main bottle-neck in you SP
              is
              > the table T2 which is consuming 86% of the time for this statement,
              > and you may need to act on this table first.
              >
              > Now consider your 3rd statement in the SP.
              >
              > 1. Consider the way you joined the tables, not the order in which
              you
              > have joined, but,
              >
              > (a) Do you really need all the three tables in your join
              > condition ? Check whether the data is available within the other
              > two tables itself.
              >
              > 2. Check the "Arrow" mark indicating the join of each table. Is it
              > very thick ?
              >
              > (a) A thick arrow indicates that a huge number of rows are being
              > retrieved from this table.
              >
              > (b) Do you really need all these rows. Is it possible to reduce
              > the number of rows by adding some more filters (conditions in where
              > clause) ?
              >
              > 3. Now see the type of operation done on each table. Most preferred
              > action is an Index Seek.
              >
              > (a) If it is a Table Scan or Clustered Index Scan I suggest you may
              > need an index here.
              >
              > (b) Even though there is an index available, and the index is not
              > being used or it is doing an index scan instead of index seek,
              > consider
              >
              > (i) Check the possiblity of adding additional column filters as
              > mentioned in 2(b) above. If the volume of data is high, query
              > optimizer may choose to go for Index Scan instead of index seek.
              >
              > (ii) Is your index columns are in the same order which you are
              > selecting, and whether the columns in where condition is included
              in
              > your index.
              >
              > This case you may need to add a new index. If you are trying to
              > alter the existing one, ensure that it is not being used some where
              > else.
              >
              > If this procedure is used much frequently, consider creating a
              > Covering Index.
              >
              > (This document is a draft one and incomplete. You may see a
              detailed
              > one in some blogs sooner. Request you all, your inputs and
              > suggestions to make this a document)
              >
              > HTH,
              >
              > Babu
              >
              > --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@> wrote:
              > >
              > > Guys,
              > >
              > > i have used index tuning wizard in SQL 2000 a couple of times. as
              > i remember i recommends us on a table where should we create a
              index.
              > i need to do it for SQL 2005, DTA is there, i tried it but in
              > recommendation...the grid is empty...might be im not using it
              > properly or if there is any other way to check and recommend index
              on
              > a table please let me know.
              > >
              > > as i have been involved in SP tuning, any quick tips would also
              > be helpful.
              > > a quick response would be highly appreciated.
              > >
              > > Thanks,
              > > deepak
              > >
              > >
              > > ---------------------------------
              > > Never miss a thing. Make Yahoo your homepage.
              > >
              > > [Non-text portions of this message have been removed]
              > >
              >
              >
              >
              >
              >
              >
              > ---------------------------------
              > Be a better pen pal. Text or chat with friends inside Yahoo! Mail.
              See how.
              >
              > [Non-text portions of this message have been removed]
              >
            • Roji P Thomas
              Sorry, but I will challenge these points. 1. Execution plan vary on different machines, depending on number of CPUs, volume of memory, Disk Storage and also on
              Message 6 of 11 , Nov 27 6:23 AM
                Sorry, but I will challenge these points.

                1. Execution plan vary on different machines, depending on number of
                CPUs, volume of memory, Disk Storage and also on volume of data in
                each tables.
                >> Ideally the execution plan should NOT vary from box to box.
                But it can vary based on the parallelism setting and the amount of data.
                So make sure that the box you are doing performance testing is of same configuration
                as in the Production Box, because if you tune a proc and get a good plan,
                and if you are not getting the same plan in production, then the exercise is waste.

                2. Removing an index also could give you improved performance. Index
                on a bit column can lead processing huge volume data than necessary.
                >>Removing an index will not improve the performance of a SELECT.
                Engine chose to seek an index only if it is selective enough.
                There are several scenarios where an index on a bit column can improve performance.

                3. Try to eliminate Temp. tables as much as possible. Use CTE
                instead.
                >> Why? CTE is not a replacement for Temp table. A non recursive CTE is nothing but a derived table.



                ----- Original Message -----
                From: Babu
                To: sqlcon@yahoogroups.com
                Sent: Tuesday, November 27, 2007 7:28 PM
                Subject: MUGH-SQLCON Re: : Query Optimization


                1) For optimize stored procedure : should "all" the errow in all
                queries be thin.(not thik)

                It depends on the volume of data you are processing in your query.
                Make sure you are fetching only the required rows and not more than
                that.

                Many cases I found that due to missing filters or wrong indexes, it
                is processing the entire data from tables. Pointing your mouse on
                this arrow will tell you the number of rows read from this table.
                Check whether it is reading more data than necessary. Always try to
                make the arrows as thin as possible.

                2) The query cost lets say is 60% for one query and maximum is on
                one table. how much is shold reduce. because if i am not wrong the
                overall query cose (for all the queries in the SP) would be 100% is
                any case?

                This also depends. There is no bench mark for this, only I can say.
                The execution time 100% is for the whole SP, and when you reduce the
                cost of one statement, you can see other statement cost increases,
                but the overall execution time will be reduced. Your ultimate aim is
                to reduce the overall execution time.

                Run SQL Profiler and see the "Reads", "CPU" and "Duration" while
                executing procedure (ensure to disable Show Exeuction plan, otherwise
                results vary significantly). See the initial values i.e., before
                tuning. I suggest you to keep an initial target of Reads < 10,000,
                and Duration < 1 Sec (1000 ms). But for a procedure which process
                large volume data the reads could vary to a great extend.

                Compare the "Reads", "CPU" and "Duration" before and after your
                modifications.

                Note: 1. Execution plan vary on different machines, depending on number of
                CPUs, volume of memory, Disk Storage and also on volume of data in
                each tables.

                2. Removing an index also could give you improved performance. Index
                on a bit column can lead processing huge volume data than necessary.

                3. Try to eliminate Temp. tables as much as possible. Use CTE
                instead.


                HTH,

                Babu

                --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@...> wrote:
                >
                > Babu,
                >
                > I have few doubts here:
                >
                > 1) For optimize stored procedure : should "all" the errow in all
                queries be thin.(not thik)
                > 2) The query cost lets say is 60% for one query and maximum is on
                one table. how much is shold reduce. because if i am not wrong the
                overall query cose (for all the queries in the SP) would be 100% is
                any case?
                >
                > Please assist.
                > and thanks a lot again for your below valuable suggations.
                >
                > regards
                > Deepak
                >
                >
                > Babu <babupp@...> wrote:
                > I have been working on SP Tuning / Optimization quite
                some time. I
                > haven't used the Index Tuning Wizard, since it is not giving the
                > required output or output in useful/easy to understand format.
                >
                > I suggest you to do the tuning manually, since it is fast and more
                > reliable. All you need is the SQL Server Management Studio.
                >
                > First and foremost I would like to tell you that, adding an index
                may
                > not solve your problem. I hope your table design include a
                Clustered
                > Index, possibly on Unique Column(s) and other Non-clustered indexes.
                >
                > 1. Execute your query with actual parameters from Management
                Studio,
                > by setting the "Include Actual Execution Plan" On (this button is
                > visible on Toolbar)
                >
                > 2. Execution plan will be shown for each statementin the SP, in
                > blocks.
                >
                > 3. For each block, the percentage of elapsed time will be shown.
                > From this you can find which statement is consuming most of the
                > execution time. Eg. if there are 5 statements in your procedure,
                you
                > may see that 3rd statement is consuming 90% of the execution time
                and
                > the rest 4 altogether only 10%.
                >
                > 4. Now you can consider the 3rd statement as candidate first-hand.
                >
                > 5. Check the block (here 3rd one) in your execution plan.
                >
                > 6. In this 3rd statement if you are joining 3 tables, say T1, T2
                and
                > T3, and the time consumed on each table will be shown. Eg. T1 (7%),
                > T2 (86%) and T3 (7%).
                >
                > 7. From this we can understand that the main bottle-neck in you SP
                is
                > the table T2 which is consuming 86% of the time for this statement,
                > and you may need to act on this table first.
                >
                > Now consider your 3rd statement in the SP.
                >
                > 1. Consider the way you joined the tables, not the order in which
                you
                > have joined, but,
                >
                > (a) Do you really need all the three tables in your join
                > condition ? Check whether the data is available within the other
                > two tables itself.
                >
                > 2. Check the "Arrow" mark indicating the join of each table. Is it
                > very thick ?
                >
                > (a) A thick arrow indicates that a huge number of rows are being
                > retrieved from this table.
                >
                > (b) Do you really need all these rows. Is it possible to reduce
                > the number of rows by adding some more filters (conditions in where
                > clause) ?
                >
                > 3. Now see the type of operation done on each table. Most preferred
                > action is an Index Seek.
                >
                > (a) If it is a Table Scan or Clustered Index Scan I suggest you may
                > need an index here.
                >
                > (b) Even though there is an index available, and the index is not
                > being used or it is doing an index scan instead of index seek,
                > consider
                >
                > (i) Check the possiblity of adding additional column filters as
                > mentioned in 2(b) above. If the volume of data is high, query
                > optimizer may choose to go for Index Scan instead of index seek.
                >
                > (ii) Is your index columns are in the same order which you are
                > selecting, and whether the columns in where condition is included
                in
                > your index.
                >
                > This case you may need to add a new index. If you are trying to
                > alter the existing one, ensure that it is not being used some where
                > else.
                >
                > If this procedure is used much frequently, consider creating a
                > Covering Index.
                >
                > (This document is a draft one and incomplete. You may see a
                detailed
                > one in some blogs sooner. Request you all, your inputs and
                > suggestions to make this a document)
                >
                > HTH,
                >
                > Babu
                >
                > --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@> wrote:
                > >
                > > Guys,
                > >
                > > i have used index tuning wizard in SQL 2000 a couple of times. as
                > i remember i recommends us on a table where should we create a
                index.
                > i need to do it for SQL 2005, DTA is there, i tried it but in
                > recommendation...the grid is empty...might be im not using it
                > properly or if there is any other way to check and recommend index
                on
                > a table please let me know.
                > >
                > > as i have been involved in SP tuning, any quick tips would also
                > be helpful.
                > > a quick response would be highly appreciated.
                > >
                > > Thanks,
                > > deepak
                > >
                > >
                > > ---------------------------------
                > > Never miss a thing. Make Yahoo your homepage.
                > >
                > > [Non-text portions of this message have been removed]
                > >
                >
                >
                >
                >
                >
                >
                > ---------------------------------
                > Be a better pen pal. Text or chat with friends inside Yahoo! Mail.
                See how.
                >
                > [Non-text portions of this message have been removed]
                >





                [Non-text portions of this message have been removed]
              • Babu
                1. Execution plan vary on different machines, depending on number of CPUs, volume of memory, Disk Storage and also on volume of data in each tables. ... But it
                Message 7 of 11 , Nov 28 4:38 AM
                  1. Execution plan vary on different machines, depending on number of
                  CPUs, volume of memory, Disk Storage and also on volume of data in
                  each tables.
                  >> Ideally the execution plan should NOT vary from box to box.
                  But it can vary based on the parallelism setting and the amount of data.
                  So make sure that the box you are doing performance testing is of same
                  configuration
                  as in the Production Box, because if you tune a proc and get a good
                  plan,
                  and if you are not getting the same plan in production, then the
                  exercise is
                  waste.

                  My Response:

                  Execution plan varies not only on the degree of parallelism, but also on
                  proportion of data in joining tables too. When the volume of data
                  chyanges, Joining order may change, Joining type could be different and
                  data access method also may change.

                  As far as my current project is concerned, we have started tuning the
                  SPs on our workstations (Dual Core CPU with 1 GB RAM) and volume of data
                  is 4GB and then on our Development Servers (2 x Dual Core CPU with 4 GB
                  RAM) with 12 GB data and finally on Pre-production servers which is
                  having the same configuration of production servers (2 x Quad Core CPU
                  with 16 GB RAM) and is having more than 90 GB data.

                  I have seen different execution plans on each machines. If in one
                  procedure if the first statement is consuming more time, in another
                  machine it could be the second statement.

                  Still I don't think testing and tuning on different configuration is
                  waste of time. In the above case we shall be tuning both the
                  statements, and it could be beneficial for the overall performance.

                  In our production serverthe CPU utilization was nearly 100% and we were
                  able to make it to come down to less than 70%.

                  ----------------------------------------------

                  2. Removing an index also could give you improved performance. Index
                  on a bit column can lead processing huge volume data than necessary.
                  >>Removing an index will not improve the performance of a SELECT.
                  Engine chose to seek an index only if it is selective enough.
                  There are several scenarios where an index on a bit column can improve
                  performance.

                  My Response:

                  In one case I found that, one read operation is processing 1.5 Lac
                  records from a table where I need less than 5000 records

                  for my read operation. This was because of an index on a bit column.
                  This case even though the index is not selective

                  enough, but found to be used by the query optmizer.

                  I think an index on a bit column alone is not at all a good idea, where
                  the volume of data is high.

                  The above is all about the Read performance. You may consider the Write
                  performance too while designing your indexes. Over

                  -indexing could lead to degraded performance. We have to consider the
                  system resources too which is common for both Read and

                  Write operations.

                  ----------------------------------------------

                  3. Try to eliminate Temp. tables as much as possible. Use CTE
                  instead.
                  >> Why? CTE is not a replacement for Temp table. A non recursive CTE is
                  nothing
                  but a derived table.

                  My Response:

                  I have re-written many procedures (Eg. procedures retrieving data for
                  paging) eliminating more than one temp. tables. I'm

                  not replacing temp. tables with CTE, but achieving the same result
                  without any temp tables.

                  See a simple example below..

                  --These procedures are just to show the use of CTE replacing Temp.
                  table.

                  -- These procedures retrieve record for list of users page-wise

                  -- Say for Page 2, 10 records per page, it is required list records from
                  11 to 20

                  /************************************ Before
                  ******************************************/

                  CREATE PROCEDURE UsersForPaging

                  @PageNo INT, -- Current Page

                  @RecPerPage INT -- Records per page

                  AS

                  BEGIN

                  SET NOCOUNT ON

                  SET @StartRec = (@PageNo - 1) * @RecPerPage

                  CREATE TABLE #Users

                  (

                  Id INT IDENTITY,

                  UserId INT,

                  UserName VARCHAR(50)

                  )

                  INSERT INTO #Users (UserId, UserName)

                  SELECT UserId, UserName

                  FROM dbo.Users

                  WHERE CreatedDate BETWEEN @FromDate AND @Todate

                  ORDER BY CreatedDate DESC

                  SELECT TOP (@RecPerPage) *

                  FROM #Users

                  WHERE Id > @StartRec

                  END

                  /************************************ After
                  ******************************************/

                  CREATE PROCEDURE UsersForPaging

                  @PageNo INT, -- Current Page

                  @RecPerPage INT -- Records per page

                  AS

                  BEGIN

                  SET NOCOUNT ON

                  SET @StartRec = (@PageNo - 1) * @RecPerPage

                  ;WITH UsersTemp AS

                  (

                  SELECT TOP (@PageNo * @RecPerPage) UserId, UserName, ROW_NUMBER() OVER
                  (ORDER BY CreatedDate DESC) AS RId

                  FROM dbo.Users

                  WHERE CreatedDate BETWEEN @FromDate AND @Todate

                  )

                  SELECT *

                  FROM UsersTemp

                  WHERE RId > @StartRec

                  END

                  /***********************************************************************\
                  *********************/

                  The above procedure is a good sample for "Paging" (I feel). But can be
                  used only with SQL 2005.


                  HTH,

                  Babu



                  --- In sqlcon@yahoogroups.com, "Roji P Thomas" <thomasroji@...> wrote:
                  >
                  > Sorry, but I will challenge these points.
                  >
                  > 1. Execution plan vary on different machines, depending on number of
                  > CPUs, volume of memory, Disk Storage and also on volume of data in
                  > each tables.
                  > >> Ideally the execution plan should NOT vary from box to box.
                  > But it can vary based on the parallelism setting and the amount of
                  data.
                  > So make sure that the box you are doing performance testing is of same
                  configuration
                  > as in the Production Box, because if you tune a proc and get a good
                  plan,
                  > and if you are not getting the same plan in production, then the
                  exercise is waste.
                  >
                  > 2. Removing an index also could give you improved performance. Index
                  > on a bit column can lead processing huge volume data than necessary.
                  > >>Removing an index will not improve the performance of a SELECT.
                  > Engine chose to seek an index only if it is selective enough.
                  > There are several scenarios where an index on a bit column can improve
                  performance.
                  >
                  > 3. Try to eliminate Temp. tables as much as possible. Use CTE
                  > instead.
                  > >> Why? CTE is not a replacement for Temp table. A non recursive CTE
                  is nothing but a derived table.
                  >
                  >
                  >
                  > ----- Original Message -----
                  > From: Babu
                  > To: sqlcon@yahoogroups.com
                  > Sent: Tuesday, November 27, 2007 7:28 PM
                  > Subject: MUGH-SQLCON Re: : Query Optimization
                  >
                  >
                  > 1) For optimize stored procedure : should "all" the errow in all
                  > queries be thin.(not thik)
                  >
                  > It depends on the volume of data you are processing in your query.
                  > Make sure you are fetching only the required rows and not more than
                  > that.
                  >
                  > Many cases I found that due to missing filters or wrong indexes, it
                  > is processing the entire data from tables. Pointing your mouse on
                  > this arrow will tell you the number of rows read from this table.
                  > Check whether it is reading more data than necessary. Always try to
                  > make the arrows as thin as possible.
                  >
                  > 2) The query cost lets say is 60% for one query and maximum is on
                  > one table. how much is shold reduce. because if i am not wrong the
                  > overall query cose (for all the queries in the SP) would be 100% is
                  > any case?
                  >
                  > This also depends. There is no bench mark for this, only I can say.
                  > The execution time 100% is for the whole SP, and when you reduce the
                  > cost of one statement, you can see other statement cost increases,
                  > but the overall execution time will be reduced. Your ultimate aim is
                  > to reduce the overall execution time.
                  >
                  > Run SQL Profiler and see the "Reads", "CPU" and "Duration" while
                  > executing procedure (ensure to disable Show Exeuction plan, otherwise
                  > results vary significantly). See the initial values i.e., before
                  > tuning. I suggest you to keep an initial target of Reads < 10,000,
                  > and Duration < 1 Sec (1000 ms). But for a procedure which process
                  > large volume data the reads could vary to a great extend.
                  >
                  > Compare the "Reads", "CPU" and "Duration" before and after your
                  > modifications.
                  >
                  > Note: 1. Execution plan vary on different machines, depending on
                  number of
                  > CPUs, volume of memory, Disk Storage and also on volume of data in
                  > each tables.
                  >
                  > 2. Removing an index also could give you improved performance. Index
                  > on a bit column can lead processing huge volume data than necessary.
                  >
                  > 3. Try to eliminate Temp. tables as much as possible. Use CTE
                  > instead.
                  >
                  >
                  > HTH,
                  >
                  > Babu
                  >
                  > --- In sqlcon@yahoogroups.com, Deepak Garg deepakgrg@ wrote:
                  > >
                  > > Babu,
                  > >
                  > > I have few doubts here:
                  > >
                  > > 1) For optimize stored procedure : should "all" the errow in all
                  > queries be thin.(not thik)
                  > > 2) The query cost lets say is 60% for one query and maximum is on
                  > one table. how much is shold reduce. because if i am not wrong the
                  > overall query cose (for all the queries in the SP) would be 100% is
                  > any case?
                  > >
                  > > Please assist.
                  > > and thanks a lot again for your below valuable suggations.
                  > >
                  > > regards
                  > > Deepak
                  > >
                  > >
                  > > Babu babupp@ wrote:
                  > > I have been working on SP Tuning / Optimization quite
                  > some time. I
                  > > haven't used the Index Tuning Wizard, since it is not giving the
                  > > required output or output in useful/easy to understand format.
                  > >
                  > > I suggest you to do the tuning manually, since it is fast and more
                  > > reliable. All you need is the SQL Server Management Studio.
                  > >
                  > > First and foremost I would like to tell you that, adding an index
                  > may
                  > > not solve your problem. I hope your table design include a
                  > Clustered
                  > > Index, possibly on Unique Column(s) and other Non-clustered indexes.
                  > >
                  > > 1. Execute your query with actual parameters from Management
                  > Studio,
                  > > by setting the "Include Actual Execution Plan" On (this button is
                  > > visible on Toolbar)
                  > >
                  > > 2. Execution plan will be shown for each statementin the SP, in
                  > > blocks.
                  > >
                  > > 3. For each block, the percentage of elapsed time will be shown.
                  > > From this you can find which statement is consuming most of the
                  > > execution time. Eg. if there are 5 statements in your procedure,
                  > you
                  > > may see that 3rd statement is consuming 90% of the execution time
                  > and
                  > > the rest 4 altogether only 10%.
                  > >
                  > > 4. Now you can consider the 3rd statement as candidate first-hand.
                  > >
                  > > 5. Check the block (here 3rd one) in your execution plan.
                  > >
                  > > 6. In this 3rd statement if you are joining 3 tables, say T1, T2
                  > and
                  > > T3, and the time consumed on each table will be shown. Eg. T1 (7%),
                  > > T2 (86%) and T3 (7%).
                  > >
                  > > 7. From this we can understand that the main bottle-neck in you SP
                  > is
                  > > the table T2 which is consuming 86% of the time for this statement,
                  > > and you may need to act on this table first.
                  > >
                  > > Now consider your 3rd statement in the SP.
                  > >
                  > > 1. Consider the way you joined the tables, not the order in which
                  > you
                  > > have joined, but,
                  > >
                  > > (a) Do you really need all the three tables in your join
                  > > condition ? Check whether the data is available within the other
                  > > two tables itself.
                  > >
                  > > 2. Check the "Arrow" mark indicating the join of each table. Is it
                  > > very thick ?
                  > >
                  > > (a) A thick arrow indicates that a huge number of rows are being
                  > > retrieved from this table.
                  > >
                  > > (b) Do you really need all these rows. Is it possible to reduce
                  > > the number of rows by adding some more filters (conditions in where
                  > > clause) ?
                  > >
                  > > 3. Now see the type of operation done on each table. Most preferred
                  > > action is an Index Seek.
                  > >
                  > > (a) If it is a Table Scan or Clustered Index Scan I suggest you may
                  > > need an index here.
                  > >
                  > > (b) Even though there is an index available, and the index is not
                  > > being used or it is doing an index scan instead of index seek,
                  > > consider
                  > >
                  > > (i) Check the possiblity of adding additional column filters as
                  > > mentioned in 2(b) above. If the volume of data is high, query
                  > > optimizer may choose to go for Index Scan instead of index seek.
                  > >
                  > > (ii) Is your index columns are in the same order which you are
                  > > selecting, and whether the columns in where condition is included
                  > in
                  > > your index.
                  > >
                  > > This case you may need to add a new index. If you are trying to
                  > > alter the existing one, ensure that it is not being used some where
                  > > else.
                  > >
                  > > If this procedure is used much frequently, consider creating a
                  > > Covering Index.
                  > >
                  > > (This document is a draft one and incomplete. You may see a
                  > detailed
                  > > one in some blogs sooner. Request you all, your inputs and
                  > > suggestions to make this a document)
                  > >
                  > > HTH,
                  > >
                  > > Babu
                  > >
                  > > --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@> wrote:
                  > > >
                  > > > Guys,
                  > > >
                  > > > i have used index tuning wizard in SQL 2000 a couple of times. as
                  > > i remember i recommends us on a table where should we create a
                  > index.
                  > > i need to do it for SQL 2005, DTA is there, i tried it but in
                  > > recommendation...the grid is empty...might be im not using it
                  > > properly or if there is any other way to check and recommend index
                  > on
                  > > a table please let me know.
                  > > >
                  > > > as i have been involved in SP tuning, any quick tips would also
                  > > be helpful.
                  > > > a quick response would be highly appreciated.
                  > > >
                  > > > Thanks,
                  > > > deepak
                  > > >
                  > > >
                  > > > ---------------------------------
                  > > > Never miss a thing. Make Yahoo your homepage.
                  > > >
                  > > > [Non-text portions of this message have been removed]
                  > > >
                  > >
                  > >
                  > >
                  > >
                  > >
                  > >
                  > > ---------------------------------
                  > > Be a better pen pal. Text or chat with friends inside Yahoo! Mail.
                  > See how.
                  > >
                  > > [Non-text portions of this message have been removed]
                  > >
                  >
                  >
                  >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >




                  [Non-text portions of this message have been removed]
                • Varra Reddy
                  Hi guys, I need a MS SQL 2000/2005 Stored procedure that works to do these things: Step 1: Full Backup all the databases (master, MSDB and all the user DBs)
                  Message 8 of 11 , Nov 29 9:11 AM
                    Hi guys,

                    I need a MS SQL 2000/2005 Stored procedure that works to do these things:


                    Step 1: Full Backup all the databases (master, MSDB and all the user DBs)
                    Step 2: Delete the old backup files more than 4 days.

                    Thanks a lotÂ…





                    ---------------------------------
                    Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now.

                    [Non-text portions of this message have been removed]
                  • sunil john
                    Dear Babu, I have one question on your stored proc for paging. if am fetching a result set of say 10,0000. how much time it will talk to store in the temp
                    Message 9 of 11 , Dec 15, 2007
                      Dear Babu,

                      I have one question on your stored proc for paging. if am fetching a result set of say 10,0000. how much time it will talk to store in the temp table or CTE, do you think that this is a good method? for huge volume of data. Instead we can use a method of getting the id's of all start position and return to front end and from front end we can get the required page at required position. In this cause u don't have to retrieve all the records unnecessarily. correct me if am wrong.

                      Best Regards
                      Sunil John

                      ----- Original Message ----
                      From: Babu <babupp@...>
                      To: sqlcon@yahoogroups.com
                      Sent: Wednesday, November 28, 2007 3:38:23 PM
                      Subject: MUGH-SQLCON Re: : Query Optimization















                      1. Execution plan vary on different machines, depending on number of

                      CPUs, volume of memory, Disk Storage and also on volume of data in

                      each tables.

                      >> Ideally the execution plan should NOT vary from box to box.

                      But it can vary based on the parallelism setting and the amount of data.

                      So make sure that the box you are doing performance testing is of same

                      configuration

                      as in the Production Box, because if you tune a proc and get a good

                      plan,

                      and if you are not getting the same plan in production, then the

                      exercise is

                      waste.



                      My Response:



                      Execution plan varies not only on the degree of parallelism, but also on

                      proportion of data in joining tables too. When the volume of data

                      chyanges, Joining order may change, Joining type could be different and

                      data access method also may change.



                      As far as my current project is concerned, we have started tuning the

                      SPs on our workstations (Dual Core CPU with 1 GB RAM) and volume of data

                      is 4GB and then on our Development Servers (2 x Dual Core CPU with 4 GB

                      RAM) with 12 GB data and finally on Pre-production servers which is

                      having the same configuration of production servers (2 x Quad Core CPU

                      with 16 GB RAM) and is having more than 90 GB data.



                      I have seen different execution plans on each machines. If in one

                      procedure if the first statement is consuming more time, in another

                      machine it could be the second statement.



                      Still I don't think testing and tuning on different configuration is

                      waste of time. In the above case we shall be tuning both the

                      statements, and it could be beneficial for the overall performance.



                      In our production serverthe CPU utilization was nearly 100% and we were

                      able to make it to come down to less than 70%.



                      ------------ --------- --------- --------- -------



                      2. Removing an index also could give you improved performance. Index

                      on a bit column can lead processing huge volume data than necessary.

                      >>Removing an index will not improve the performance of a SELECT.

                      Engine chose to seek an index only if it is selective enough.

                      There are several scenarios where an index on a bit column can improve

                      performance.



                      My Response:



                      In one case I found that, one read operation is processing 1.5 Lac

                      records from a table where I need less than 5000 records



                      for my read operation. This was because of an index on a bit column.

                      This case even though the index is not selective



                      enough, but found to be used by the query optmizer.



                      I think an index on a bit column alone is not at all a good idea, where

                      the volume of data is high.



                      The above is all about the Read performance. You may consider the Write

                      performance too while designing your indexes. Over



                      -indexing could lead to degraded performance. We have to consider the

                      system resources too which is common for both Read and



                      Write operations.



                      ------------ --------- --------- --------- -------



                      3. Try to eliminate Temp. tables as much as possible. Use CTE

                      instead.

                      >> Why? CTE is not a replacement for Temp table. A non recursive CTE is

                      nothing

                      but a derived table.



                      My Response:



                      I have re-written many procedures (Eg. procedures retrieving data for

                      paging) eliminating more than one temp. tables. I'm



                      not replacing temp. tables with CTE, but achieving the same result

                      without any temp tables.



                      See a simple example below..



                      --These procedures are just to show the use of CTE replacing Temp.

                      table.



                      -- These procedures retrieve record for list of users page-wise



                      -- Say for Page 2, 10 records per page, it is required list records from

                      11 to 20



                      /*********** ********* ********* ******* Before

                      ************ ********* ********* ********* ***/



                      CREATE PROCEDURE UsersForPaging



                      @PageNo INT, -- Current Page



                      @RecPerPage INT -- Records per page



                      AS



                      BEGIN



                      SET NOCOUNT ON



                      SET @StartRec = (@PageNo - 1) * @RecPerPage



                      CREATE TABLE #Users



                      (



                      Id INT IDENTITY,



                      UserId INT,



                      UserName VARCHAR(50)



                      )



                      INSERT INTO #Users (UserId, UserName)



                      SELECT UserId, UserName



                      FROM dbo.Users



                      WHERE CreatedDate BETWEEN @FromDate AND @Todate



                      ORDER BY CreatedDate DESC



                      SELECT TOP (@RecPerPage) *



                      FROM #Users



                      WHERE Id > @StartRec



                      END



                      /*********** ********* ********* ******* After

                      ************ ********* ********* ********* ***/



                      CREATE PROCEDURE UsersForPaging



                      @PageNo INT, -- Current Page



                      @RecPerPage INT -- Records per page



                      AS



                      BEGIN



                      SET NOCOUNT ON



                      SET @StartRec = (@PageNo - 1) * @RecPerPage



                      ;WITH UsersTemp AS



                      (



                      SELECT TOP (@PageNo * @RecPerPage) UserId, UserName, ROW_NUMBER() OVER

                      (ORDER BY CreatedDate DESC) AS RId



                      FROM dbo.Users



                      WHERE CreatedDate BETWEEN @FromDate AND @Todate



                      )



                      SELECT *



                      FROM UsersTemp



                      WHERE RId > @StartRec



                      END



                      /*********** ********* ********* ********* ********* ********* ********* ******\

                      ************ ********* /



                      The above procedure is a good sample for "Paging" (I feel). But can be

                      used only with SQL 2005.



                      HTH,



                      Babu



                      --- In sqlcon@yahoogroups. com, "Roji P Thomas" <thomasroji@ ...> wrote:

                      >

                      > Sorry, but I will challenge these points.

                      >

                      > 1. Execution plan vary on different machines, depending on number of

                      > CPUs, volume of memory, Disk Storage and also on volume of data in

                      > each tables.

                      > >> Ideally the execution plan should NOT vary from box to box.

                      > But it can vary based on the parallelism setting and the amount of

                      data.

                      > So make sure that the box you are doing performance testing is of same

                      configuration

                      > as in the Production Box, because if you tune a proc and get a good

                      plan,

                      > and if you are not getting the same plan in production, then the

                      exercise is waste.

                      >

                      > 2. Removing an index also could give you improved performance. Index

                      > on a bit column can lead processing huge volume data than necessary.

                      > >>Removing an index will not improve the performance of a SELECT.

                      > Engine chose to seek an index only if it is selective enough.

                      > There are several scenarios where an index on a bit column can improve

                      performance.

                      >

                      > 3. Try to eliminate Temp. tables as much as possible. Use CTE

                      > instead.

                      > >> Why? CTE is not a replacement for Temp table. A non recursive CTE

                      is nothing but a derived table.

                      >

                      >

                      >

                      > ----- Original Message -----

                      > From: Babu

                      > To: sqlcon@yahoogroups. com

                      > Sent: Tuesday, November 27, 2007 7:28 PM

                      > Subject: MUGH-SQLCON Re: : Query Optimization

                      >

                      >

                      > 1) For optimize stored procedure : should "all" the errow in all

                      > queries be thin.(not thik)

                      >

                      > It depends on the volume of data you are processing in your query.

                      > Make sure you are fetching only the required rows and not more than

                      > that.

                      >

                      > Many cases I found that due to missing filters or wrong indexes, it

                      > is processing the entire data from tables. Pointing your mouse on

                      > this arrow will tell you the number of rows read from this table.

                      > Check whether it is reading more data than necessary. Always try to

                      > make the arrows as thin as possible.

                      >

                      > 2) The query cost lets say is 60% for one query and maximum is on

                      > one table. how much is shold reduce. because if i am not wrong the

                      > overall query cose (for all the queries in the SP) would be 100% is

                      > any case?

                      >

                      > This also depends. There is no bench mark for this, only I can say.

                      > The execution time 100% is for the whole SP, and when you reduce the

                      > cost of one statement, you can see other statement cost increases,

                      > but the overall execution time will be reduced. Your ultimate aim is

                      > to reduce the overall execution time.

                      >

                      > Run SQL Profiler and see the "Reads", "CPU" and "Duration" while

                      > executing procedure (ensure to disable Show Exeuction plan, otherwise

                      > results vary significantly) . See the initial values i.e., before

                      > tuning. I suggest you to keep an initial target of Reads < 10,000,

                      > and Duration < 1 Sec (1000 ms). But for a procedure which process

                      > large volume data the reads could vary to a great extend.

                      >

                      > Compare the "Reads", "CPU" and "Duration" before and after your

                      > modifications.

                      >

                      > Note: 1. Execution plan vary on different machines, depending on

                      number of

                      > CPUs, volume of memory, Disk Storage and also on volume of data in

                      > each tables.

                      >

                      > 2. Removing an index also could give you improved performance. Index

                      > on a bit column can lead processing huge volume data than necessary.

                      >

                      > 3. Try to eliminate Temp. tables as much as possible. Use CTE

                      > instead.

                      >

                      >

                      > HTH,

                      >

                      > Babu

                      >

                      > --- In sqlcon@yahoogroups. com, Deepak Garg deepakgrg@ wrote:

                      > >

                      > > Babu,

                      > >

                      > > I have few doubts here:

                      > >

                      > > 1) For optimize stored procedure : should "all" the errow in all

                      > queries be thin.(not thik)

                      > > 2) The query cost lets say is 60% for one query and maximum is on

                      > one table. how much is shold reduce. because if i am not wrong the

                      > overall query cose (for all the queries in the SP) would be 100% is

                      > any case?

                      > >

                      > > Please assist.

                      > > and thanks a lot again for your below valuable suggations.

                      > >

                      > > regards

                      > > Deepak

                      > >

                      > >

                      > > Babu babupp@ wrote:

                      > > I have been working on SP Tuning / Optimization quite

                      > some time. I

                      > > haven't used the Index Tuning Wizard, since it is not giving the

                      > > required output or output in useful/easy to understand format.

                      > >

                      > > I suggest you to do the tuning manually, since it is fast and more

                      > > reliable. All you need is the SQL Server Management Studio.

                      > >

                      > > First and foremost I would like to tell you that, adding an index

                      > may

                      > > not solve your problem. I hope your table design include a

                      > Clustered

                      > > Index, possibly on Unique Column(s) and other Non-clustered indexes.

                      > >

                      > > 1. Execute your query with actual parameters from Management

                      > Studio,

                      > > by setting the "Include Actual Execution Plan" On (this button is

                      > > visible on Toolbar)

                      > >

                      > > 2. Execution plan will be shown for each statementin the SP, in

                      > > blocks.

                      > >

                      > > 3. For each block, the percentage of elapsed time will be shown.

                      > > From this you can find which statement is consuming most of the

                      > > execution time. Eg. if there are 5 statements in your procedure,

                      > you

                      > > may see that 3rd statement is consuming 90% of the execution time

                      > and

                      > > the rest 4 altogether only 10%.

                      > >

                      > > 4. Now you can consider the 3rd statement as candidate first-hand.

                      > >

                      > > 5. Check the block (here 3rd one) in your execution plan.

                      > >

                      > > 6. In this 3rd statement if you are joining 3 tables, say T1, T2

                      > and

                      > > T3, and the time consumed on each table will be shown. Eg. T1 (7%),

                      > > T2 (86%) and T3 (7%).

                      > >

                      > > 7. From this we can understand that the main bottle-neck in you SP

                      > is

                      > > the table T2 which is consuming 86% of the time for this statement,

                      > > and you may need to act on this table first.

                      > >

                      > > Now consider your 3rd statement in the SP.

                      > >

                      > > 1. Consider the way you joined the tables, not the order in which

                      > you

                      > > have joined, but,

                      > >

                      > > (a) Do you really need all the three tables in your join

                      > > condition ? Check whether the data is available within the other

                      > > two tables itself.

                      > >

                      > > 2. Check the "Arrow" mark indicating the join of each table. Is it

                      > > very thick ?

                      > >

                      > > (a) A thick arrow indicates that a huge number of rows are being

                      > > retrieved from this table.

                      > >

                      > > (b) Do you really need all these rows. Is it possible to reduce

                      > > the number of rows by adding some more filters (conditions in where

                      > > clause) ?

                      > >

                      > > 3. Now see the type of operation done on each table. Most preferred

                      > > action is an Index Seek.

                      > >

                      > > (a) If it is a Table Scan or Clustered Index Scan I suggest you may

                      > > need an index here.

                      > >

                      > > (b) Even though there is an index available, and the index is not

                      > > being used or it is doing an index scan instead of index seek,

                      > > consider

                      > >

                      > > (i) Check the possiblity of adding additional column filters as

                      > > mentioned in 2(b) above. If the volume of data is high, query

                      > > optimizer may choose to go for Index Scan instead of index seek.

                      > >

                      > > (ii) Is your index columns are in the same order which you are

                      > > selecting, and whether the columns in where condition is included

                      > in

                      > > your index.

                      > >

                      > > This case you may need to add a new index. If you are trying to

                      > > alter the existing one, ensure that it is not being used some where

                      > > else.

                      > >

                      > > If this procedure is used much frequently, consider creating a

                      > > Covering Index.

                      > >

                      > > (This document is a draft one and incomplete. You may see a

                      > detailed

                      > > one in some blogs sooner. Request you all, your inputs and

                      > > suggestions to make this a document)

                      > >

                      > > HTH,

                      > >

                      > > Babu

                      > >

                      > > --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@> wrote:

                      > > >

                      > > > Guys,

                      > > >

                      > > > i have used index tuning wizard in SQL 2000 a couple of times. as

                      > > i remember i recommends us on a table where should we create a

                      > index.

                      > > i need to do it for SQL 2005, DTA is there, i tried it but in

                      > > recommendation. ..the grid is empty...might be im not using it

                      > > properly or if there is any other way to check and recommend index

                      > on

                      > > a table please let me know.

                      > > >

                      > > > as i have been involved in SP tuning, any quick tips would also

                      > > be helpful.

                      > > > a quick response would be highly appreciated.

                      > > >

                      > > > Thanks,

                      > > > deepak

                      > > >

                      > > >

                      > > > ------------ --------- --------- ---

                      > > > Never miss a thing. Make Yahoo your homepage.

                      > > >

                      > > > [Non-text portions of this message have been removed]

                      > > >

                      > >

                      > >

                      > >

                      > >

                      > >

                      > >

                      > > ------------ --------- --------- ---

                      > > Be a better pen pal. Text or chat with friends inside Yahoo! Mail.

                      > See how.

                      > >

                      > > [Non-text portions of this message have been removed]

                      > >

                      >

                      >

                      >

                      >

                      >

                      > [Non-text portions of this message have been removed]

                      >



                      [Non-text portions of this message have been removed]














                      <!--

                      #ygrp-mkp{
                      border:1px solid #d8d8d8;font-family:Arial;margin:14px 0px;padding:0px 14px;}
                      #ygrp-mkp hr{
                      border:1px solid #d8d8d8;}
                      #ygrp-mkp #hd{
                      color:#628c2a;font-size:85%;font-weight:bold;line-height:122%;margin:10px 0px;}
                      #ygrp-mkp #ads{
                      margin-bottom:10px;}
                      #ygrp-mkp .ad{
                      padding:0 0;}
                      #ygrp-mkp .ad a{
                      color:#0000ff;text-decoration:none;}
                      -->



                      <!--

                      #ygrp-sponsor #ygrp-lc{
                      font-family:Arial;}
                      #ygrp-sponsor #ygrp-lc #hd{
                      margin:10px 0px;font-weight:bold;font-size:78%;line-height:122%;}
                      #ygrp-sponsor #ygrp-lc .ad{
                      margin-bottom:10px;padding:0 0;}
                      -->



                      <!--

                      #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;}
                      #ygrp-mlmsg table {font-size:inherit;font:100%;}
                      #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;}
                      #ygrp-mlmsg pre, code {font:115% monospace;}
                      #ygrp-mlmsg * {line-height:1.22em;}
                      #ygrp-text{
                      font-family:Georgia;
                      }
                      #ygrp-text p{
                      margin:0 0 1em 0;}
                      #ygrp-tpmsgs{
                      font-family:Arial;
                      clear:both;}
                      #ygrp-vitnav{
                      padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
                      #ygrp-vitnav a{
                      padding:0 1px;}
                      #ygrp-actbar{
                      clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
                      #ygrp-actbar .left{
                      float:left;white-space:nowrap;}
                      .bld{font-weight:bold;}
                      #ygrp-grft{
                      font-family:Verdana;font-size:77%;padding:15px 0;}
                      #ygrp-ft{
                      font-family:verdana;font-size:77%;border-top:1px solid #666;
                      padding:5px 0;
                      }
                      #ygrp-mlmsg #logo{
                      padding-bottom:10px;}

                      #ygrp-vital{
                      background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
                      #ygrp-vital #vithd{
                      font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
                      #ygrp-vital ul{
                      padding:0;margin:2px 0;}
                      #ygrp-vital ul li{
                      list-style-type:none;clear:both;border:1px solid #e0ecee;
                      }
                      #ygrp-vital ul li .ct{
                      font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
                      #ygrp-vital ul li .cat{
                      font-weight:bold;}
                      #ygrp-vital a{
                      text-decoration:none;}

                      #ygrp-vital a:hover{
                      text-decoration:underline;}

                      #ygrp-sponsor #hd{
                      color:#999;font-size:77%;}
                      #ygrp-sponsor #ov{
                      padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
                      #ygrp-sponsor #ov ul{
                      padding:0 0 0 8px;margin:0;}
                      #ygrp-sponsor #ov li{
                      list-style-type:square;padding:6px 0;font-size:77%;}
                      #ygrp-sponsor #ov li a{
                      text-decoration:none;font-size:130%;}
                      #ygrp-sponsor #nc{
                      background-color:#eee;margin-bottom:20px;padding:0 8px;}
                      #ygrp-sponsor .ad{
                      padding:8px 0;}
                      #ygrp-sponsor .ad #hd1{
                      font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
                      #ygrp-sponsor .ad a{
                      text-decoration:none;}
                      #ygrp-sponsor .ad a:hover{
                      text-decoration:underline;}
                      #ygrp-sponsor .ad p{
                      margin:0;}
                      o{font-size:0;}
                      .MsoNormal{
                      margin:0 0 0 0;}
                      #ygrp-text tt{
                      font-size:120%;}
                      blockquote{margin:0 0 0 4px;}
                      .replbq{margin:4;}
                      -->








                      Send instant messages to your online friends http://uk.messenger.yahoo.com

                      [Non-text portions of this message have been removed]
                    • Babu
                      Hope you have checked the right code (in my example, first one is before tuning and second is after tuning). Consider I m in 3rd Page, so @PageNo=3, and
                      Message 10 of 11 , Dec 18, 2007
                        Hope you have checked the right code (in my example, first one is
                        before tuning and second is after tuning).

                        Consider I'm in 3rd Page, so @PageNo=3, and records per page is 10
                        (@RecPerPage=10), the inner select will be fetching TOP 30 (3 * 10)
                        records and the outer select will fetch records where RId (Row Id) >
                        20 (@StartRec = (@PageNo - 1) * @RecPerPage), that is the last 10
                        records.

                        ;WITH UsersTemp AS
                        (
                        SELECT TOP (@PageNo * @RecPerPage) UserId, UserName, ROW_NUMBER() OVER
                        (ORDER BY CreatedDate DESC) AS RId
                        FROM dbo.Users
                        WHERE CreatedDate BETWEEN @FromDate AND @Todate
                        )
                        SELECT *
                        FROM UsersTemp
                        WHERE RId > @StartRec

                        This example is using server-side paging.

                        You are suggesting client-side paging, and both are having its own
                        pros and cons. In an application with huge volume concurrent users,
                        it may not be practical, because we need huge memory to cache the
                        data in the web server.

                        So, choosing the option depends on your application.


                        Regards

                        Babu



                        --- In sqlcon@yahoogroups.com, sunil john <suniljk7@...> wrote:
                        >
                        > Dear Babu,
                        >
                        > I have one question on your stored proc for paging. if am
                        fetching a result set of say 10,0000. how much time it will talk to
                        store in the temp table or CTE, do you think that this is a good
                        method? for huge volume of data. Instead we can use a method of
                        getting the id's of all start position and return to front end and
                        from front end we can get the required page at required position. In
                        this cause u don't have to retrieve all the records unnecessarily.
                        correct me if am wrong.
                        >
                        > Best Regards
                        > Sunil John
                        >
                        > ----- Original Message ----
                        > From: Babu <babupp@...>
                        > To: sqlcon@yahoogroups.com
                        > Sent: Wednesday, November 28, 2007 3:38:23 PM
                        > Subject: MUGH-SQLCON Re: : Query Optimization
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        > 1. Execution plan vary on different machines, depending on number of
                        >
                        > CPUs, volume of memory, Disk Storage and also on volume of data in
                        >
                        > each tables.
                        >
                        > >> Ideally the execution plan should NOT vary from box to box.
                        >
                        > But it can vary based on the parallelism setting and the amount of
                        data.
                        >
                        > So make sure that the box you are doing performance testing is of
                        same
                        >
                        > configuration
                        >
                        > as in the Production Box, because if you tune a proc and get a good
                        >
                        > plan,
                        >
                        > and if you are not getting the same plan in production, then the
                        >
                        > exercise is
                        >
                        > waste.
                        >
                        >
                        >
                        > My Response:
                        >
                        >
                        >
                        > Execution plan varies not only on the degree of parallelism, but
                        also on
                        >
                        > proportion of data in joining tables too. When the volume of data
                        >
                        > chyanges, Joining order may change, Joining type could be different
                        and
                        >
                        > data access method also may change.
                        >
                        >
                        >
                        > As far as my current project is concerned, we have started tuning
                        the
                        >
                        > SPs on our workstations (Dual Core CPU with 1 GB RAM) and volume of
                        data
                        >
                        > is 4GB and then on our Development Servers (2 x Dual Core CPU with
                        4 GB
                        >
                        > RAM) with 12 GB data and finally on Pre-production servers which is
                        >
                        > having the same configuration of production servers (2 x Quad Core
                        CPU
                        >
                        > with 16 GB RAM) and is having more than 90 GB data.
                        >
                        >
                        >
                        > I have seen different execution plans on each machines. If in one
                        >
                        > procedure if the first statement is consuming more time, in another
                        >
                        > machine it could be the second statement.
                        >
                        >
                        >
                        > Still I don't think testing and tuning on different configuration is
                        >
                        > waste of time. In the above case we shall be tuning both the
                        >
                        > statements, and it could be beneficial for the overall performance.
                        >
                        >
                        >
                        > In our production serverthe CPU utilization was nearly 100% and we
                        were
                        >
                        > able to make it to come down to less than 70%.
                        >
                        >
                        >
                        > ------------ --------- --------- --------- -------
                        >
                        >
                        >
                        > 2. Removing an index also could give you improved performance. Index
                        >
                        > on a bit column can lead processing huge volume data than necessary.
                        >
                        > >>Removing an index will not improve the performance of a SELECT.
                        >
                        > Engine chose to seek an index only if it is selective enough.
                        >
                        > There are several scenarios where an index on a bit column can
                        improve
                        >
                        > performance.
                        >
                        >
                        >
                        > My Response:
                        >
                        >
                        >
                        > In one case I found that, one read operation is processing 1.5 Lac
                        >
                        > records from a table where I need less than 5000 records
                        >
                        >
                        >
                        > for my read operation. This was because of an index on a bit
                        column.
                        >
                        > This case even though the index is not selective
                        >
                        >
                        >
                        > enough, but found to be used by the query optmizer.
                        >
                        >
                        >
                        > I think an index on a bit column alone is not at all a good idea,
                        where
                        >
                        > the volume of data is high.
                        >
                        >
                        >
                        > The above is all about the Read performance. You may consider the
                        Write
                        >
                        > performance too while designing your indexes. Over
                        >
                        >
                        >
                        > -indexing could lead to degraded performance. We have to consider
                        the
                        >
                        > system resources too which is common for both Read and
                        >
                        >
                        >
                        > Write operations.
                        >
                        >
                        >
                        > ------------ --------- --------- --------- -------
                        >
                        >
                        >
                        > 3. Try to eliminate Temp. tables as much as possible. Use CTE
                        >
                        > instead.
                        >
                        > >> Why? CTE is not a replacement for Temp table. A non recursive
                        CTE is
                        >
                        > nothing
                        >
                        > but a derived table.
                        >
                        >
                        >
                        > My Response:
                        >
                        >
                        >
                        > I have re-written many procedures (Eg. procedures retrieving data
                        for
                        >
                        > paging) eliminating more than one temp. tables. I'm
                        >
                        >
                        >
                        > not replacing temp. tables with CTE, but achieving the same result
                        >
                        > without any temp tables.
                        >
                        >
                        >
                        > See a simple example below..
                        >
                        >
                        >
                        > --These procedures are just to show the use of CTE replacing Temp.
                        >
                        > table.
                        >
                        >
                        >
                        > -- These procedures retrieve record for list of users page-wise
                        >
                        >
                        >
                        > -- Say for Page 2, 10 records per page, it is required list records
                        from
                        >
                        > 11 to 20
                        >
                        >
                        >
                        > /*********** ********* ********* ******* Before
                        >
                        > ************ ********* ********* ********* ***/
                        >
                        >
                        >
                        > CREATE PROCEDURE UsersForPaging
                        >
                        >
                        >
                        > @PageNo INT, -- Current Page
                        >
                        >
                        >
                        > @RecPerPage INT -- Records per page
                        >
                        >
                        >
                        > AS
                        >
                        >
                        >
                        > BEGIN
                        >
                        >
                        >
                        > SET NOCOUNT ON
                        >
                        >
                        >
                        > SET @StartRec = (@PageNo - 1) * @RecPerPage
                        >
                        >
                        >
                        > CREATE TABLE #Users
                        >
                        >
                        >
                        > (
                        >
                        >
                        >
                        > Id INT IDENTITY,
                        >
                        >
                        >
                        > UserId INT,
                        >
                        >
                        >
                        > UserName VARCHAR(50)
                        >
                        >
                        >
                        > )
                        >
                        >
                        >
                        > INSERT INTO #Users (UserId, UserName)
                        >
                        >
                        >
                        > SELECT UserId, UserName
                        >
                        >
                        >
                        > FROM dbo.Users
                        >
                        >
                        >
                        > WHERE CreatedDate BETWEEN @FromDate AND @Todate
                        >
                        >
                        >
                        > ORDER BY CreatedDate DESC
                        >
                        >
                        >
                        > SELECT TOP (@RecPerPage) *
                        >
                        >
                        >
                        > FROM #Users
                        >
                        >
                        >
                        > WHERE Id > @StartRec
                        >
                        >
                        >
                        > END
                        >
                        >
                        >
                        > /*********** ********* ********* ******* After
                        >
                        > ************ ********* ********* ********* ***/
                        >
                        >
                        >
                        > CREATE PROCEDURE UsersForPaging
                        >
                        >
                        >
                        > @PageNo INT, -- Current Page
                        >
                        >
                        >
                        > @RecPerPage INT -- Records per page
                        >
                        >
                        >
                        > AS
                        >
                        >
                        >
                        > BEGIN
                        >
                        >
                        >
                        > SET NOCOUNT ON
                        >
                        >
                        >
                        > SET @StartRec = (@PageNo - 1) * @RecPerPage
                        >
                        >
                        >
                        > ;WITH UsersTemp AS
                        >
                        >
                        >
                        > (
                        >
                        >
                        >
                        > SELECT TOP (@PageNo * @RecPerPage) UserId, UserName, ROW_NUMBER()
                        OVER
                        >
                        > (ORDER BY CreatedDate DESC) AS RId
                        >
                        >
                        >
                        > FROM dbo.Users
                        >
                        >
                        >
                        > WHERE CreatedDate BETWEEN @FromDate AND @Todate
                        >
                        >
                        >
                        > )
                        >
                        >
                        >
                        > SELECT *
                        >
                        >
                        >
                        > FROM UsersTemp
                        >
                        >
                        >
                        > WHERE RId > @StartRec
                        >
                        >
                        >
                        > END
                        >
                        >
                        >
                        > /*********** ********* ********* ********* ********* *********
                        ********* ******\
                        >
                        > ************ ********* /
                        >
                        >
                        >
                        > The above procedure is a good sample for "Paging" (I feel). But
                        can be
                        >
                        > used only with SQL 2005.
                        >
                        >
                        >
                        > HTH,
                        >
                        >
                        >
                        > Babu
                        >
                        >
                        >
                        > --- In sqlcon@yahoogroups. com, "Roji P Thomas" <thomasroji@ ...>
                        wrote:
                        >
                        > >
                        >
                        > > Sorry, but I will challenge these points.
                        >
                        > >
                        >
                        > > 1. Execution plan vary on different machines, depending on number
                        of
                        >
                        > > CPUs, volume of memory, Disk Storage and also on volume of data in
                        >
                        > > each tables.
                        >
                        > > >> Ideally the execution plan should NOT vary from box to box.
                        >
                        > > But it can vary based on the parallelism setting and the amount of
                        >
                        > data.
                        >
                        > > So make sure that the box you are doing performance testing is of
                        same
                        >
                        > configuration
                        >
                        > > as in the Production Box, because if you tune a proc and get a
                        good
                        >
                        > plan,
                        >
                        > > and if you are not getting the same plan in production, then the
                        >
                        > exercise is waste.
                        >
                        > >
                        >
                        > > 2. Removing an index also could give you improved performance.
                        Index
                        >
                        > > on a bit column can lead processing huge volume data than
                        necessary.
                        >
                        > > >>Removing an index will not improve the performance of a SELECT.
                        >
                        > > Engine chose to seek an index only if it is selective enough.
                        >
                        > > There are several scenarios where an index on a bit column can
                        improve
                        >
                        > performance.
                        >
                        > >
                        >
                        > > 3. Try to eliminate Temp. tables as much as possible. Use CTE
                        >
                        > > instead.
                        >
                        > > >> Why? CTE is not a replacement for Temp table. A non recursive
                        CTE
                        >
                        > is nothing but a derived table.
                        >
                        > >
                        >
                        > >
                        >
                        > >
                        >
                        > > ----- Original Message -----
                        >
                        > > From: Babu
                        >
                        > > To: sqlcon@yahoogroups. com
                        >
                        > > Sent: Tuesday, November 27, 2007 7:28 PM
                        >
                        > > Subject: MUGH-SQLCON Re: : Query Optimization
                        >
                        > >
                        >
                        > >
                        >
                        > > 1) For optimize stored procedure : should "all" the errow in all
                        >
                        > > queries be thin.(not thik)
                        >
                        > >
                        >
                        > > It depends on the volume of data you are processing in your query.
                        >
                        > > Make sure you are fetching only the required rows and not more
                        than
                        >
                        > > that.
                        >
                        > >
                        >
                        > > Many cases I found that due to missing filters or wrong indexes,
                        it
                        >
                        > > is processing the entire data from tables. Pointing your mouse on
                        >
                        > > this arrow will tell you the number of rows read from this table.
                        >
                        > > Check whether it is reading more data than necessary. Always try
                        to
                        >
                        > > make the arrows as thin as possible.
                        >
                        > >
                        >
                        > > 2) The query cost lets say is 60% for one query and maximum is on
                        >
                        > > one table. how much is shold reduce. because if i am not wrong the
                        >
                        > > overall query cose (for all the queries in the SP) would be 100%
                        is
                        >
                        > > any case?
                        >
                        > >
                        >
                        > > This also depends. There is no bench mark for this, only I can
                        say.
                        >
                        > > The execution time 100% is for the whole SP, and when you reduce
                        the
                        >
                        > > cost of one statement, you can see other statement cost increases,
                        >
                        > > but the overall execution time will be reduced. Your ultimate aim
                        is
                        >
                        > > to reduce the overall execution time.
                        >
                        > >
                        >
                        > > Run SQL Profiler and see the "Reads", "CPU" and "Duration" while
                        >
                        > > executing procedure (ensure to disable Show Exeuction plan,
                        otherwise
                        >
                        > > results vary significantly) . See the initial values i.e., before
                        >
                        > > tuning. I suggest you to keep an initial target of Reads < 10,000,
                        >
                        > > and Duration < 1 Sec (1000 ms). But for a procedure which process
                        >
                        > > large volume data the reads could vary to a great extend.
                        >
                        > >
                        >
                        > > Compare the "Reads", "CPU" and "Duration" before and after your
                        >
                        > > modifications.
                        >
                        > >
                        >
                        > > Note: 1. Execution plan vary on different machines, depending on
                        >
                        > number of
                        >
                        > > CPUs, volume of memory, Disk Storage and also on volume of data in
                        >
                        > > each tables.
                        >
                        > >
                        >
                        > > 2. Removing an index also could give you improved performance.
                        Index
                        >
                        > > on a bit column can lead processing huge volume data than
                        necessary.
                        >
                        > >
                        >
                        > > 3. Try to eliminate Temp. tables as much as possible. Use CTE
                        >
                        > > instead.
                        >
                        > >
                        >
                        > >
                        >
                        > > HTH,
                        >
                        > >
                        >
                        > > Babu
                        >
                        > >
                        >
                        > > --- In sqlcon@yahoogroups. com, Deepak Garg deepakgrg@ wrote:
                        >
                        > > >
                        >
                        > > > Babu,
                        >
                        > > >
                        >
                        > > > I have few doubts here:
                        >
                        > > >
                        >
                        > > > 1) For optimize stored procedure : should "all" the errow in all
                        >
                        > > queries be thin.(not thik)
                        >
                        > > > 2) The query cost lets say is 60% for one query and maximum is
                        on
                        >
                        > > one table. how much is shold reduce. because if i am not wrong the
                        >
                        > > overall query cose (for all the queries in the SP) would be 100%
                        is
                        >
                        > > any case?
                        >
                        > > >
                        >
                        > > > Please assist.
                        >
                        > > > and thanks a lot again for your below valuable suggations.
                        >
                        > > >
                        >
                        > > > regards
                        >
                        > > > Deepak
                        >
                        > > >
                        >
                        > > >
                        >
                        > > > Babu babupp@ wrote:
                        >
                        > > > I have been working on SP Tuning / Optimization quite
                        >
                        > > some time. I
                        >
                        > > > haven't used the Index Tuning Wizard, since it is not giving the
                        >
                        > > > required output or output in useful/easy to understand format.
                        >
                        > > >
                        >
                        > > > I suggest you to do the tuning manually, since it is fast and
                        more
                        >
                        > > > reliable. All you need is the SQL Server Management Studio.
                        >
                        > > >
                        >
                        > > > First and foremost I would like to tell you that, adding an
                        index
                        >
                        > > may
                        >
                        > > > not solve your problem. I hope your table design include a
                        >
                        > > Clustered
                        >
                        > > > Index, possibly on Unique Column(s) and other Non-clustered
                        indexes.
                        >
                        > > >
                        >
                        > > > 1. Execute your query with actual parameters from Management
                        >
                        > > Studio,
                        >
                        > > > by setting the "Include Actual Execution Plan" On (this button
                        is
                        >
                        > > > visible on Toolbar)
                        >
                        > > >
                        >
                        > > > 2. Execution plan will be shown for each statementin the SP, in
                        >
                        > > > blocks.
                        >
                        > > >
                        >
                        > > > 3. For each block, the percentage of elapsed time will be shown.
                        >
                        > > > From this you can find which statement is consuming most of the
                        >
                        > > > execution time. Eg. if there are 5 statements in your procedure,
                        >
                        > > you
                        >
                        > > > may see that 3rd statement is consuming 90% of the execution
                        time
                        >
                        > > and
                        >
                        > > > the rest 4 altogether only 10%.
                        >
                        > > >
                        >
                        > > > 4. Now you can consider the 3rd statement as candidate first-
                        hand.
                        >
                        > > >
                        >
                        > > > 5. Check the block (here 3rd one) in your execution plan.
                        >
                        > > >
                        >
                        > > > 6. In this 3rd statement if you are joining 3 tables, say T1, T2
                        >
                        > > and
                        >
                        > > > T3, and the time consumed on each table will be shown. Eg. T1
                        (7%),
                        >
                        > > > T2 (86%) and T3 (7%).
                        >
                        > > >
                        >
                        > > > 7. From this we can understand that the main bottle-neck in you
                        SP
                        >
                        > > is
                        >
                        > > > the table T2 which is consuming 86% of the time for this
                        statement,
                        >
                        > > > and you may need to act on this table first.
                        >
                        > > >
                        >
                        > > > Now consider your 3rd statement in the SP.
                        >
                        > > >
                        >
                        > > > 1. Consider the way you joined the tables, not the order in
                        which
                        >
                        > > you
                        >
                        > > > have joined, but,
                        >
                        > > >
                        >
                        > > > (a) Do you really need all the three tables in your join
                        >
                        > > > condition ? Check whether the data is available within the other
                        >
                        > > > two tables itself.
                        >
                        > > >
                        >
                        > > > 2. Check the "Arrow" mark indicating the join of each table. Is
                        it
                        >
                        > > > very thick ?
                        >
                        > > >
                        >
                        > > > (a) A thick arrow indicates that a huge number of rows are being
                        >
                        > > > retrieved from this table.
                        >
                        > > >
                        >
                        > > > (b) Do you really need all these rows. Is it possible to reduce
                        >
                        > > > the number of rows by adding some more filters (conditions in
                        where
                        >
                        > > > clause) ?
                        >
                        > > >
                        >
                        > > > 3. Now see the type of operation done on each table. Most
                        preferred
                        >
                        > > > action is an Index Seek.
                        >
                        > > >
                        >
                        > > > (a) If it is a Table Scan or Clustered Index Scan I suggest you
                        may
                        >
                        > > > need an index here.
                        >
                        > > >
                        >
                        > > > (b) Even though there is an index available, and the index is
                        not
                        >
                        > > > being used or it is doing an index scan instead of index seek,
                        >
                        > > > consider
                        >
                        > > >
                        >
                        > > > (i) Check the possiblity of adding additional column filters as
                        >
                        > > > mentioned in 2(b) above. If the volume of data is high, query
                        >
                        > > > optimizer may choose to go for Index Scan instead of index seek.
                        >
                        > > >
                        >
                        > > > (ii) Is your index columns are in the same order which you are
                        >
                        > > > selecting, and whether the columns in where condition is
                        included
                        >
                        > > in
                        >
                        > > > your index.
                        >
                        > > >
                        >
                        > > > This case you may need to add a new index. If you are trying to
                        >
                        > > > alter the existing one, ensure that it is not being used some
                        where
                        >
                        > > > else.
                        >
                        > > >
                        >
                        > > > If this procedure is used much frequently, consider creating a
                        >
                        > > > Covering Index.
                        >
                        > > >
                        >
                        > > > (This document is a draft one and incomplete. You may see a
                        >
                        > > detailed
                        >
                        > > > one in some blogs sooner. Request you all, your inputs and
                        >
                        > > > suggestions to make this a document)
                        >
                        > > >
                        >
                        > > > HTH,
                        >
                        > > >
                        >
                        > > > Babu
                        >
                        > > >
                        >
                        > > > --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@> wrote:
                        >
                        > > > >
                        >
                        > > > > Guys,
                        >
                        > > > >
                        >
                        > > > > i have used index tuning wizard in SQL 2000 a couple of
                        times. as
                        >
                        > > > i remember i recommends us on a table where should we create a
                        >
                        > > index.
                        >
                        > > > i need to do it for SQL 2005, DTA is there, i tried it but in
                        >
                        > > > recommendation. ..the grid is empty...might be im not using it
                        >
                        > > > properly or if there is any other way to check and recommend
                        index
                        >
                        > > on
                        >
                        > > > a table please let me know.
                        >
                        > > > >
                        >
                        > > > > as i have been involved in SP tuning, any quick tips would
                        also
                        >
                        > > > be helpful.
                        >
                        > > > > a quick response would be highly appreciated.
                        >
                        > > > >
                        >
                        > > > > Thanks,
                        >
                        > > > > deepak
                        >
                        > > > >
                        >
                        > > > >
                        >
                        > > > > ------------ --------- --------- ---
                        >
                        > > > > Never miss a thing. Make Yahoo your homepage.
                        >
                        > > > >
                        >
                        > > > > [Non-text portions of this message have been removed]
                        >
                        > > > >
                        >
                        > > >
                        >
                        > > >
                        >
                        > > >
                        >
                        > > >
                        >
                        > > >
                        >
                        > > >
                        >
                        > > > ------------ --------- --------- ---
                        >
                        > > > Be a better pen pal. Text or chat with friends inside Yahoo!
                        Mail.
                        >
                        > > See how.
                        >
                        > > >
                        >
                        > > > [Non-text portions of this message have been removed]
                        >
                        > > >
                        >
                        > >
                        >
                        > >
                        >
                        > >
                        >
                        > >
                        >
                        > >
                        >
                        > > [Non-text portions of this message have been removed]
                        >
                        > >
                        >
                        >
                        >
                        > [Non-text portions of this message have been removed]
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        > <!--
                        >
                        > #ygrp-mkp{
                        > border:1px solid #d8d8d8;font-family:Arial;margin:14px
                        0px;padding:0px 14px;}
                        > #ygrp-mkp hr{
                        > border:1px solid #d8d8d8;}
                        > #ygrp-mkp #hd{
                        > color:#628c2a;font-size:85%;font-weight:bold;line-
                        height:122%;margin:10px 0px;}
                        > #ygrp-mkp #ads{
                        > margin-bottom:10px;}
                        > #ygrp-mkp .ad{
                        > padding:0 0;}
                        > #ygrp-mkp .ad a{
                        > color:#0000ff;text-decoration:none;}
                        > -->
                        >
                        >
                        >
                        > <!--
                        >
                        > #ygrp-sponsor #ygrp-lc{
                        > font-family:Arial;}
                        > #ygrp-sponsor #ygrp-lc #hd{
                        > margin:10px 0px;font-weight:bold;font-size:78%;line-height:122%;}
                        > #ygrp-sponsor #ygrp-lc .ad{
                        > margin-bottom:10px;padding:0 0;}
                        > -->
                        >
                        >
                        >
                        > <!--
                        >
                        > #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean,
                        sans-serif;}
                        > #ygrp-mlmsg table {font-size:inherit;font:100%;}
                        > #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica,
                        clean, sans-serif;}
                        > #ygrp-mlmsg pre, code {font:115% monospace;}
                        > #ygrp-mlmsg * {line-height:1.22em;}
                        > #ygrp-text{
                        > font-family:Georgia;
                        > }
                        > #ygrp-text p{
                        > margin:0 0 1em 0;}
                        > #ygrp-tpmsgs{
                        > font-family:Arial;
                        > clear:both;}
                        > #ygrp-vitnav{
                        > padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
                        > #ygrp-vitnav a{
                        > padding:0 1px;}
                        > #ygrp-actbar{
                        > clear:both;margin:25px 0;white-space:nowrap;color:#666;text-
                        align:right;}
                        > #ygrp-actbar .left{
                        > float:left;white-space:nowrap;}
                        > .bld{font-weight:bold;}
                        > #ygrp-grft{
                        > font-family:Verdana;font-size:77%;padding:15px 0;}
                        > #ygrp-ft{
                        > font-family:verdana;font-size:77%;border-top:1px solid #666;
                        > padding:5px 0;
                        > }
                        > #ygrp-mlmsg #logo{
                        > padding-bottom:10px;}
                        >
                        > #ygrp-vital{
                        > background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
                        > #ygrp-vital #vithd{
                        > font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-
                        transform:uppercase;}
                        > #ygrp-vital ul{
                        > padding:0;margin:2px 0;}
                        > #ygrp-vital ul li{
                        > list-style-type:none;clear:both;border:1px solid #e0ecee;
                        > }
                        > #ygrp-vital ul li .ct{
                        > font-weight:bold;color:#ff7900;float:right;width:2em;text-
                        align:right;padding-right:.5em;}
                        > #ygrp-vital ul li .cat{
                        > font-weight:bold;}
                        > #ygrp-vital a{
                        > text-decoration:none;}
                        >
                        > #ygrp-vital a:hover{
                        > text-decoration:underline;}
                        >
                        > #ygrp-sponsor #hd{
                        > color:#999;font-size:77%;}
                        > #ygrp-sponsor #ov{
                        > padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
                        > #ygrp-sponsor #ov ul{
                        > padding:0 0 0 8px;margin:0;}
                        > #ygrp-sponsor #ov li{
                        > list-style-type:square;padding:6px 0;font-size:77%;}
                        > #ygrp-sponsor #ov li a{
                        > text-decoration:none;font-size:130%;}
                        > #ygrp-sponsor #nc{
                        > background-color:#eee;margin-bottom:20px;padding:0 8px;}
                        > #ygrp-sponsor .ad{
                        > padding:8px 0;}
                        > #ygrp-sponsor .ad #hd1{
                        > font-family:Arial;font-weight:bold;color:#628c2a;font-
                        size:100%;line-height:122%;}
                        > #ygrp-sponsor .ad a{
                        > text-decoration:none;}
                        > #ygrp-sponsor .ad a:hover{
                        > text-decoration:underline;}
                        > #ygrp-sponsor .ad p{
                        > margin:0;}
                        > o{font-size:0;}
                        > .MsoNormal{
                        > margin:0 0 0 0;}
                        > #ygrp-text tt{
                        > font-size:120%;}
                        > blockquote{margin:0 0 0 4px;}
                        > .replbq{margin:4;}
                        > -->
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        > Send instant messages to your online friends
                        http://uk.messenger.yahoo.com
                        >
                        > [Non-text portions of this message have been removed]
                        >
                      Your message has been successfully submitted and would be delivered to recipients shortly.