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

Re: [mugh-sqlcon] Higher fragmentation in datapages

Expand Messages
  • smart chip
    Preet, the table lot_lit_detail doesn t have a clustered index. Please send a dbcc showcontig result of the table having clustered index. I remember u said
    Message 1 of 15 , Jan 31, 2006
    • 0 Attachment
      Preet,

      the table 'lot_lit_detail'doesn't have a clustered index. Please send a dbcc showcontig result of the table having clustered index. I remember u said that ur tables have clustered index. but this dbcc result should be from a heap.



      Regards,
      Kalyan


      Preet Sharma <mpreetsharma@...> wrote:
      Yes Kalyan

      This is the result coming form the showcontig.
      Also my san is dedicated to sql server datafiles only.

      Given is the result by showcontig. DBCC
      SHOCONTIG(lot_lit_detail)
      DBCC SHOWCONTIG scanning 'lot_lit_detail' table...
      Table: 'lot_lit_detail' (535672956); index ID: 0,
      database ID: 20
      TABLE level scan performed.
      - Pages Scanned................................: 9103
      - Extents Scanned..............................: 8570
      - Extent Switches..............................: 8569
      - Avg. Pages per Extent........................: 1.1
      - Scan Density [Best Count:Actual Count].......:
      13.28% [1138:8570]
      - Extent Scan Fragmentation ...................:
      80.30%
      - Avg. Bytes Free per Page.....................:
      6001.4
      - Avg. Page Density (full).....................:
      25.85%
      DBCC execution completed. If DBCC printed error
      messages, contact your system administrator.

      Hope this will help you to understood the problem.

      RGDS
      Mohit


      --- smart chip <knowledge_chip@...> wrote:


      ---------------------------------
      Mohit,

      It is good to share this among our dba friends.

      First of all how did u find this 15-20% usage? (is
      it dbcc showcontig result?)
      Is your san drives dedicated only to SQL server?
      Is this problem suddenly arised?
      What do your sys admin says about this?

      If your databases are doing any delete operations on
      text and image columns. If it is true there is a
      chance that sql server does not release that space
      after deleting that data. I am not sure wether this
      cause that much fragmentation or not. But this issue
      exists in sql 2000. There us a Kb article about this
      http://support.microsoft.com/default.aspx/kb/324432

      otherwise how that fragmentaion arises??



      Kalyan

      Preet Sharma <mpreetsharma@...> wrote:
      Dear Kalyan

      Thanks for the article. That what i was talking about
      in my previous mail and would like to share with my
      dba friends.

      I am maintaing 34 large Databases on the san
      configured with multiple filegroups across the drives.
      The thing extenral fragmenation i am talking about is
      can also be my guess But u see as i am maintaing table
      sturucters without clustered indexes and inthis case
      such high fragmentation should not be find b'cos it
      cab be natural in only in case of clustered index. But
      here its showing high fragmenation on datapages such
      as 15-20% usage in maximum cases which does not make
      any sense and above all defrag commands and rebuilding
      indexes are noway helping me out. Its just the select
      * itno helps me where i am creating a new table and
      then putting the required indexs on it.

      But obviously its also not the right solution in OLTP.




      Mohit

      --- smart chip <knowledge_chip@...> wrote:


      ---------------------------------
      Preet,

      this is Interesting

      Did you checked out this?


      http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx

      this will give some info. But for your case running
      defragmentaion on OS level is not possible (I think).

      I would also want to know hoe your database is
      spread accross multiple disks. If it is spread accross
      mutiple disks. That will probabaly should not be
      heavly fragmented at OS level.

      What do you say?


      Regards
      Kalyan


      Preet Sharma <mpreetsharma@...> wrote:
      Dear Veer

      FIrst of all thank you very much for your fast reply.

      But as i have said that this problem is basically not
      that we face in our OLTP enviroment generally. But
      already here after putting all the methodologies i am
      not able to defrag the space on the pages. As its
      increasing overall size of my datafiles which exactly
      need only the 50%usage in it.What you are talking
      about it basically is the internal fragmentation of
      sql server. If you read my mail line by line then you
      will be able to know that all this commands are not
      anyway working and i have already implemented them.
      Here i let you put your kind attention towards the
      external fragmenation such as on o/s level. Because
      being in this profession for more than 4 years i faced
      this type of problem first time as the maintenance
      commands are not helping anyway.
      I just want to ask the fragmentation caused by the
      operating system, if you guys have ever faced, bcos
      that's why select *into seems to be helping in big way
      but this is not the solution for the 24*7 environment
      db, where we have rarely downtime for these type of
      issues and its even more tougher to convince seniors
      for this type of problem.

      Hope this time u are getting me.

      Thanks

      Mohit








      --- veer wangoo <vwangoo@...> wrote:


      ---------------------------------
      There are two wasy to solve this problem one is pro
      Active and other one reactive.

      ProActive tasks to solve such problem are

      1.

      When You take regular back upalways try to involve
      Databases health Checking and rectfying DBCCs like as
      you said indexdefrag or update statistics.

      2.

      when you design a database make sure the amount of
      data that one row is going to hold and do proper
      analysis based on page Split and Fill factor.

      Now that you have limitation in above two methods
      but I would suggest you carry up the first point on
      weekend.

      REACTIVE APPROACH

      1. Changing the Fill factor (Which I see is not a
      good practice in prod Server unless very important)


      Now lets see the four ways of defragging but before
      that it is important to understand the fragmentation
      information.

      "To view how much fragmentation an index has you use
      DBCC SHOWCONTIG. It can show information for all
      indexes on a table or just a single index at a time.
      Using the option TABLERESULTS you get extra columns in
      the output that describe statistics about the index.
      By default DBCC SHOWCONTIG scans the page chain at the
      leaf level, but it is possible to scan all levels of
      an index.
      When reviewing the output from DBCC SHOWCONTIG you
      should pay special attention to the following metrics:

      Avg. Page Density (full): Shows the average level
      of how filled the pages are. A percentage means the
      pages are almost full, and a low indicates much free
      space. This value should be compared to the fill
      factor setting specified when the index was created to
      decide whether or not the index is internally
      fragmented.
      Scan Density: Shows the ratio between the Best
      Count of extents that should be necessary to read when
      scanning all the pages of the index, and the Actual
      Count of extents that was read. This percentage should
      be as close to 100% as possible. Defining an
      acceptable level is difficult, but anything under 75%
      definitely indicates external fragmentation.
      Logical Scan Fragmentation: Shows the ratio of
      pages that are out of logical order. The value should
      be as close to 0% as possible and anything over 10%
      indicates external fragmentation.
      Note: The value for Extent Scan Fragmentation is not
      really important. This is because the algorithm that
      calculates it does not work for indexes that span over
      multiple files. According to some Microsoft sources,
      in the next version of SQL Server, DBCC SHOWCONTIG
      will not even include this metric. "

      Having said the Above.... lets examine the four ways
      of defragging the Table

      There are four different ways to defragment an
      index.

      "
      Drop and recreate index
      Pros: Completely rebuilds the index. Reorders and
      compacts the index pages, and removes unnecessary
      pages.
      Cons: Index is not available during process which
      means that queries will suffer. The process will block
      all queries accessing the index and also be blocked
      itself by other processes using the index, unable to
      do it’s work until those processes are finished. This
      option is especially bad for clustered indexes as
      dropping a clustered index means that all
      non-clustered indexes must be rebuilt (to use RID as
      bookmark), and then recreating the clustered index
      will once again force a rebuild of all non-clustered
      indexes.

      CREATE INDEX ... WITH DROP_EXISTING
      Pros: Rebuilds index in one step which is good for
      clustered indexes, as it means that non-clustered
      indexes do not need to be rebuilt. Can be used to
      recreate (and thereby defragment) indexes created by
      constraints, if the index definition matches the
      requirements of the constraint.
      Cons: Potential blocking problems with other processes
      in the same way as for drop and recreate index above.

      DBCC DBREINDEX
      Pros: Does the best job of removing internal and
      external fragmentation since it physically assigns new
      pages to the index and moves data to them. Has the
      possibility to rebuild all indexes on a table using
      one statement. Recreates indexes for constraints
      without forcing you to know their requirements.
      Cons: The same blocking issues as for the two above.
      Runs in a transaction so all work that has been done
      is rolled back if the command is stopped.

      DBCC INDEXDEFRAG
      Pros: Reorders the leaf pages in index order –
      enhancing performance for scans especially – and
      compacts them using the setting specified for fill
      factor when the index was created. Empty pages are
      removed. This is an online operation, meaning it does
      not take long-term locks and thereby do not block
      other processes and do not get blocked itself by them.
      Work that has already been done will still be done if
      the command is cancelled.
      Cons: The fact that this is an online operation can
      also have a negative impact, as this means that DBCC
      INDEXDEFRAG will simply skip pages that are used by
      other processes. Very fragmented indexes will take a
      long time to defragment, so they should probably be
      rebuilt instead if possible. Generates a lot of log
      activity. "


      But I would still suggest the best proactive way is
      STEP 1







      Preet Sharma <mpreetsharma@...> wrote:
      Hi Guys!

      This is the first time i am writing email to you. As i
      feel this group is really impressive and helpful for
      our breed.

      So here is the problem which is related to higher
      level of fragmentation in the datapages/extent level
      on the non clustered tables(tables without clustered
      index). I have used all the available option to remove
      this fragmentation like DBCC reindex,dropping/creating
      indexes but all in vain but one thing really helped me
      was the select * into statement, in this case also
      after being again on live database it encounters the
      same problem after day or two when putting shwocontig.
      But being in live you cant do this exerciese like we
      do for our scheduling of jobs for indexdefrag or
      update statistics.

      Pls. suggest me the the other options to encounter
      this problem as this is the definite cause of the bad
      performance for browsing several thousands pages by
      sql server for few hundred rows, as mine case its
      happening. if you have any queries related to this pls
      let me know.

      looking forward to hear from you soon.

      Tons of thanks in advance.


      Regards
      Mohit Sharma
      Contact#91-9810204418

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


      SqlCon is a special interests group run under the
      aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User
      Group.




      ---------------------------------
      YAHOO! GROUPS LINKS


      Visit your group "sqlcon" on the web.

      To unsubscribe from this group, send an email to:
      sqlcon-unsubscribe@yahoogroups.com

      Your use of Yahoo! Groups is subject to the Yahoo!
      Terms of Service.


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






      ---------------------------------
      Yahoo! Autos. Looking for a sweet ride? Get pricing,
      reviews, & more on new and used cars.

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



      SqlCon is a special interests group run under the
      aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User
      Group.




      ---------------------------------
      YAHOO! GROUPS LINKS


      Visit your group "sqlcon" on the web.

      To unsubscribe from this group, send an email to:
      sqlcon-unsubscribe@yahoogroups.com

      Your use of Yahoo! Groups is subject to the Yahoo!
      Terms of Service.


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






      __________________________________________________________

      Yahoo! India Matrimony: Find your partner now. Go to
      http://yahoo.shaadi.com


      SqlCon is a special interests group run under the
      aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User
      Group.




      SPONSORED LINKS
      C programming language Computer programming
      languages Java programming language The c
      programming language C programming language
      Concept of programming language

      ---------------------------------
      YAHOO! GROUPS LINKS


      Visit your group "sqlcon" on the web.

      To unsubscribe from this group, send an email to:
      sqlcon-unsubscribe@yahoogroups.com

      Your use of Yahoo! Groups is subject to the Yahoo!
      Terms of Service.


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






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

      What are the most popular cars? Find out at Yahoo!
      Autos

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



      SqlCon is a special interests group run under the
      aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User
      Group.




      SPONSORED LINKS
      C
      programming language
      Computer programming languages
      Java programming language
      The c
      programming language
      C programming language
      Concept of programming language


      ---------------------------------
      YAHOO! GROUPS LINKS


      Visit your group "sqlcon" on the web.

      To unsubscribe from this group, send an email to:
      sqlcon-unsubscribe@yahoogroups.com

      Your use of Yahoo! Groups is subject to the Yahoo!
      Terms of Service.


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






      __________________________________________________________

      Yahoo! India Matrimony: Find your partner now. Go to
      http://yahoo.shaadi.com


      SqlCon is a special interests group run under the
      aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User
      Group.




      SPONSORED LINKS
      C programming language Computer programming
      languages Java programming language The c
      programming language C programming language
      Concept of programming language

      ---------------------------------
      YAHOO! GROUPS LINKS


      Visit your group "sqlcon" on the web.

      To unsubscribe from this group, send an email to:
      sqlcon-unsubscribe@yahoogroups.com

      Your use of Yahoo! Groups is subject to the Yahoo!
      Terms of Service.


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






      ---------------------------------
      Do you Yahoo!?
      With a free 1 GB, there's more in store with Yahoo!
      Mail.

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



      SqlCon is a special interests group run under the
      aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User
      Group.




      ---------------------------------
      YAHOO! GROUPS LINKS


      Visit your group "sqlcon" on the web.

      To unsubscribe from this group, send an email to:
      sqlcon-unsubscribe@yahoogroups.com

      Your use of Yahoo! Groups is subject to the Yahoo!
      Terms of Service.


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






      __________________________________________________________
      Yahoo! India Matrimony: Find your partner now. Go to http://yahoo.shaadi.com


      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User Group.




      ---------------------------------
      YAHOO! GROUPS LINKS


      Visit your group "sqlcon" on the web.

      To unsubscribe from this group, send an email to:
      sqlcon-unsubscribe@yahoogroups.com

      Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


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






      ---------------------------------
      Do you Yahoo!?
      With a free 1 GB, there's more in store with Yahoo! Mail.

      [Non-text portions of this message have been removed]
    • Preet Sharma
      Dear Kalyan I never said my tables are clustered, so naturally no clustered index is there.And even then i am getting so much of fragmentation, that i feel
      Message 2 of 15 , Feb 1, 2006
      • 0 Attachment
        Dear Kalyan

        I never said my tables are clustered, so naturally no
        clustered index is there.And even then i am getting so
        much of fragmentation, that i feel happens in the case
        if you have the clustered on your table.

        but my concern is towards the fragmentation on my
        tables without clustered on it.

        mohit

        --- smart chip <knowledge_chip@...> wrote:


        ---------------------------------
        Preet,

        the table 'lot_lit_detail'doesn't have a clustered
        index. Please send a dbcc showcontig result of the
        table having clustered index. I remember u said that
        ur tables have clustered index. but this dbcc result
        should be from a heap.



        Regards,
        Kalyan


        Preet Sharma <mpreetsharma@...> wrote:
        Yes Kalyan

        This is the result coming form the showcontig.
        Also my san is dedicated to sql server datafiles only.

        Given is the result by showcontig. DBCC
        SHOCONTIG(lot_lit_detail)
        DBCC SHOWCONTIG scanning 'lot_lit_detail' table...
        Table: 'lot_lit_detail' (535672956); index ID: 0,
        database ID: 20
        TABLE level scan performed.
        - Pages Scanned................................: 9103
        - Extents Scanned..............................: 8570
        - Extent Switches..............................: 8569
        - Avg. Pages per Extent........................: 1.1
        - Scan Density [Best Count:Actual Count].......:
        13.28% [1138:8570]
        - Extent Scan Fragmentation ...................:
        80.30%
        - Avg. Bytes Free per Page.....................:
        6001.4
        - Avg. Page Density (full).....................:
        25.85%
        DBCC execution completed. If DBCC printed error
        messages, contact your system administrator.

        Hope this will help you to understood the problem.

        RGDS
        Mohit


        --- smart chip <knowledge_chip@...> wrote:


        ---------------------------------
        Mohit,

        It is good to share this among our dba friends.

        First of all how did u find this 15-20% usage? (is
        it dbcc showcontig result?)
        Is your san drives dedicated only to SQL server?
        Is this problem suddenly arised?
        What do your sys admin says about this?

        If your databases are doing any delete operations on
        text and image columns. If it is true there is a
        chance that sql server does not release that space
        after deleting that data. I am not sure wether this
        cause that much fragmentation or not. But this issue
        exists in sql 2000. There us a Kb article about this
        http://support.microsoft.com/default.aspx/kb/324432

        otherwise how that fragmentaion arises??



        Kalyan

        Preet Sharma <mpreetsharma@...> wrote:
        Dear Kalyan

        Thanks for the article. That what i was talking about
        in my previous mail and would like to share with my
        dba friends.

        I am maintaing 34 large Databases on the san
        configured with multiple filegroups across the drives.
        The thing extenral fragmenation i am talking about is
        can also be my guess But u see as i am maintaing table
        sturucters without clustered indexes and inthis case
        such high fragmentation should not be find b'cos it
        cab be natural in only in case of clustered index. But
        here its showing high fragmenation on datapages such
        as 15-20% usage in maximum cases which does not make
        any sense and above all defrag commands and rebuilding
        indexes are noway helping me out. Its just the select
        * itno helps me where i am creating a new table and
        then putting the required indexs on it.

        But obviously its also not the right solution in OLTP.




        Mohit

        --- smart chip <knowledge_chip@...> wrote:


        ---------------------------------
        Preet,

        this is Interesting

        Did you checked out this?


        http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_083104.mspx

        this will give some info. But for your case running
        defragmentaion on OS level is not possible (I think).

        I would also want to know hoe your database is
        spread accross multiple disks. If it is spread accross
        mutiple disks. That will probabaly should not be
        heavly fragmented at OS level.

        What do you say?


        Regards
        Kalyan


        Preet Sharma <mpreetsharma@...> wrote:
        Dear Veer

        FIrst of all thank you very much for your fast reply.

        But as i have said that this problem is basically not
        that we face in our OLTP enviroment generally. But
        already here after putting all the methodologies i am
        not able to defrag the space on the pages. As its
        increasing overall size of my datafiles which exactly
        need only the 50%usage in it.What you are talking
        about it basically is the internal fragmentation of
        sql server. If you read my mail line by line then you
        will be able to know that all this commands are not
        anyway working and i have already implemented them.
        Here i let you put your kind attention towards the
        external fragmenation such as on o/s level. Because
        being in this profession for more than 4 years i faced
        this type of problem first time as the maintenance
        commands are not helping anyway.
        I just want to ask the fragmentation caused by the
        operating system, if you guys have ever faced, bcos
        that's why select *into seems to be helping in big way
        but this is not the solution for the 24*7 environment
        db, where we have rarely downtime for these type of
        issues and its even more tougher to convince seniors
        for this type of problem.

        Hope this time u are getting me.

        Thanks

        Mohit








        --- veer wangoo <vwangoo@...> wrote:


        ---------------------------------
        There are two wasy to solve this problem one is pro
        Active and other one reactive.

        ProActive tasks to solve such problem are

        1.

        When You take regular back upalways try to involve
        Databases health Checking and rectfying DBCCs like as
        you said indexdefrag or update statistics.

        2.

        when you design a database make sure the amount of
        data that one row is going to hold and do proper
        analysis based on page Split and Fill factor.

        Now that you have limitation in above two methods
        but I would suggest you carry up the first point on
        weekend.

        REACTIVE APPROACH

        1. Changing the Fill factor (Which I see is not a
        good practice in prod Server unless very important)


        Now lets see the four ways of defragging but before
        that it is important to understand the fragmentation
        information.

        "To view how much fragmentation an index has you use
        DBCC SHOWCONTIG. It can show information for all
        indexes on a table or just a single index at a time.
        Using the option TABLERESULTS you get extra columns in
        the output that describe statistics about the index.
        By default DBCC SHOWCONTIG scans the page chain at the
        leaf level, but it is possible to scan all levels of
        an index.
        When reviewing the output from DBCC SHOWCONTIG you
        should pay special attention to the following metrics:

        Avg. Page Density (full): Shows the average level
        of how filled the pages are. A percentage means the
        pages are almost full, and a low indicates much free
        space. This value should be compared to the fill
        factor setting specified when the index was created to
        decide whether or not the index is internally
        fragmented.
        Scan Density: Shows the ratio between the Best
        Count of extents that should be necessary to read when
        scanning all the pages of the index, and the Actual
        Count of extents that was read. This percentage should
        be as close to 100% as possible. Defining an
        acceptable level is difficult, but anything under 75%
        definitely indicates external fragmentation.
        Logical Scan Fragmentation: Shows the ratio of
        pages that are out of logical order. The value should
        be as close to 0% as possible and anything over 10%
        indicates external fragmentation.
        Note: The value for Extent Scan Fragmentation is not
        really important. This is because the algorithm that
        calculates it does not work for indexes that span over
        multiple files. According to some Microsoft sources,
        in the next version of SQL Server, DBCC SHOWCONTIG
        will not even include this metric. "

        Having said the Above.... lets examine the four ways
        of defragging the Table

        There are four different ways to defragment an
        index.

        "
        Drop and recreate index
        Pros: Completely rebuilds the index. Reorders and
        compacts the index pages, and removes unnecessary
        pages.
        Cons: Index is not available during process which
        means that queries will suffer. The process will block
        all queries accessing the index and also be blocked
        itself by other processes using the index, unable to
        do it’s work until those processes are finished. This
        option is especially bad for clustered indexes as
        dropping a clustered index means that all
        non-clustered indexes must be rebuilt (to use RID as
        bookmark), and then recreating the clustered index
        will once again force a rebuild of all non-clustered
        indexes.

        CREATE INDEX ... WITH DROP_EXISTING
        Pros: Rebuilds index in one step which is good for
        clustered indexes, as it means that non-clustered
        indexes do not need to be rebuilt. Can be used to
        recreate (and thereby defragment) indexes created by
        constraints, if the index definition matches the
        requirements of the constraint.
        Cons: Potential blocking problems with other processes
        in the same way as for drop and recreate index above.

        DBCC DBREINDEX
        Pros: Does the best job of removing internal and
        external fragmentation since it physically assigns new
        pages to the index and moves data to them. Has the
        possibility to rebuild all indexes on a table using
        one statement. Recreates indexes for constraints
        without forcing you to know their requirements.
        Cons: The same blocking issues as for the two above.
        Runs in a transaction so all work that has been done
        is rolled back if the command is stopped.

        DBCC INDEXDEFRAG
        Pros: Reorders the leaf pages in index order –
        enhancing performance for scans especially – and
        compacts them using the setting specified for fill
        factor when the index was created. Empty pages are
        removed. This is an online operation, meaning it does
        not take long-term locks and thereby do not block
        other processes and do not get blocked itself by them.
        Work that has already been done will still be done if
        the command is cancelled.
        Cons: The fact that this is an online operation can
        also have a negative impact, as this means that DBCC
        INDEXDEFRAG will simply skip pages that are used by
        other processes. Very fragmented indexes will take a
        long time to defragment, so they should probably be
        rebuilt instead if possible. Generates a lot of log
        activity. "


        But I would still suggest the best proactive way is
        STEP 1







        Preet Sharma <mpreetsharma@...> wrote:
        Hi Guys!

        This is the first time i am writing email to you. As i
        feel this group is really impressive and helpful for
        our breed.

        So here is the problem which is related to higher
        level of fragmentation in the datapages/extent level
        on the non clustered tables(tables without clustered
        index). I have used all the available option to remove
        this fragmentation like DBCC reindex,dropping/creating
        indexes but all in vain but one thing really helped me
        was the select * into statement, in this case also
        after being again on live database it encounters the
        same problem after day or two when putting shwocontig.
        But being in live you cant do this exerciese like we
        do for our scheduling of jobs for indexdefrag or
        update statistics.

        Pls. suggest me the the other options to encounter
        this problem as this is the definite cause of the bad
        performance for browsing several thousands pages by
        sql server for few hundred rows, as mine case its
        happening. if you have any queries related to this pls
        let me know.

        looking forward to hear from you soon.

        Tons of thanks in advance.


        Regards
        Mohit Sharma
        Contact#91-9810204418

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


        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






        ---------------------------------
        Yahoo! Autos. Looking for a sweet ride? Get pricing,
        reviews, & more on new and used cars.

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



        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






        __________________________________________________________

        Yahoo! India Matrimony: Find your partner now. Go to
        http://yahoo.shaadi.com


        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        SPONSORED LINKS
        C programming language Computer programming
        languages Java programming language The c
        programming language C programming language
        Concept of programming language

        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






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

        What are the most popular cars? Find out at Yahoo!
        Autos

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



        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        SPONSORED LINKS
        C
        programming language
        Computer programming languages
        Java programming language
        The c
        programming language
        C programming language
        Concept of programming language


        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






        __________________________________________________________

        Yahoo! India Matrimony: Find your partner now. Go to
        http://yahoo.shaadi.com


        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        SPONSORED LINKS
        C programming language Computer programming
        languages Java programming language The c
        programming language C programming language
        Concept of programming language

        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






        ---------------------------------
        Do you Yahoo!?
        With a free 1 GB, there's more in store with Yahoo!
        Mail.

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



        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






        __________________________________________________________

        Yahoo! India Matrimony: Find your partner now. Go to
        http://yahoo.shaadi.com


        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






        ---------------------------------
        Do you Yahoo!?
        With a free 1 GB, there's more in store with Yahoo!
        Mail.

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



        SqlCon is a special interests group run under the
        aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User
        Group.




        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo!
        Terms of Service.


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






        __________________________________________________________
        Yahoo! India Matrimony: Find your partner now. Go to http://yahoo.shaadi.com
      • Veer Ji Wangoo
        Sorry for getting looped out... I ll answer some more points on it ASAP.-----Original Message----- From: sqlcon@yahoogroups.com
        Message 3 of 15 , Feb 2, 2006
        • 0 Attachment
          Sorry for getting looped out... I ll answer some more points on it ASAP.

          -----Original Message-----
          From: sqlcon@yahoogroups.com [mailto:sqlcon@yahoogroups.com] On Behalf
          Of Preet Sharma
          Sent: Wednesday, February 01, 2006 10:29 PM
          To: sqlcon@yahoogroups.com
          Subject: Re: [mugh-sqlcon] Higher fragmentation in datapages

          Dear Kalyan

          I never said my tables are clustered, so naturally no
          clustered index is there.And even then i am getting so
          much of fragmentation, that i feel happens in the case
          if you have the clustered on your table.

          but my concern is towards the fragmentation on my
          tables without clustered on it.

          mohit

          --- smart chip <knowledge_chip@...> wrote:


          ---------------------------------
          Preet,

          the table 'lot_lit_detail'doesn't have a clustered
          index. Please send a dbcc showcontig result of the
          table having clustered index. I remember u said that
          ur tables have clustered index. but this dbcc result
          should be from a heap.



          Regards,
          Kalyan


          Preet Sharma <mpreetsharma@...> wrote:
          Yes Kalyan

          This is the result coming form the showcontig.
          Also my san is dedicated to sql server datafiles only.

          Given is the result by showcontig. DBCC
          SHOCONTIG(lot_lit_detail)
          DBCC SHOWCONTIG scanning 'lot_lit_detail' table...
          Table: 'lot_lit_detail' (535672956); index ID: 0,
          database ID: 20
          TABLE level scan performed.
          - Pages Scanned................................: 9103
          - Extents Scanned..............................: 8570
          - Extent Switches..............................: 8569
          - Avg. Pages per Extent........................: 1.1
          - Scan Density [Best Count:Actual Count].......:
          13.28% [1138:8570]
          - Extent Scan Fragmentation ...................:
          80.30%
          - Avg. Bytes Free per Page.....................:
          6001.4
          - Avg. Page Density (full).....................:
          25.85%
          DBCC execution completed. If DBCC printed error
          messages, contact your system administrator.

          Hope this will help you to understood the problem.

          RGDS
          Mohit


          --- smart chip <knowledge_chip@...> wrote:


          ---------------------------------
          Mohit,

          It is good to share this among our dba friends.

          First of all how did u find this 15-20% usage? (is
          it dbcc showcontig result?)
          Is your san drives dedicated only to SQL server?
          Is this problem suddenly arised?
          What do your sys admin says about this?

          If your databases are doing any delete operations on
          text and image columns. If it is true there is a
          chance that sql server does not release that space
          after deleting that data. I am not sure wether this
          cause that much fragmentation or not. But this issue
          exists in sql 2000. There us a Kb article about this
          http://support.microsoft.com/default.aspx/kb/324432

          otherwise how that fragmentaion arises??



          Kalyan

          Preet Sharma <mpreetsharma@...> wrote:
          Dear Kalyan

          Thanks for the article. That what i was talking about
          in my previous mail and would like to share with my
          dba friends.

          I am maintaing 34 large Databases on the san
          configured with multiple filegroups across the drives.
          The thing extenral fragmenation i am talking about is
          can also be my guess But u see as i am maintaing table
          sturucters without clustered indexes and inthis case
          such high fragmentation should not be find b'cos it
          cab be natural in only in case of clustered index. But
          here its showing high fragmenation on datapages such
          as 15-20% usage in maximum cases which does not make
          any sense and above all defrag commands and rebuilding
          indexes are noway helping me out. Its just the select
          * itno helps me where i am creating a new table and
          then putting the required indexs on it.

          But obviously its also not the right solution in OLTP.




          Mohit

          --- smart chip <knowledge_chip@...> wrote:


          ---------------------------------
          Preet,

          this is Interesting

          Did you checked out this?


          http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_0
          83104.mspx

          this will give some info. But for your case running
          defragmentaion on OS level is not possible (I think).

          I would also want to know hoe your database is
          spread accross multiple disks. If it is spread accross
          mutiple disks. That will probabaly should not be
          heavly fragmented at OS level.

          What do you say?


          Regards
          Kalyan


          Preet Sharma <mpreetsharma@...> wrote:
          Dear Veer

          FIrst of all thank you very much for your fast reply.

          But as i have said that this problem is basically not
          that we face in our OLTP enviroment generally. But
          already here after putting all the methodologies i am
          not able to defrag the space on the pages. As its
          increasing overall size of my datafiles which exactly
          need only the 50%usage in it.What you are talking
          about it basically is the internal fragmentation of
          sql server. If you read my mail line by line then you
          will be able to know that all this commands are not
          anyway working and i have already implemented them.
          Here i let you put your kind attention towards the
          external fragmenation such as on o/s level. Because
          being in this profession for more than 4 years i faced
          this type of problem first time as the maintenance
          commands are not helping anyway.
          I just want to ask the fragmentation caused by the
          operating system, if you guys have ever faced, bcos
          that's why select *into seems to be helping in big way
          but this is not the solution for the 24*7 environment
          db, where we have rarely downtime for these type of
          issues and its even more tougher to convince seniors
          for this type of problem.

          Hope this time u are getting me.

          Thanks

          Mohit








          --- veer wangoo <vwangoo@...> wrote:


          ---------------------------------
          There are two wasy to solve this problem one is pro
          Active and other one reactive.

          ProActive tasks to solve such problem are

          1.

          When You take regular back upalways try to involve
          Databases health Checking and rectfying DBCCs like as
          you said indexdefrag or update statistics.

          2.

          when you design a database make sure the amount of
          data that one row is going to hold and do proper
          analysis based on page Split and Fill factor.

          Now that you have limitation in above two methods
          but I would suggest you carry up the first point on
          weekend.

          REACTIVE APPROACH

          1. Changing the Fill factor (Which I see is not a
          good practice in prod Server unless very important)


          Now lets see the four ways of defragging but before
          that it is important to understand the fragmentation
          information.

          "To view how much fragmentation an index has you use
          DBCC SHOWCONTIG. It can show information for all
          indexes on a table or just a single index at a time.
          Using the option TABLERESULTS you get extra columns in
          the output that describe statistics about the index.
          By default DBCC SHOWCONTIG scans the page chain at the
          leaf level, but it is possible to scan all levels of
          an index.
          When reviewing the output from DBCC SHOWCONTIG you
          should pay special attention to the following metrics:

          Avg. Page Density (full): Shows the average level
          of how filled the pages are. A percentage means the
          pages are almost full, and a low indicates much free
          space. This value should be compared to the fill
          factor setting specified when the index was created to
          decide whether or not the index is internally
          fragmented.
          Scan Density: Shows the ratio between the Best
          Count of extents that should be necessary to read when
          scanning all the pages of the index, and the Actual
          Count of extents that was read. This percentage should
          be as close to 100% as possible. Defining an
          acceptable level is difficult, but anything under 75%
          definitely indicates external fragmentation.
          Logical Scan Fragmentation: Shows the ratio of
          pages that are out of logical order. The value should
          be as close to 0% as possible and anything over 10%
          indicates external fragmentation.
          Note: The value for Extent Scan Fragmentation is not
          really important. This is because the algorithm that
          calculates it does not work for indexes that span over
          multiple files. According to some Microsoft sources,
          in the next version of SQL Server, DBCC SHOWCONTIG
          will not even include this metric. "

          Having said the Above.... lets examine the four ways
          of defragging the Table

          There are four different ways to defragment an
          index.

          "
          Drop and recreate index
          Pros: Completely rebuilds the index. Reorders and
          compacts the index pages, and removes unnecessary
          pages.
          Cons: Index is not available during process which
          means that queries will suffer. The process will block
          all queries accessing the index and also be blocked
          itself by other processes using the index, unable to
          do it's work until those processes are finished. This
          option is especially bad for clustered indexes as
          dropping a clustered index means that all
          non-clustered indexes must be rebuilt (to use RID as
          bookmark), and then recreating the clustered index
          will once again force a rebuild of all non-clustered
          indexes.

          CREATE INDEX ... WITH DROP_EXISTING
          Pros: Rebuilds index in one step which is good for
          clustered indexes, as it means that non-clustered
          indexes do not need to be rebuilt. Can be used to
          recreate (and thereby defragment) indexes created by
          constraints, if the index definition matches the
          requirements of the constraint.
          Cons: Potential blocking problems with other processes
          in the same way as for drop and recreate index above.

          DBCC DBREINDEX
          Pros: Does the best job of removing internal and
          external fragmentation since it physically assigns new
          pages to the index and moves data to them. Has the
          possibility to rebuild all indexes on a table using
          one statement. Recreates indexes for constraints
          without forcing you to know their requirements.
          Cons: The same blocking issues as for the two above.
          Runs in a transaction so all work that has been done
          is rolled back if the command is stopped.

          DBCC INDEXDEFRAG
          Pros: Reorders the leaf pages in index order -
          enhancing performance for scans especially - and
          compacts them using the setting specified for fill
          factor when the index was created. Empty pages are
          removed. This is an online operation, meaning it does
          not take long-term locks and thereby do not block
          other processes and do not get blocked itself by them.
          Work that has already been done will still be done if
          the command is cancelled.
          Cons: The fact that this is an online operation can
          also have a negative impact, as this means that DBCC
          INDEXDEFRAG will simply skip pages that are used by
          other processes. Very fragmented indexes will take a
          long time to defragment, so they should probably be
          rebuilt instead if possible. Generates a lot of log
          activity. "


          But I would still suggest the best proactive way is
          STEP 1







          Preet Sharma <mpreetsharma@...> wrote:
          Hi Guys!

          This is the first time i am writing email to you. As i
          feel this group is really impressive and helpful for
          our breed.

          So here is the problem which is related to higher
          level of fragmentation in the datapages/extent level
          on the non clustered tables(tables without clustered
          index). I have used all the available option to remove
          this fragmentation like DBCC reindex,dropping/creating
          indexes but all in vain but one thing really helped me
          was the select * into statement, in this case also
          after being again on live database it encounters the
          same problem after day or two when putting shwocontig.
          But being in live you cant do this exerciese like we
          do for our scheduling of jobs for indexdefrag or
          update statistics.

          Pls. suggest me the the other options to encounter
          this problem as this is the definite cause of the bad
          performance for browsing several thousands pages by
          sql server for few hundred rows, as mine case its
          happening. if you have any queries related to this pls
          let me know.

          looking forward to hear from you soon.

          Tons of thanks in advance.


          Regards
          Mohit Sharma
          Contact#91-9810204418

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


          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






          ---------------------------------
          Yahoo! Autos. Looking for a sweet ride? Get pricing,
          reviews, & more on new and used cars.

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



          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






          __________________________________________________________

          Yahoo! India Matrimony: Find your partner now. Go to
          http://yahoo.shaadi.com


          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          SPONSORED LINKS
          C programming language Computer programming
          languages Java programming language The c
          programming language C programming language
          Concept of programming language

          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






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

          What are the most popular cars? Find out at Yahoo!
          Autos

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



          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          SPONSORED LINKS
          C
          programming language
          Computer programming languages
          Java programming language
          The c
          programming language
          C programming language
          Concept of programming language


          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






          __________________________________________________________

          Yahoo! India Matrimony: Find your partner now. Go to
          http://yahoo.shaadi.com


          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          SPONSORED LINKS
          C programming language Computer programming
          languages Java programming language The c
          programming language C programming language
          Concept of programming language

          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






          ---------------------------------
          Do you Yahoo!?
          With a free 1 GB, there's more in store with Yahoo!
          Mail.

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



          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






          __________________________________________________________

          Yahoo! India Matrimony: Find your partner now. Go to
          http://yahoo.shaadi.com


          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






          ---------------------------------
          Do you Yahoo!?
          With a free 1 GB, there's more in store with Yahoo!
          Mail.

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



          SqlCon is a special interests group run under the
          aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User
          Group.




          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo!
          Terms of Service.


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






          __________________________________________________________
          Yahoo! India Matrimony: Find your partner now. Go to
          http://yahoo.shaadi.com


          SqlCon is a special interests group run under the aegis of MUGH -
          http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User Group.

          Yahoo! Groups Links







          DISCLAIMER:
          --------------------------------------------------------------------------------------------------------------

          This e-mail contains confidential and/or privileged information. If you are not the intended recipient
          (or have received this e-mail in error)please notify the sender immediately and destroy this e-mail.
          Any unauthorized copying, disclosure, use or distribution of the material in this e-mail is strictly forbidden.
          ---------------------------------------------------------------------------------------------------------------
        • Veer Ji Wangoo
          Can you send the Table Structure of lot_lit_detailOr simply the Create Syntax of this Table .... ... I never said my tables are clustered, so naturally no
          Message 4 of 15 , Feb 2, 2006
          • 0 Attachment
            Can you send the Table Structure of "lot_lit_detail"

            Or simply the Create Syntax of this Table ....

            -----Original Message-----
            From: sqlcon@yahoogroups.com [mailto:sqlcon@yahoogroups.com] On Behalf
            Of Preet Sharma
            Sent: Wednesday, February 01, 2006 10:29 PM
            To: sqlcon@yahoogroups.com
            Subject: Re: [mugh-sqlcon] Higher fragmentation in datapages

            Dear Kalyan

            I never said my tables are clustered, so naturally no
            clustered index is there.And even then i am getting so
            much of fragmentation, that i feel happens in the case
            if you have the clustered on your table.

            but my concern is towards the fragmentation on my
            tables without clustered on it.

            mohit

            --- smart chip <knowledge_chip@...> wrote:


            ---------------------------------
            Preet,

            the table 'lot_lit_detail'doesn't have a clustered
            index. Please send a dbcc showcontig result of the
            table having clustered index. I remember u said that
            ur tables have clustered index. but this dbcc result
            should be from a heap.



            Regards,
            Kalyan


            Preet Sharma <mpreetsharma@...> wrote:
            Yes Kalyan

            This is the result coming form the showcontig.
            Also my san is dedicated to sql server datafiles only.

            Given is the result by showcontig. DBCC
            SHOCONTIG(lot_lit_detail)
            DBCC SHOWCONTIG scanning 'lot_lit_detail' table...
            Table: 'lot_lit_detail' (535672956); index ID: 0,
            database ID: 20
            TABLE level scan performed.
            - Pages Scanned................................: 9103
            - Extents Scanned..............................: 8570
            - Extent Switches..............................: 8569
            - Avg. Pages per Extent........................: 1.1
            - Scan Density [Best Count:Actual Count].......:
            13.28% [1138:8570]
            - Extent Scan Fragmentation ...................:
            80.30%
            - Avg. Bytes Free per Page.....................:
            6001.4
            - Avg. Page Density (full).....................:
            25.85%
            DBCC execution completed. If DBCC printed error
            messages, contact your system administrator.

            Hope this will help you to understood the problem.

            RGDS
            Mohit


            --- smart chip <knowledge_chip@...> wrote:


            ---------------------------------
            Mohit,

            It is good to share this among our dba friends.

            First of all how did u find this 15-20% usage? (is
            it dbcc showcontig result?)
            Is your san drives dedicated only to SQL server?
            Is this problem suddenly arised?
            What do your sys admin says about this?

            If your databases are doing any delete operations on
            text and image columns. If it is true there is a
            chance that sql server does not release that space
            after deleting that data. I am not sure wether this
            cause that much fragmentation or not. But this issue
            exists in sql 2000. There us a Kb article about this
            http://support.microsoft.com/default.aspx/kb/324432

            otherwise how that fragmentaion arises??



            Kalyan

            Preet Sharma <mpreetsharma@...> wrote:
            Dear Kalyan

            Thanks for the article. That what i was talking about
            in my previous mail and would like to share with my
            dba friends.

            I am maintaing 34 large Databases on the san
            configured with multiple filegroups across the drives.
            The thing extenral fragmenation i am talking about is
            can also be my guess But u see as i am maintaing table
            sturucters without clustered indexes and inthis case
            such high fragmentation should not be find b'cos it
            cab be natural in only in case of clustered index. But
            here its showing high fragmenation on datapages such
            as 15-20% usage in maximum cases which does not make
            any sense and above all defrag commands and rebuilding
            indexes are noway helping me out. Its just the select
            * itno helps me where i am creating a new table and
            then putting the required indexs on it.

            But obviously its also not the right solution in OLTP.




            Mohit

            --- smart chip <knowledge_chip@...> wrote:


            ---------------------------------
            Preet,

            this is Interesting

            Did you checked out this?


            http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_0
            83104.mspx

            this will give some info. But for your case running
            defragmentaion on OS level is not possible (I think).

            I would also want to know hoe your database is
            spread accross multiple disks. If it is spread accross
            mutiple disks. That will probabaly should not be
            heavly fragmented at OS level.

            What do you say?


            Regards
            Kalyan


            Preet Sharma <mpreetsharma@...> wrote:
            Dear Veer

            FIrst of all thank you very much for your fast reply.

            But as i have said that this problem is basically not
            that we face in our OLTP enviroment generally. But
            already here after putting all the methodologies i am
            not able to defrag the space on the pages. As its
            increasing overall size of my datafiles which exactly
            need only the 50%usage in it.What you are talking
            about it basically is the internal fragmentation of
            sql server. If you read my mail line by line then you
            will be able to know that all this commands are not
            anyway working and i have already implemented them.
            Here i let you put your kind attention towards the
            external fragmenation such as on o/s level. Because
            being in this profession for more than 4 years i faced
            this type of problem first time as the maintenance
            commands are not helping anyway.
            I just want to ask the fragmentation caused by the
            operating system, if you guys have ever faced, bcos
            that's why select *into seems to be helping in big way
            but this is not the solution for the 24*7 environment
            db, where we have rarely downtime for these type of
            issues and its even more tougher to convince seniors
            for this type of problem.

            Hope this time u are getting me.

            Thanks

            Mohit








            --- veer wangoo <vwangoo@...> wrote:


            ---------------------------------
            There are two wasy to solve this problem one is pro
            Active and other one reactive.

            ProActive tasks to solve such problem are

            1.

            When You take regular back upalways try to involve
            Databases health Checking and rectfying DBCCs like as
            you said indexdefrag or update statistics.

            2.

            when you design a database make sure the amount of
            data that one row is going to hold and do proper
            analysis based on page Split and Fill factor.

            Now that you have limitation in above two methods
            but I would suggest you carry up the first point on
            weekend.

            REACTIVE APPROACH

            1. Changing the Fill factor (Which I see is not a
            good practice in prod Server unless very important)


            Now lets see the four ways of defragging but before
            that it is important to understand the fragmentation
            information.

            "To view how much fragmentation an index has you use
            DBCC SHOWCONTIG. It can show information for all
            indexes on a table or just a single index at a time.
            Using the option TABLERESULTS you get extra columns in
            the output that describe statistics about the index.
            By default DBCC SHOWCONTIG scans the page chain at the
            leaf level, but it is possible to scan all levels of
            an index.
            When reviewing the output from DBCC SHOWCONTIG you
            should pay special attention to the following metrics:

            Avg. Page Density (full): Shows the average level
            of how filled the pages are. A percentage means the
            pages are almost full, and a low indicates much free
            space. This value should be compared to the fill
            factor setting specified when the index was created to
            decide whether or not the index is internally
            fragmented.
            Scan Density: Shows the ratio between the Best
            Count of extents that should be necessary to read when
            scanning all the pages of the index, and the Actual
            Count of extents that was read. This percentage should
            be as close to 100% as possible. Defining an
            acceptable level is difficult, but anything under 75%
            definitely indicates external fragmentation.
            Logical Scan Fragmentation: Shows the ratio of
            pages that are out of logical order. The value should
            be as close to 0% as possible and anything over 10%
            indicates external fragmentation.
            Note: The value for Extent Scan Fragmentation is not
            really important. This is because the algorithm that
            calculates it does not work for indexes that span over
            multiple files. According to some Microsoft sources,
            in the next version of SQL Server, DBCC SHOWCONTIG
            will not even include this metric. "

            Having said the Above.... lets examine the four ways
            of defragging the Table

            There are four different ways to defragment an
            index.

            "
            Drop and recreate index
            Pros: Completely rebuilds the index. Reorders and
            compacts the index pages, and removes unnecessary
            pages.
            Cons: Index is not available during process which
            means that queries will suffer. The process will block
            all queries accessing the index and also be blocked
            itself by other processes using the index, unable to
            do it's work until those processes are finished. This
            option is especially bad for clustered indexes as
            dropping a clustered index means that all
            non-clustered indexes must be rebuilt (to use RID as
            bookmark), and then recreating the clustered index
            will once again force a rebuild of all non-clustered
            indexes.

            CREATE INDEX ... WITH DROP_EXISTING
            Pros: Rebuilds index in one step which is good for
            clustered indexes, as it means that non-clustered
            indexes do not need to be rebuilt. Can be used to
            recreate (and thereby defragment) indexes created by
            constraints, if the index definition matches the
            requirements of the constraint.
            Cons: Potential blocking problems with other processes
            in the same way as for drop and recreate index above.

            DBCC DBREINDEX
            Pros: Does the best job of removing internal and
            external fragmentation since it physically assigns new
            pages to the index and moves data to them. Has the
            possibility to rebuild all indexes on a table using
            one statement. Recreates indexes for constraints
            without forcing you to know their requirements.
            Cons: The same blocking issues as for the two above.
            Runs in a transaction so all work that has been done
            is rolled back if the command is stopped.

            DBCC INDEXDEFRAG
            Pros: Reorders the leaf pages in index order -
            enhancing performance for scans especially - and
            compacts them using the setting specified for fill
            factor when the index was created. Empty pages are
            removed. This is an online operation, meaning it does
            not take long-term locks and thereby do not block
            other processes and do not get blocked itself by them.
            Work that has already been done will still be done if
            the command is cancelled.
            Cons: The fact that this is an online operation can
            also have a negative impact, as this means that DBCC
            INDEXDEFRAG will simply skip pages that are used by
            other processes. Very fragmented indexes will take a
            long time to defragment, so they should probably be
            rebuilt instead if possible. Generates a lot of log
            activity. "


            But I would still suggest the best proactive way is
            STEP 1







            Preet Sharma <mpreetsharma@...> wrote:
            Hi Guys!

            This is the first time i am writing email to you. As i
            feel this group is really impressive and helpful for
            our breed.

            So here is the problem which is related to higher
            level of fragmentation in the datapages/extent level
            on the non clustered tables(tables without clustered
            index). I have used all the available option to remove
            this fragmentation like DBCC reindex,dropping/creating
            indexes but all in vain but one thing really helped me
            was the select * into statement, in this case also
            after being again on live database it encounters the
            same problem after day or two when putting shwocontig.
            But being in live you cant do this exerciese like we
            do for our scheduling of jobs for indexdefrag or
            update statistics.

            Pls. suggest me the the other options to encounter
            this problem as this is the definite cause of the bad
            performance for browsing several thousands pages by
            sql server for few hundred rows, as mine case its
            happening. if you have any queries related to this pls
            let me know.

            looking forward to hear from you soon.

            Tons of thanks in advance.


            Regards
            Mohit Sharma
            Contact#91-9810204418

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


            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






            ---------------------------------
            Yahoo! Autos. Looking for a sweet ride? Get pricing,
            reviews, & more on new and used cars.

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



            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






            __________________________________________________________

            Yahoo! India Matrimony: Find your partner now. Go to
            http://yahoo.shaadi.com


            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            SPONSORED LINKS
            C programming language Computer programming
            languages Java programming language The c
            programming language C programming language
            Concept of programming language

            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






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

            What are the most popular cars? Find out at Yahoo!
            Autos

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



            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            SPONSORED LINKS
            C
            programming language
            Computer programming languages
            Java programming language
            The c
            programming language
            C programming language
            Concept of programming language


            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






            __________________________________________________________

            Yahoo! India Matrimony: Find your partner now. Go to
            http://yahoo.shaadi.com


            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            SPONSORED LINKS
            C programming language Computer programming
            languages Java programming language The c
            programming language C programming language
            Concept of programming language

            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






            ---------------------------------
            Do you Yahoo!?
            With a free 1 GB, there's more in store with Yahoo!
            Mail.

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



            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






            __________________________________________________________

            Yahoo! India Matrimony: Find your partner now. Go to
            http://yahoo.shaadi.com


            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






            ---------------------------------
            Do you Yahoo!?
            With a free 1 GB, there's more in store with Yahoo!
            Mail.

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



            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.




            ---------------------------------
            YAHOO! GROUPS LINKS


            Visit your group "sqlcon" on the web.

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.


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






            __________________________________________________________
            Yahoo! India Matrimony: Find your partner now. Go to
            http://yahoo.shaadi.com


            SqlCon is a special interests group run under the aegis of MUGH -
            http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User Group.

            Yahoo! Groups Links







            DISCLAIMER:
            --------------------------------------------------------------------------------------------------------------

            This e-mail contains confidential and/or privileged information. If you are not the intended recipient
            (or have received this e-mail in error)please notify the sender immediately and destroy this e-mail.
            Any unauthorized copying, disclosure, use or distribution of the material in this e-mail is strictly forbidden.
            ---------------------------------------------------------------------------------------------------------------
          Your message has been successfully submitted and would be delivered to recipients shortly.