freetexttable query continued

Still working on this query:
select *
from products
inner join freetexttable(products,*,@.searchstr) ft1
on ft1.[key] = products.p_id
left join sku
on sku.p_id= products.p_id
inner join freetexttable(sku,*,@.searchstr) ft2
on ft2.[key] = sku.sku
I replaced the inner join with a left join on the second table.
I still get no results if the searchstr exists in the first table but not in
the second table. Full join doesn't work either.
Did I modify the wrong join? If I left-join on the FTcat I get all the rows
in both tables.
TIA!
can you post the schemas of all related tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
> Still working on this query:
> select *
> from products
> inner join freetexttable(products,*,@.searchstr) ft1
> on ft1.[key] = products.p_id
> left join sku
> on sku.p_id= products.p_id
> inner join freetexttable(sku,*,@.searchstr) ft2
> on ft2.[key] = sku.sku
> I replaced the inner join with a left join on the second table.
> I still get no results if the searchstr exists in the first table but not
> in the second table. Full join doesn't work either.
> Did I modify the wrong join? If I left-join on the FTcat I get all the
> rows in both tables.
> TIA!
>
>
|||> can you post the schemas of all related tables?
Products:
p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no yes SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no yes SQL_Latin1_General_CP1_CI_AS
p_man int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL
(p_id is PK)
Manufacturers:
m_name varchar no 100 no no no SQL_Latin1_General_CP1_CI_AS
m_id int no 4 10 0 no (n/a) (n/a) NULL
(m_id is PK)
SKUs:
p_id varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
sku varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
sku_weight decimal no 9 18 1 yes (n/a) (n/a) NULL
attribute1 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
attribute2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status int no 4 10 0 yes (n/a) (n/a) NULL
wholesale_cost decimal no 5 7 2 no (n/a) (n/a) NULL
special_order bit no 1 no (n/a) (n/a) NULL
date_available datetime no 8 yes (n/a) (n/a) NULL
(sku is PK)
where
m_id in manufacturers = p_man in products
and p_id in sku = p_id in products

> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
>

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...
>
>
|||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:
[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:

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 hav
e
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 crea
te
> 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 t
he
> fields at all after they were in place? That too can lead to fragmentatio
n.
> 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...
>
>|||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 th
e
> 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 an
d
> update statistics but result is the same or similar. Other tables I have h
ave
> 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 th
e
> 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 an
d
> 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:
[vbcol=seagreen]
> DXC,
> You should not worry about fragmentation on a table with only 1,964 record
s.
> 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:
>|||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...[vbcol=seagreen]
> 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:
>

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.
>>