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

MUGH-SQLCON Re: How to check the performance of the server in SQLSERVER2000.

Expand Messages
  • SARADA DEVI
    After running dbcc show contig: if scandensity is 100% than no need to go for defrag or rebuild. If scan density is 50% than rebuild required. What else are
    Message 1 of 7 , Jan 1, 2009
    • 0 Attachment
      After running dbcc show contig:

      if scandensity is 100% than no need to go for defrag or rebuild.

      If scan density is 50% than rebuild required.

      What else are the parameters or indications there is a fragmentation
      for a database

      Thanks,
      krishna


      --- In sqlcon@yahoogroups.com, sunil john <suniljk7@...> wrote:
      >
      >
      > 1]how to check the fragmentation for a database
      > sql 2000 - dbcc showcontig (<table name>)
      > sql 2005 - SELECT database_id,object_name(object_id)as
      TabName,index_type_desc,avg_fragmentation_in_percent
      > FROM sys.dm_db_index_physical_stats(db_id, NULL, NULL,
      NULL, 'DETAILED')
      > ORDER by avg_fragmentation_in_percent desc
      >
      > 2]how do identify index creation is required for a particular
      column or row?
      > can use index tuning wizard
      > there is no index for row.. index is created on column
      >
      > 3]how can i know for a particular column or row index creation is
      required?
      > same as above
      >
      > 4]how to idendify for a particular row or column index got
      fragmented.hence indexdefrag or reindex needed.?
      > sql 2000. dbcc indexdefrag (<dbname>, <table name>, <index
      name>)
      > dbcc dbreindex (<table name>, <fillfactor
      (optional)>)
      > sql 2005 ALTER INDEX ALL ON <TableName> REBUILD
      >
      >
      > 5]update stats what exactly it will do?
      > statistics about distribution of key values in each index and
      use these statistic to determine which indexs to use in Query
      processing.
      >
      > -- experts please correct me if i am wrong
      >
      > Best Regards
      >
      >
      > Sunil John
      >
      >
      >
      >
      > ________________________________
      > From: SARADADEVI <sv_39@...>
      > To: sqlcon@yahoogroups.com
      > Sent: Saturday, December 20, 2008 6:45:28
      > Subject: Re: MUGH-SQLCON Re: How to check the performance of the
      server in SQLSERVER2000.
      >
      >
      >
      >
      > 1]how to check the fragmentation for a database,
      >
      > 2]how do identify index creation is required for a particular
      column or row?
      >
      > 3]how can i know for a particular column or row index creation is
      required?
      >
      > 4]how to idendify for a particular row or column index got
      fragmented.hence indexdefrag or reindex needed.?
      >
      > 5]update stats what exactly it will do?
      >
      > Thanks,
      > krishna
      >
      > --- On Sat, 13/12/08, Madhu K Nair <madhuotp@yahoo. com> wrote:
      > From: Madhu K Nair <madhuotp@yahoo. com>
      > Subject: MUGH-SQLCON Re: How to check the performance of the server
      in SQLSERVER2000.
      > To: sqlcon@yahoogroups. com
      > Date: Saturday, 13 December, 2008, 7:41 PM
      >
      > Its very openended question. For any kind of performance tuning you
      >
      > should be having a baseline report. WHat was the performance on so
      and
      >
      > so date kind. Once you have baseline and you are sure that there is
      >
      > perf issue, then you can start with analysing where is the
      performance
      >
      > botttle neck. If you see that the issue is with DB, then you may
      want
      >
      > to check the fragmentation, creating/maintainin g indexes, update
      stats,
      >
      > re-writing query if need etc.
      >
      > www.sql-server- performance. com have lot of artilce in this subject
      >
      > Madhu
      >
      > --- In sqlcon@yahoogroups. com, "SARADA DEVI" <sv_39@> wrote:
      >
      > >
      >
      > > How to check the performance of the server in SQLSERVER2000.
      >
      > >
      >
      > > Thanks,
      >
      > > Sarada
      >
      > >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      > Add more friends to your messenger and enjoy! Go to
      http://messenger. yahoo.com/ invite/
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      >
      > New Email addresses available on Yahoo!
      > Get the Email name you've always wanted on the new @ymail and
      @rocketmail.
      > Hurry before someone else does!
      > http://mail.promotions.yahoo.com/newdomains/aa/
      >
      > [Non-text portions of this message have been removed]
      >
    • Santhosh
      Krishna, That is the only parameter which indicates the fragmentation level. If it is SQL 2005 it is recommended to use the dmv sys.dm_db_index_physical_stats
      Message 2 of 7 , Jan 2, 2009
      • 0 Attachment
        Krishna,
        That is the only parameter which indicates the fragmentation level. If it is SQL 2005 it is recommended to use the dmv sys.dm_db_index_physical_stats and look for the "avg_fragmentation_in_percent" column.

        With :))s
        Santhosh Kumar S.J - MCITP
        ReadMe @ sqlspy.blogspot.com
        ReadMe @ santhoshpakkangal.blogspot.com




        ________________________________
        From: SARADA DEVI <sv_39@...>
        To: sqlcon@yahoogroups.com
        Sent: Friday, 2 January, 2009 9:11:12 AM
        Subject: MUGH-SQLCON Re: How to check the performance of the server in SQLSERVER2000.



        After running dbcc show contig:

        if scandensity is 100% than no need to go for defrag or rebuild.

        If scan density is 50% than rebuild required.

        What else are the parameters or indications there is a fragmentation
        for a database

        Thanks,
        krishna

        --- In sqlcon@yahoogroups. com, sunil john <suniljk7@.. .> wrote:
        >
        >
        > 1]how to check the fragmentation for a database
        > sql 2000 - dbcc showcontig (<table name>)
        > sql 2005 - SELECT database_id, object_name( object_id) as
        TabName,index_ type_desc, avg_fragmentatio n_in_percent
        > FROM sys.dm_db_index_ physical_ stats(db_ id, NULL, NULL,
        NULL, 'DETAILED')
        > ORDER by avg_fragmentation_ in_percent desc
        >
        > 2]how do identify index creation is required for a particular
        column or row?
        > can use index tuning wizard
        > there is no index for row.. index is created on column
        >
        > 3]how can i know for a particular column or row index creation is
        required?
        > same as above
        >
        > 4]how to idendify for a particular row or column index got
        fragmented.hence indexdefrag or reindex needed.?
        > sql 2000. dbcc indexdefrag (<dbname>, <table name>, <index
        name>)
        > dbcc dbreindex (<table name>, <fillfactor
        (optional)>)
        > sql 2005 ALTER INDEX ALL ON <TableName> REBUILD
        >
        >
        > 5]update stats what exactly it will do?
        > statistics about distribution of key values in each index and
        use these statistic to determine which indexs to use in Query
        processing.
        >
        > -- experts please correct me if i am wrong
        >
        > Best Regards
        >
        >
        > Sunil John
        >
        >
        >
        >
        > ____________ _________ _________ __
        > From: SARADADEVI <sv_39@...>
        > To: sqlcon@yahoogroups. com
        > Sent: Saturday, December 20, 2008 6:45:28
        > Subject: Re: MUGH-SQLCON Re: How to check the performance of the
        server in SQLSERVER2000.
        >
        >
        >
        >
        > 1]how to check the fragmentation for a database,
        >
        > 2]how do identify index creation is required for a particular
        column or row?
        >
        > 3]how can i know for a particular column or row index creation is
        required?
        >
        > 4]how to idendify for a particular row or column index got
        fragmented.hence indexdefrag or reindex needed.?
        >
        > 5]update stats what exactly it will do?
        >
        > Thanks,
        > krishna
        >
        > --- On Sat, 13/12/08, Madhu K Nair <madhuotp@yahoo. com> wrote:
        > From: Madhu K Nair <madhuotp@yahoo. com>
        > Subject: MUGH-SQLCON Re: How to check the performance of the server
        in SQLSERVER2000.
        > To: sqlcon@yahoogroups. com
        > Date: Saturday, 13 December, 2008, 7:41 PM
        >
        > Its very openended question. For any kind of performance tuning you
        >
        > should be having a baseline report. WHat was the performance on so
        and
        >
        > so date kind. Once you have baseline and you are sure that there is
        >
        > perf issue, then you can start with analysing where is the
        performance
        >
        > botttle neck. If you see that the issue is with DB, then you may
        want
        >
        > to check the fragmentation, creating/maintainin g indexes, update
        stats,
        >
        > re-writing query if need etc.
        >
        > www.sql-server- performance. com have lot of artilce in this subject
        >
        > Madhu
        >
        > --- In sqlcon@yahoogroups. com, "SARADA DEVI" <sv_39@> wrote:
        >
        > >
        >
        > > How to check the performance of the server in SQLSERVER2000.
        >
        > >
        >
        > > Thanks,
        >
        > > Sarada
        >
        > >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        > Add more friends to your messenger and enjoy! Go to
        http://messenger. yahoo.com/ invite/
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >
        >
        > New Email addresses available on Yahoo!
        > Get the Email name you've always wanted on the new @ymail and
        @rocketmail.
        > Hurry before someone else does!
        > http://mail. promotions. yahoo.com/ newdomains/ aa/
        >
        > [Non-text portions of this message have been removed]
        >




        Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/

        [Non-text portions of this message have been removed]
      Your message has been successfully submitted and would be delivered to recipients shortly.