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

: Query Optimization

Expand Messages
  • Deepak Garg
    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
    Message 1 of 11 , Nov 22, 2007
    • 0 Attachment
      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]
    • 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 2 of 11 , Nov 22, 2007
      • 0 Attachment
        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 3 of 11 , Nov 22, 2007
        • 0 Attachment
          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 4 of 11 , Nov 27, 2007
          • 0 Attachment
            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 5 of 11 , Nov 27, 2007
            • 0 Attachment
              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 6 of 11 , Nov 27, 2007
              • 0 Attachment
                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 7 of 11 , Nov 27, 2007
                • 0 Attachment
                  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 8 of 11 , Nov 28, 2007
                  • 0 Attachment
                    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 9 of 11 , Nov 29, 2007
                    • 0 Attachment
                      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 10 of 11 , Dec 15, 2007
                      • 0 Attachment
                        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 11 of 11 , Dec 18, 2007
                        • 0 Attachment
                          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.