Indext extent fragmentation

Hi,
On the databases I manage I notice high (50%+) extent
fragmentation when I run DBCC SHOWCONTIG against the
tables' indexes. I've run dbcc indexdefrag, dbcc
dbreindex, and create index ...with drop existing on these
indexes and they still show the same fragmentation even
after an UPDATE STATISTICS.
Is there any way to eliminate this beyond completely
dropping the indexes and starting from scratch (not a
realistic option)? The procedure I run has virtually
eliminated the logical fragmentation- are there major
performance issues with leaving them as-is?
-DanHi,
If your select statement returns all the records of a table, such table
fragmentation can cause additional page reads which will reduce the
performance of your query and utilize more resource (Memory/CPU / Disk
reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
table.
One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
recreate index, all the fragmented pages will be cleared and you will have
enough
free space in your database.
Thanks
Hari
US Technology
Drop and re-create a clustered index. "Dan Wunder"
<anonymous@.discussions.microsoft.com> wrote in message
news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> Hi,
> On the databases I manage I notice high (50%+) extent
> fragmentation when I run DBCC SHOWCONTIG against the
> tables' indexes. I've run dbcc indexdefrag, dbcc
> dbreindex, and create index ...with drop existing on these
> indexes and they still show the same fragmentation even
> after an UPDATE STATISTICS.
> Is there any way to eliminate this beyond completely
> dropping the indexes and starting from scratch (not a
> realistic option)? The procedure I run has virtually
> eliminated the logical fragmentation- are there major
> performance issues with leaving them as-is?
> -Dan|||Please read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
This explains everything you need to know.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OAqkB7dmDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If your select statement returns all the records of a table, such table
> fragmentation can cause additional page reads which will reduce the
> performance of your query and utilize more resource (Memory/CPU / Disk
> reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
> table.
> One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
> recreate index, all the fragmented pages will be cleared and you will
have
> enough
> free space in your database.
> Thanks
> Hari
> US Technology
>
> Drop and re-create a clustered index. "Dan Wunder"
> <anonymous@.discussions.microsoft.com> wrote in message
> news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> > Hi,
> >
> > On the databases I manage I notice high (50%+) extent
> > fragmentation when I run DBCC SHOWCONTIG against the
> > tables' indexes. I've run dbcc indexdefrag, dbcc
> > dbreindex, and create index ...with drop existing on these
> > indexes and they still show the same fragmentation even
> > after an UPDATE STATISTICS.
> > Is there any way to eliminate this beyond completely
> > dropping the indexes and starting from scratch (not a
> > realistic option)? The procedure I run has virtually
> > eliminated the logical fragmentation- are there major
> > performance issues with leaving them as-is?
> >
> > -Dan
>

Indexing Results of Stored Proc (or new table created by one)

Hi,

I am using data from multiple databases and/or queries. It would greatly
simplify and speed things up if I could use CONTAINS in processing the
results. However, "CONTAINS" requires the data to be indexed. Due to the
amount of processing, I think it would be faster even if I had to re-index
every time.

For example, I would like to do something like this (simplified to
illustrate the desired functionality... This should show all of the words
from one table that are not contained in their current or inflectional forms
within another table):

SELECT W1.Content
FROM
(SELECT Word AS Content
FROM MyTable) W1
LEFT OUTER JOIN
(SELECT Phrase AS Content
FROM MyOtherTable) W2
ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
WHERE W2.Content IS NULL

Can the results of a procedure be indexed? If not, can I drop the results
into a new table and trigger an automatic index of it, pausing the procedure
until the indexing is done?

Or, it there another way?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simon|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simonsql

Indexing question

Hello All,
Have seen 2 different databases now both on 2005 STD edition different
servers.
That still indicate fragmentation in their indexes (both clustered and non
clustered) immediately post rebuild proccess. I am looking for broad sweeping
statements to stimulate my thought processes as to why this is occuring.
Thanks in advance,
How many pages do they have? If it is less than 8 you will never get rid of
all the fragmentation since they used mixed extents. But if you show the
results it may help.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:62DBFA8F-1CC4-42ED-A7E4-0AFFB4626BEB@.microsoft.com...
> Hello All,
> Have seen 2 different databases now both on 2005 STD edition different
> servers.
> That still indicate fragmentation in their indexes (both clustered and non
> clustered) immediately post rebuild proccess. I am looking for broad
> sweeping
> statements to stimulate my thought processes as to why this is occuring.
> Thanks in advance,
>

Indexing question

Hello All,
Have seen 2 different databases now both on 2005 STD edition different
servers.
That still indicate fragmentation in their indexes (both clustered and non
clustered) immediately post rebuild proccess. I am looking for broad sweeping
statements to stimulate my thought processes as to why this is occuring.
Thanks in advance,How many pages do they have? If it is less than 8 you will never get rid of
all the fragmentation since they used mixed extents. But if you show the
results it may help.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:62DBFA8F-1CC4-42ED-A7E4-0AFFB4626BEB@.microsoft.com...
> Hello All,
> Have seen 2 different databases now both on 2005 STD edition different
> servers.
> That still indicate fragmentation in their indexes (both clustered and non
> clustered) immediately post rebuild proccess. I am looking for broad
> sweeping
> statements to stimulate my thought processes as to why this is occuring.
> Thanks in advance,
>

Indexing

If I need to add indexes to a large number of SQL Server 2005
tables/databases tables, can I do it during the day when the database is in
use? Or is this something that I should do after hours? Adding and
dropping indexes seems pretty benign (assuming you pick the right fields),
but I'm new at this, if you can't tell. Thanks!
You can add indexes using the ONLINE option (some restrictions, see Books Online, the updated
version, CREATE INDEX). ONLINE mean that SQL Server will not acquire a lock on the data while the
index is added. ONLINE is only available on Enterprise Edition.
If you don't use ONLINE, the table will be locked by shared lock if you create non-clustered index,
or exclusive lock if you create clustered index. This will obviously have an impact of those who try
to access the tables while the index is being created.
Apart from the locking and blocking situation, the will be an obvious resource usage while the index
is being created.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Huddle" <chuddle@.NOSPAMtimeplus.com> wrote in message
news:OMlTwkAWGHA.5148@.TK2MSFTNGP12.phx.gbl...
> If I need to add indexes to a large number of SQL Server 2005 tables/databases tables, can I do
> it during the day when the database is in use? Or is this something that I should do after hours?
> Adding and dropping indexes seems pretty benign (assuming you pick the right fields), but I'm new
> at this, if you can't tell. Thanks!
>
|||ummm, adding indexes during the day pretty much locks out the users
from accessing that table while the indexes are being made.
if the users don't mind, then neither do I !!!!!!!!!!

Indexing

If I need to add indexes to a large number of SQL Server 2005
tables/databases tables, can I do it during the day when the database is in
use? Or is this something that I should do after hours? Adding and
dropping indexes seems pretty benign (assuming you pick the right fields),
but I'm new at this, if you can't tell. Thanks!You can add indexes using the ONLINE option (some restrictions, see Books On
line, the updated
version, CREATE INDEX). ONLINE mean that SQL Server will not acquire a lock
on the data while the
index is added. ONLINE is only available on Enterprise Edition.
If you don't use ONLINE, the table will be locked by shared lock if you crea
te non-clustered index,
or exclusive lock if you create clustered index. This will obviously have an
impact of those who try
to access the tables while the index is being created.
Apart from the locking and blocking situation, the will be an obvious resour
ce usage while the index
is being created.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Huddle" <chuddle@.NOSPAMtimeplus.com> wrote in message
news:OMlTwkAWGHA.5148@.TK2MSFTNGP12.phx.gbl...
> If I need to add indexes to a large number of SQL Server 2005 tables/data
bases tables, can I do
> it during the day when the database is in use? Or is this something that
I should do after hours?
> Adding and dropping indexes seems pretty benign (assuming you pick the rig
ht fields), but I'm new
> at this, if you can't tell. Thanks!
>|||ummm, adding indexes during the day pretty much locks out the users
from accessing that table while the indexes are being made.
if the users don't mind, then neither do I !!!!!!!!!!

Indexing

If I need to add indexes to a large number of SQL Server 2005
tables/databases tables, can I do it during the day when the database is in
use? Or is this something that I should do after hours? Adding and
dropping indexes seems pretty benign (assuming you pick the right fields),
but I'm new at this, if you can't tell. Thanks!You can add indexes using the ONLINE option (some restrictions, see Books Online, the updated
version, CREATE INDEX). ONLINE mean that SQL Server will not acquire a lock on the data while the
index is added. ONLINE is only available on Enterprise Edition.
If you don't use ONLINE, the table will be locked by shared lock if you create non-clustered index,
or exclusive lock if you create clustered index. This will obviously have an impact of those who try
to access the tables while the index is being created.
Apart from the locking and blocking situation, the will be an obvious resource usage while the index
is being created.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Huddle" <chuddle@.NOSPAMtimeplus.com> wrote in message
news:OMlTwkAWGHA.5148@.TK2MSFTNGP12.phx.gbl...
> If I need to add indexes to a large number of SQL Server 2005 tables/databases tables, can I do
> it during the day when the database is in use? Or is this something that I should do after hours?
> Adding and dropping indexes seems pretty benign (assuming you pick the right fields), but I'm new
> at this, if you can't tell. Thanks!
>|||ummm, adding indexes during the day pretty much locks out the users
from accessing that table while the indexes are being made.
if the users don't mind, then neither do I !!!!!!!!!!

Indexes..

I have a database with 1 table and 4 fields. No relational link with
anything or other databases. I add about 20 records per second and
periodically query for information. My question, should I create an index
for this table? If so why? If so why? I thought a index would make sense if
it was relation one field to another.
Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
hi ralph,
indexes are "particularly" useful in SELECT statements.
Some examples where indexes will be useful are:
a)indexes will be useful for a query against a table in which columns
frequently involved in search conditions of a select query using WHERE
clause.
b)select statements that involves joins and grouping are frequently done
against specific columns.
c)select queries that involves, returning of distinct values from the table.
d)select queries involving order by cluases. Creation of indexes on the
columns of table that are part of "order by" clause eliminates the need for
SQL Server to sort the data because the rows are already sorted. This
improves query performance.
As you said the table inserts almost 20 records per second which , shows
high volume of data manipulation operation on the table which may get
affected if you create unncessary index on the table.
So careful while creating index on the table and give consideration to the
queries that are run against the table. Mainly SELECT,UPDATE that involves
where clauses/joins.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Indexes..

