SQL Server Indexes

#1

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table get
slower. However if we drop and re-create the index the querys are now quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#2

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#3

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#4

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#5

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#6

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#7

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#8

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#9

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?

#10

QSIDeveloper,

Instead of dropping and recreating indexes, you probably just need to update
the statistics. Check your settings for automatic updating of statistics.
http://support.microsoft.com/kb/195565

If necessary, per that article, you might do specific updates.
http://technet.microsoft.com/en-us/l.../ms187348.aspx

UPDATE STATISTICS dbo.Table1Name
UPDATE STATISTICS dbo.Table2Name

RLF

"QSIDeveloper" <QSIDeveloper (AT) newsgroup (DOT) nospam> wrote

Quote:

We have a User table with 2million records in them, that has 4 indexes set
for faster Search. At an average we have 187k new records coming into the
table per day. We see every 4 or 5 days the queries we run on the table
get
slower. However if we drop and re-create the index the querys are now
quick.

How do we identify the issue which gets resolved by dropping and creating
the index. Is there an dbcc command that we can use to identify the issue?