Fragmentation Problem

SQL Server 2005 SP2.
I have a table with 1964 rows of data in it. I have a primary key and 4
non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
column per index) and the other 2 indexes are on the int column (Again 1
column per index).
The problem is, when I go to "Properties" then select the "fragmentation"
of the indexes, for the first index (varchar(80)) I have;
Page fullness: 99.72%
Total Fragmentation: 85.71%
Second index (varchar(80)) I have;
Page fullness: 86.72%
Total Fragmentation: 87.50%
The third index (int column)
Page fullness: 88.95%
Total Fragmentation: 66.67%
The fourth index (int column)
Page fullness: 84.90%
Total Fragmentation: 75.00%
I have re-created the table in another database and add the indexes but the
similar result. Why do I have the fragmentation on the indexes ?. Is it
because of the data type ?. I also tried drop-recreate, rebuild indexes and
update statistics but result is the same or similar. Other tables I have have
0.0% Fragmentation.
Thanks for any help.1) how much free space is in the database? You need contiguous free space
for indexes to be laid down contiguously during their creation or defrag
processes. Double the size of the database if possible then drop and create
the indexes and see what you get.
2) How did you populate the table in the other database? INSERT scripts?
If so, that could be the reason for fragmentation. Also, did you update the
fields at all after they were in place? That too can lead to fragmentation.
3) what order did you create the indexes in on the second database?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but
> the
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes
> and
> update statistics but result is the same or similar. Other tables I have
> have
> 0.0% Fragmentation.
> Thanks for any help.|||The table is so small that the reported fragmentation will make no
difference to performance.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
SQL Server 2005 SP2.
I have a table with 1964 rows of data in it. I have a primary key and 4
non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
column per index) and the other 2 indexes are on the int column (Again 1
column per index).
The problem is, when I go to "Properties" then select the "fragmentation"
of the indexes, for the first index (varchar(80)) I have;
Page fullness: 99.72%
Total Fragmentation: 85.71%
Second index (varchar(80)) I have;
Page fullness: 86.72%
Total Fragmentation: 87.50%
The third index (int column)
Page fullness: 88.95%
Total Fragmentation: 66.67%
The fourth index (int column)
Page fullness: 84.90%
Total Fragmentation: 75.00%
I have re-created the table in another database and add the indexes but the
similar result. Why do I have the fragmentation on the indexes ?. Is it
because of the data type ?. I also tried drop-recreate, rebuild indexes and
update statistics but result is the same or similar. Other tables I have
have
0.0% Fragmentation.
Thanks for any help.|||1) Total space usage: 2,888.69 MB
Data Files Space Usage: 618.19 MB
Transaction Log Space Usage: 2,270.50 MB
Both data and log files set to 10% file growth and Unrestricted File Growth.
2) I have created the table from DDL (Without Primary key and indexes). I
have not updates the fields after the data import. Just select data from the
table.
3) (From script) Created the primary key first, then index 1 and 2 which are
the varchar(80) columns then the other 2 int columns.
"TheSQLGuru" wrote:
> 1) how much free space is in the database? You need contiguous free space
> for indexes to be laid down contiguously during their creation or defrag
> processes. Double the size of the database if possible then drop and create
> the indexes and see what you get.
> 2) How did you populate the table in the other database? INSERT scripts?
> If so, that could be the reason for fragmentation. Also, did you update the
> fields at all after they were in place? That too can lead to fragmentation.
> 3) what order did you create the indexes in on the second database?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
> > SQL Server 2005 SP2.
> >
> > I have a table with 1964 rows of data in it. I have a primary key and 4
> > non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> > column per index) and the other 2 indexes are on the int column (Again 1
> > column per index).
> >
> > The problem is, when I go to "Properties" then select the "fragmentation"
> > of the indexes, for the first index (varchar(80)) I have;
> >
> > Page fullness: 99.72%
> > Total Fragmentation: 85.71%
> >
> > Second index (varchar(80)) I have;
> >
> > Page fullness: 86.72%
> > Total Fragmentation: 87.50%
> >
> > The third index (int column)
> >
> > Page fullness: 88.95%
> > Total Fragmentation: 66.67%
> >
> > The fourth index (int column)
> >
> > Page fullness: 84.90%
> > Total Fragmentation: 75.00%
> >
> >
> > I have re-created the table in another database and add the indexes but
> > the
> > similar result. Why do I have the fragmentation on the indexes ?. Is it
> > because of the data type ?. I also tried drop-recreate, rebuild indexes
> > and
> > update statistics but result is the same or similar. Other tables I have
> > have
> > 0.0% Fragmentation.
> >
> > Thanks for any help.
>
>|||DXC,
You should not worry about fragmentation on a table with only 1,964 records.
One recommendation is to ignore fragmentation on indexes with less than
1,000 pages, as shown by dbcc showcontig.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"DXC" wrote:
> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but the
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes and
> update statistics but result is the same or similar. Other tables I have have
> 0.0% Fragmentation.
> Thanks for any help.|||Yes but why would it show some 87.00% fragmentation ?. By the way, the
primary key column (int) has fragmentation at 0.0%.
"Tom Moreau" wrote:
> The table is so small that the reported fragmentation will make no
> difference to performance.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
> SQL Server 2005 SP2.
> I have a table with 1964 rows of data in it. I have a primary key and 4
> non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> column per index) and the other 2 indexes are on the int column (Again 1
> column per index).
> The problem is, when I go to "Properties" then select the "fragmentation"
> of the indexes, for the first index (varchar(80)) I have;
> Page fullness: 99.72%
> Total Fragmentation: 85.71%
> Second index (varchar(80)) I have;
> Page fullness: 86.72%
> Total Fragmentation: 87.50%
> The third index (int column)
> Page fullness: 88.95%
> Total Fragmentation: 66.67%
> The fourth index (int column)
> Page fullness: 84.90%
> Total Fragmentation: 75.00%
>
> I have re-created the table in another database and add the indexes but the
> similar result. Why do I have the fragmentation on the indexes ?. Is it
> because of the data type ?. I also tried drop-recreate, rebuild indexes and
> update statistics but result is the same or similar. Other tables I have
> have
> 0.0% Fragmentation.
> Thanks for any help.
>|||Yes..........This is what shows after the DBCC SHOWCONTIG but
fragmentation on the index properties shows something else.........Thanks.
DBCC SHOWCONTIG scanning 'customerinfo' table...
Table: 'customerinfo' (1288391659); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 94
- Extents Scanned.......................: 12
- Extent Switches.......................: 11
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [12:12]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 265.5
- Avg. Page Density (full)................: 96.72%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Ben Nevarez" wrote:
> DXC,
> You should not worry about fragmentation on a table with only 1,964 records.
> One recommendation is to ignore fragmentation on indexes with less than
> 1,000 pages, as shown by dbcc showcontig.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "DXC" wrote:
> > SQL Server 2005 SP2.
> >
> > I have a table with 1964 rows of data in it. I have a primary key and 4
> > non-clustered indexes on it. 2 of the indexes on a varchar(80) column (1
> > column per index) and the other 2 indexes are on the int column (Again 1
> > column per index).
> >
> > The problem is, when I go to "Properties" then select the "fragmentation"
> > of the indexes, for the first index (varchar(80)) I have;
> >
> > Page fullness: 99.72%
> > Total Fragmentation: 85.71%
> >
> > Second index (varchar(80)) I have;
> >
> > Page fullness: 86.72%
> > Total Fragmentation: 87.50%
> >
> > The third index (int column)
> >
> > Page fullness: 88.95%
> > Total Fragmentation: 66.67%
> >
> > The fourth index (int column)
> >
> > Page fullness: 84.90%
> > Total Fragmentation: 75.00%
> >
> >
> > I have re-created the table in another database and add the indexes but the
> > similar result. Why do I have the fragmentation on the indexes ?. Is it
> > because of the data type ?. I also tried drop-recreate, rebuild indexes and
> > update statistics but result is the same or similar. Other tables I have have
> > 0.0% Fragmentation.
> >
> > Thanks for any help.|||Your output doesn't reveal how much actual data is being used in the
database, just that 618MB is allocated.
Your output also reveals that you probably have your database set to FULL
recovery mode but are not doing transaction log backups. If so your tlog
will continue to grow until it fills up the disk it is on, at which point
your database activity will come to a grinding halt. :-)
As others pointed out, fragmentation on a table with 2000 rows is pretty
much a non-issue from a performance standpoint.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:51C396D7-ECE8-4588-91F7-E078620A481E@.microsoft.com...
> 1) Total space usage: 2,888.69 MB
> Data Files Space Usage: 618.19 MB
> Transaction Log Space Usage: 2,270.50 MB
> Both data and log files set to 10% file growth and Unrestricted File
> Growth.
> 2) I have created the table from DDL (Without Primary key and indexes). I
> have not updates the fields after the data import. Just select data from
> the
> table.
> 3) (From script) Created the primary key first, then index 1 and 2 which
> are
> the varchar(80) columns then the other 2 int columns.
>
>
> "TheSQLGuru" wrote:
>> 1) how much free space is in the database? You need contiguous free
>> space
>> for indexes to be laid down contiguously during their creation or defrag
>> processes. Double the size of the database if possible then drop and
>> create
>> the indexes and see what you get.
>> 2) How did you populate the table in the other database? INSERT scripts?
>> If so, that could be the reason for fragmentation. Also, did you update
>> the
>> fields at all after they were in place? That too can lead to
>> fragmentation.
>> 3) what order did you create the indexes in on the second database?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:88BD8A7B-FA17-48CB-A68A-F8E0E6293603@.microsoft.com...
>> > SQL Server 2005 SP2.
>> >
>> > I have a table with 1964 rows of data in it. I have a primary key and 4
>> > non-clustered indexes on it. 2 of the indexes on a varchar(80) column
>> > (1
>> > column per index) and the other 2 indexes are on the int column (Again
>> > 1
>> > column per index).
>> >
>> > The problem is, when I go to "Properties" then select the
>> > "fragmentation"
>> > of the indexes, for the first index (varchar(80)) I have;
>> >
>> > Page fullness: 99.72%
>> > Total Fragmentation: 85.71%
>> >
>> > Second index (varchar(80)) I have;
>> >
>> > Page fullness: 86.72%
>> > Total Fragmentation: 87.50%
>> >
>> > The third index (int column)
>> >
>> > Page fullness: 88.95%
>> > Total Fragmentation: 66.67%
>> >
>> > The fourth index (int column)
>> >
>> > Page fullness: 84.90%
>> > Total Fragmentation: 75.00%
>> >
>> >
>> > I have re-created the table in another database and add the indexes but
>> > the
>> > similar result. Why do I have the fragmentation on the indexes ?. Is it
>> > because of the data type ?. I also tried drop-recreate, rebuild indexes
>> > and
>> > update statistics but result is the same or similar. Other tables I
>> > have
>> > have
>> > 0.0% Fragmentation.
>> >
>> > Thanks for any help.
>>