I have a database with 1 table and 4 fields. No relational link with
anything or other databases. I add about 20 records per second and
periodically query for information. My question, should I create an index
for this table? If so why? If so why? I thought a index would make sense if
it was relation one field to another.
Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpghi ralph,
indexes are "particularly" useful in SELECT statements.
Some examples where indexes will be useful are:
a)indexes will be useful for a query against a table in which columns
frequently involved in search conditions of a select query using WHERE
clause.
b)select statements that involves joins and grouping are frequently done
against specific columns.
c)select queries that involves, returning of distinct values from the table.
d)select queries involving order by cluases. Creation of indexes on the
columns of table that are part of "order by" clause eliminates the need for
SQL Server to sort the data because the rows are already sorted. This
improves query performance.
As you said the table inserts almost 20 records per second which , shows
high volume of data manipulation operation on the table which may get
affected if you create unncessary index on the table.
So careful while creating index on the table and give consideration to the
queries that are run against the table. Mainly SELECT,UPDATE that involves
where clauses/joins.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Indexes slowing down BULK INSERT

I've been doing some experiments with speeding up copying tables of
approximately 1 million rows between databases using BCP and BULK INSERT.

I noticed that the total time for removing the indexes (non-clustered) and
then recreating them after the BULK INSERT was significantly less than just
doing the BULK INSERT with the indexes left there, even though I specified
TABLOCK.

