SQLCON is a Restricted Group with 1313 members.

Primary Navigation

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.