Re: SQL Server Indexes
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 10 Jun 2008 14:58:18 -0700
QSIDeveloper (QSIDeveloper@xxxxxxxxxxxxxxxx) writes:
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?
In addition to Russel's post, the slowdown may be due to fragmentation.
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Re: SQL Server Indexes
- Next by Date: how can export activity monitor process info data to txt or csv ?
- Previous by thread: Re: SQL Server Indexes
- Next by thread: how can export activity monitor process info data to txt or csv ?
- Index(es):
Relevant Pages
|