I would have expected SQL Server not to update the index until the insert
completed (given the table lock) and so removing the indexes would have no
effect. Can anyone explain why removing the indexes should speed it up?

This is on SQL Server 7.

Cheers
DaveDavid Sharp (no email address supplied) writes:
> I've been doing some experiments with speeding up copying tables of
> approximately 1 million rows between databases using BCP and BULK INSERT.
> I noticed that the total time for removing the indexes (non-clustered)
> and then recreating them after the BULK INSERT was significantly less
> than just doing the BULK INSERT with the indexes left there, even though
> I specified TABLOCK.
> I would have expected SQL Server not to update the index until the insert
> completed (given the table lock) and so removing the indexes would have no
> effect. Can anyone explain why removing the indexes should speed it up?

I have not studied this case very closely. But a few observations: if
you supplied a batch size with /b, SQL Server had no choice but to
maintain the indexes while loading, since each batch is committed
separately.

When running some bulk-loading recently, I notice that when loading on
a completely unindexed table, BCP reported the copied rows swiftly, and
then completely directly, whereas on indexed tables there was a delay
from when all rows had been loaded until the command had completed,
which I supposed was spent on rebuilding indexes. I did not use TABLOCK.

I should add that I was working on SQL 2000, and the behaviour I saw
may reflect an improvement from SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns941B1079D8D3Yazorman@.127.0.0.1...
> David Sharp (no email address supplied) writes:
> > I've been doing some experiments with speeding up copying tables of
> > approximately 1 million rows between databases using BCP and BULK
INSERT.
> > I noticed that the total time for removing the indexes (non-clustered)
> > and then recreating them after the BULK INSERT was significantly less
> > than just doing the BULK INSERT with the indexes left there, even though
> > I specified TABLOCK.
> > I would have expected SQL Server not to update the index until the
insert
> > completed (given the table lock) and so removing the indexes would have
no
> > effect. Can anyone explain why removing the indexes should speed it up?
> I have not studied this case very closely. But a few observations: if
> you supplied a batch size with /b, SQL Server had no choice but to
> maintain the indexes while loading, since each batch is committed
> separately.

