Quick SQL Server 7.0 index question

[xyz]

At the moment I only know enough about SQL Server to create the databases I
need as a backend for the various internal desktop/web applications I write.
I haven't ever had the time to get seriously involved with internal workings
of SQL Server and we are far too small to employ a DBA.

With any new application performance isn't a problem as the number of
records is very low. Over the life of the application the number of records
obviously increases and performance can start to suffer. My normal cure is
to use the Query Analyzer to suggest and implement indexes that normally get
things back up to speed.

I was wondering whether SQL Server automatically keeps the created indexes
up to date or do I need to be doing anything else either manually or as a
scheduled job? The databases all have auto create statistics and auto update
statistics enabled.

Any pointers gratefully received

Thanks

Alan

[louis]

Ensure the indexes are periodically defragged. Either by a maintenance
plan or DBCC DBReindex. See BOL for details.

[Erland Sommarskog]

xyz (so*****@somewhere.com) writes:

I was wondering whether SQL Server automatically keeps the created
indexes up to date or do I need to be doing anything else either
manually or as a scheduled job? The databases all have auto create
statistics and auto update statistics enabled.

What can happen with indexes - as well with the data pages them selves -
is that they can get fragmented. Essentially this means that that the
data is not stored as compactly as it could be. Recall that SQL Server
stores data on pages of 8192 bytes size. And these pages are gathered
eight by eight in extents. One extent is usually only for one table
(but the first page for a table goes into a mixed extent.) If there are
lots of insertions and deletions, you can end up with extents with
only some single rows in them. This leads to that SQL Server will have
to perform more disk reads to get the same amount of data.

The easiest way to approach this is to set up a maintenance job that
performs a DBCC DBREINDEX on all tables. (Although this is not the
most effecient. Tables with little fragmentation are best left alone.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[xyz]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

xyz (so*****@somewhere.com) writes:

I was wondering whether SQL Server automatically keeps the created
indexes up to date or do I need to be doing anything else either
manually or as a scheduled job? The databases all have auto create
statistics and auto update statistics enabled.

What can happen with indexes - as well with the data pages them selves -
is that they can get fragmented. Essentially this means that that the
data is not stored as compactly as it could be. Recall that SQL Server
stores data on pages of 8192 bytes size. And these pages are gathered
eight by eight in extents. One extent is usually only for one table
(but the first page for a table goes into a mixed extent.) If there are
lots of insertions and deletions, you can end up with extents with
only some single rows in them. This leads to that SQL Server will have
to perform more disk reads to get the same amount of data.

The easiest way to approach this is to set up a maintenance job that
performs a DBCC DBREINDEX on all tables. (Although this is not the
most effecient. Tables with little fragmentation are best left alone.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks to both you and louis for the imformation.