Re-indexing required?

Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>