I have studied this somewhat closely. :-)

And what Erland says about the /b is a critical part of it. We on a
quarterly basis have to load a multimillion set of rows. As an experiment
recently (to reconfirm my thoughts) I did a test load on a backup server.
It took I believe 3 days to do the load. This was without removing the
non-clustered indices first.

For the actual load I I removed the non-clustered indices (but kept the
clustered index since the data is BCP'd out of another table already in
order). This took well under 12 hours. (Actually not 100% sure how long it
took since I started it around midnight and the scripts finished sometime
before 9:00 AM). This included re-applying the indices to the table.

I don't know how much of a difference there would be if the data had been
completely unordered.

> When running some bulk-loading recently, I notice that when loading on
> a completely unindexed table, BCP reported the copied rows swiftly, and
> then completely directly, whereas on indexed tables there was a delay
> from when all rows had been loaded until the command had completed,
> which I supposed was spent on rebuilding indexes. I did not use TABLOCK.
> I should add that I was working on SQL 2000, and the behaviour I saw
> may reflect an improvement from SQL7.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Indexes question

Hi Gurus,
When I query a table using sp_MShelpindex in one of my databases I see
following output:-
Name Status Indid
pk_ACTIONS 18450 1
_WA_COMPLETE_6FB49575 8388704 2
Please tell me what are these %_WA_% objects. Are these statistics or
automatically generated indexes created by index tuning wizard?
Thanks in advance
ManuThe "indexes" with names in that format are not indexes, just
statistics. They are not created by the tuning wizard, but they SQL
Server in normal operation. Simply ignore them and you will be fine.
Roy Harvey
Beacon Falls, CT
On Wed, 19 Sep 2007 13:04:04 -0700, manu
<manu@.discussions.microsoft.com> wrote:
>Hi Gurus,
>When I query a table using sp_MShelpindex in one of my databases I see
>following output:-
>Name Status Indid
>pk_ACTIONS 18450 1
>_WA_COMPLETE_6FB49575 8388704 2
>Please tell me what are these %_WA_% objects. Are these statistics or
>automatically generated indexes created by index tuning wizard?
>Thanks in advance
>Manu