How to fix corrupted Index of SQL Server ?
Question
Friday, August 4, 2017 12:09 PM
A consultant says that "The longest running queries were all faulting due to missing indexes on tables" and advises that “These may be able to be fixed by running a repair on the database.”. We don't have the chance to contact him again (My supervisor doesn't prefer to pay consultant fee). After asking in this Forum re missing index, we believe that what he means may be corrupted index (Not missing Index).
We have been running Rebuild Index task for that database weekly.
When we run DBCC CHECKDB, it reports that
CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB_Name'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
1) Does it mean that there is no corrupted index ?
2) Besides, can I confirm the command to fix corrupted index is "dbcc checkdb('db name', repair_rebuild)" ?
Thanks again.
All replies (9)
Friday, August 4, 2017 12:43 PM ✅Answered
1) It means no database corruption (allocation/consistency errors) were found. Nothing to do with index health like fragmentation.
2) This command is for attempting to repair any database corruption, not improving index health.
DBCC CHECKDB is for checking integrity/consistency (corruption) of databases, not necessarily index health (ie: fragmentation). Repair/rebuild operation attempts to repair corruption, not improve health of indexes specifically.
Indexes can become fragmented over time, due to many insert/update/delete operations, and therefore require periodic maintenance like an index reorg/rebuild job (or maintenance plan) which will defragment indexes as much as possible. Indexes which are not periodically maintained like this can become so fragmented that it leads to poor query performance. Perhaps that's what the 'consultant' was meaning.
If you suspect problems with an index, you can simply drop and recreate the index, which is essentially the same as index rebuild operation.
Don't confuse Database Integrity check (DBCC CHECKDB) with Index maintenance (reorg/rebuild). They are different. Refer to following articles for more in depth explanation:
Reorganize and Rebuild Indexes
HTH,
Phil Streiff, MCDBA, MCITP, MCSA
Saturday, August 5, 2017 3:32 PM ✅Answered
1) Does it mean that there is no corrupted index ?
2) Besides, can I confirm the command to fix corrupted index is "dbcc checkdb('db name', repair_rebuild)" ?
Thanks again.
You must confirm from the person as what actually he is trying to say. There is no relation between missing index and corruption.
If DBCC checkdb comes out clean you have no corruption in data file, there might still be in transaction log file and checkdb does not "completely" checks integrity of log file
Any checkdb repair command is not guaranteed to fix corruption, you must have proper backup plan just and that must be used to recover from corruption not repair option, repair option should be your last method to get out of corruption
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Friday, August 4, 2017 1:22 PM
CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB_Name'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which means the DBCC CHECKDB has been passed.
As you running Rebuild Index task for that database weekly. During week days what is the index fragmentation level? If you see fragmentation for lesser count pages don't be scare it is expected.
If you see heavy fragmentation on week days, it is suggestible to run Indes reorganize jobs Bi-Weekly
Coming to missing indexes - creating all the missing is not advisable - Please check with your Apps team and consider them regarding new indexes.
Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)
Friday, August 4, 2017 1:27 PM
1) it could mean that the index is disabled or perhaps there is an untrusted constraint. If there were database corruption it could be localized to an index which could be solved by dropping and recreating it. If this was a non-clustered index there would be no data loss. If it was a clustered index there could be potential for data loss.
2) I would drop and recreate the non-clustered index - but this does not appear to be your problem. If there is clustered index corruption, I would try to export the data using bcp to see if it is exportable and to determine how severe the clustered index corruption is.
The checkdb statement might solve database corruption, but you might lose data.
It does sound like your consultant is using the wrong terminology and means to say there are missing indexes, or queries which could take advantage of indexing.
Friday, August 4, 2017 1:34 PM
What you posted are 2 different things:
"The longest running queries were all faulting due to missing indexes on tables" would seem to indicate you need to ADD some indexes to support your query.
“These may be able to be fixed by running a repair on the database.” would indicate he thinks there is database corruption. That is a totally different thing than the first statement. I would ask WHY he thinks repair would do anything.
If you just need to add indexes, did he suggest indexes or just a general statement?
Sunday, August 6, 2017 8:17 AM
Hi,
Many thanks for reply from fellows.
Is there any script for us to rebuild indexes for all tables in database ?
Thanks
Sunday, August 6, 2017 6:31 PM
Hi,
Many thanks for reply from fellows.
Is there any script for us to rebuild indexes for all tables in database ?
Thanks
Yep please see
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Monday, August 7, 2017 12:09 AM
Dear Shashank,
Many thanks for your advice.
I have run the script and to my surprise, the script finishes in less than 30 seconds. After listing all databases in the instance, we only get the following lines. Databases are running Compatibility Level of 90 and 100. Besides, fragmentation of some tables should be more than 80%.
IDD objectname indexname Schemaname AFIP
IDD objectname indexname Schemaname AFIP
Thanks
Monday, August 7, 2017 7:39 AM
The script runs for all databases you can edit it to just include the single database. You can do that from sys.databases where 'db_name.'
The script omits indexes which have page_count < 2000. It could be possible that you have small indexes and so all were omitted. As a fact you do not need to perform any maintenance activity on small indexes
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it