January 23, 2003 at 8:14 am
Environment: SQL 2000 SP3, running on Server 2000 SP3. Stand alone dedicated SQL Server. 2GB ram. (more ram than DB space).
DB contains about 100 tables. Most have primary keys and several other indexes established. Very few (one or two) FK's.
Symptoms: Accessing data is extreemly slow or can't be done at all. This typically occurs in the AM. It appears that our indexes (on random tables) get damaged somehow.
Example, do a select * from a table and you get exactly what you would expect (doesn't matter if it contains 400 rows or over 100,000 rows). Do a order by on the same table and the DB times out. Do a where table.fld = 'some unique field value' and DB times out.
Rebuild the indexes on the tables and the issue goes away.
Most indexes and PK's contain varchar data as part of their values.
We've had to resort to rebuilding indexes as a maintenance routine.
Any ideas????
Thanks in advance for any feedback you may provide.
Len
January 23, 2003 at 8:44 am
Try to run DBCC CHECKDB to see if there are any errors reported.
Run UPDATE STATISTICS to your database If there is significant change in the key values in the index or If a large amount of data in an indexed column has been added, changed, or removed.
Generate execution plan to your SQL statement to see whether correct indexes are being used.
January 27, 2003 at 12:44 pm
I agree with the DBCC CHECKDB. However, we had a similar problem recently and found the issue was really a bad disk in the array set. You probably want to perform some hardware tests as well.
-Guarddata
January 31, 2003 at 2:26 am
If database is quite old and no rebuild index is done then it's possible.....because of logical and external fragmentation.
Check dbcc showcontig for that table if scan density is not nearing 100% then move this table to different filegroup and then fire this query.....
Check dbcc showcontig now and the query will work.......
For moving the table drop the clustered indezx and create it back on different filegroup.....
Please revert.....
Cheers
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply