Aleksandar's computer workshop

Let's see what Aleksandar was fixing today.
My findings, tips & tricks related to computers, internet, programming and other stuff I was working with.

How to find out which version of Linux you are running

To get running version type this in command line:
uname -a

Linux asterisk1.local 2.6.9-34.0.2.EL #1 Fri Jul 7 19:24:57 CDT
2006 i686 i686 i386 GNU/Linux

To get release version which was used during installation type this:
cat /etc/*-release

CentOS release 4.3 (Final)

SQL: How do you know when a table is fragmented

Microsoft SQL Server 2000 Index Defragmentation Best Practices

As Microsoft SQL Server 2000 maintains indexes to reflect updates to their
underlying tables, these indexes can become fragmented. Depending on workload
characteristics, this fragmentation can adversely affect workload performance.
This white paper provides information to help you determine whether you should
defragment table indexes to benefit workload performance. To defragment indexes,
SQL Server 2000 provides several statements. This white paper compares two of

How to Detect Table Fragmentation in SQL Server 2000 and 2005

Copy of the article

How do you know when a table is

  • Poor query performance over time.

  • More disk activity.

  • Poor cache utilization.

  • Verify the I/O of a query.

  • Verify scan density in SQL 2000 using DBCC SHOWCONTIG and in SQL 2005 using
    the dynamic management view sys.dm_db_index_physical_stats.

Table fragmentation can occur when modifying data with INSERT, UPDATE, or
DELETE statements, which over time cause gaps in each page. If a query search is
based on a table scan or partial table scan, then it will create overhead for
the SQL Server process with additional page reads, leading to high CPU activity
and unresponsiveness. Defragmentation targets logical fragmentation at the leaf
level of an index, ensuring optimum performance.

What is the effect of index

It will degrade the performance; normal queries will take longer to display
results. If the query search arguments are small, based on the data it will be
fetching, then the query optimizer chooses the order of the scan operation, as
long as the table has the appropriate index to find the data quickly. Whether
the table has got the right index is not always an easy question to answer, as
you have to rely on scanning of all the execution plans or on using the Index
Tuning Wizard GUI along with Profiler.

How can you measure a table that is fragmented and
how can you reduce the fragmentation?

In SQL Server 2000, you can use DBCC SHOWCONTIG, which will show the scan
density and fragmentation level of a table. If you use this statement and WITH
TABLERESULTS then it displays results as a row set with additional information.
The DBCC INDEXDEFRAG and DBCC DBREINDEX statements are best used to reduce table

DBCC INDEXDEFRAG will defrag the leaf level of an index, which will enable
the physical order of pages to match the logical order of the leaf nodes. This
statement reports the estimated percentage of the process every five minutes.
(You can learn more about how to verify the fragmentation in SQL Server 2000 on
this Web site.)

Even if the indexes are properly created, they tend to lose effectiveness
over time due to the fragmentation that occurs on the table throughout the
INSERT, UPDATE and DELETE processes. The resulting fragmentation (free space on
pages) will lead to improper usage of memory when using search arguments to scan
for a particular dataset.

What's New in SQL Server 2005?

The DBCC SHOWCONTIG statement has been replaced. Using the dynamic management
function sys.dm_db_index_physical_stats will help you detect
fragmentation in a specific index or all indexes on a table or indexed view. In
the case of partitioned indexes, this DMV will also provide fragmentation
information for each partition. The general syntax for this DMV is:

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'<Database Name>'), OBJECT_ID(N'<Table

To return information for all the tables and indexes, use:

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL,

The Scanning Mode helps to determine the level of scanning performed to
obtain the statistical data used by the function. The three modes are LIMITED,
SAMPLED, or DETAILED and it applies the Intent-Shared (IS) table lock. The
default mode is LIMITED, which displays page counts and external fragmentation
without any page density. The SAMPLED and DETAILED modes will analyze the entire
table for both internal and external fragmentation.

The column avg_fragmentation_in_percent represents the
fragmentation level of an index or heap. The table-valued function returns the
tabular set. You can use SELECT INTO to catch the entire result set. There is no
need to use dynamic string execution to interpret the results.

SELECT * INTO #tbl_Fragmentation FROM
sys.dm_db_index_physical_stats (DatabaseID, TableId, IndexId, NULL,

Please note that the Scanning Mode of the above Dynamic Management Function
will be LIMITED, which is the default behavior in SQL Server 2005. To get more
information on the results, execute as follows (Johan Bijens):

SELECT * INTO #tbl_Fragmentation FROM
sys.dm_db_index_physical_stats (DatabaseID, TableId, IndexId, NULL,

Under certain circumstances, SQL Server 2005 uses a feature called "forwarded
rows." For example, when a table with a variable-length column is updated to a
larger size in that page and can no longer fit, SQL Server creates a forwarding
pointer in that page. That pointer remains in place unless the row shrinks
enough to move back to its original location. It will also be compressed when
the database is processed to SHRINK, which will reassign the row identifiers by
avoiding the generation of forwarded rows. This may have a side effect on
performance by creating additional I/O to obtain the first record pointer to the
relocated row. The dynamic management function sys.dm_db_index_physical_stats provides both forwarded record
count and record percent. This function helps to monitor changing information
such as locks, index health, and so on. To get only the required percentage of
fragmented index information you can join the sysindexes table
with this dynamic management function:


OBJECT_NAME(i.object_id) AS TableName, AS TableIndexName, phystat.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,

JOIN sys.indexes i ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id

phystat.avg_fragmentation_in_percent > 10 AND
phystat.avg_fragmentation_in_percent < 40

A Few Tips on Avoiding

  • When a database is created, make sure the data files are created with or
    assigned the largest values possible. You can do this by planning to use a value
    that can fit the maximum amount of data during a certain period (say, three
    years at least).

  • Sometimes it is feasible to permit the data files to grow automatically
    while keeping a limit on the growth by specifying a maximum data file growth
    size that leaves some available space on the hard disk.

  • After a period of time, ascertain and re-evaluate the expected maximum
    database size by adding more files or filegroups to the database, if

  • Do not let the data files grow automatically if there many data files share
    the same disk partition. If the data files are heavily used then locate them in
    a different filegroup or on a different partition.

  • Perform regular database maintenance tasks, such as DBCC DBREINDEX, and
    recompiling stored procedures and triggers.

  • If the table row(s) are modified or deleted frequently then it is better to
    run intermittent UPDATE STATISTICS on the table, which will help it avoid any
    slow performance from the execution plan.

Queries Dragging Try Defragging

Did you ever have a user tell you a query is taking a lot longer to compete than
before, even though nothing in it has changed? If so, there's a good chance that
the indexes in the table that the query ran against have become fragmented.
Fixing this problem is a two-step process.