Exception when selecting from SYSINDEXES in SQL Server 2005

During our course of troubleshooting queries taking long time for compilation, co-incidentally we found that SELECT * FROM SYSINDEXES was giving us an exception. We were getting the following error in Management Studio whenever querying SYSINDEXES

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

However SELECT * FROM SYS.INDEXES came back clean

***Please note: SYSINDEXES is a backward compatibility view containing SQL Server 2000 ROWMODCTR

From the error log:

Error log:

2007-10-16 17:48:38.16 spid57 * Exception Address = 01115322 Module(sqlservr+00115322)

2007-10-16 17:48:38.16 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION

2007-10-16 17:48:38.16 spid57 * Access Violation occurred reading address 00000000

2007-10-16 17:48:38.16 spid57 * Input Buffer 70 bytes -

2007-10-16 17:48:38.16 spid57 * select * from sysindexes

2007-10-16 17:48:38.21 spid57 Stack Signature for the dump is 0x2B7E7932

2007-10-16 17:48:42.96 spid57 External dump process return code 0x20000001.

External dump process returned no errors.

2007-10-16 17:48:42.96 Server Error: 17310, Severity: 20, State: 1.

2007-10-16 17:48:42.96 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

We did observe lot of objects in SYSOBJECTS starting with _DTA, probably created by incomplete operation of DTA something similar to hypothetical indexes. During analysis we found that there were 13204 hypothetical indexes and 1367 views. For some reason normal processing of DTA was interrupted and there were leftovers. Within materialized hypothetical views there is a problem during ROWMODCTR calculation.

Conclusion:

The actual problem is during attempt to calculate SQL Server 2000 (Shiloh) ROWMODCTR for old style SYSINDEXES we were not maintaining the rowmodctr on hypothetical indexed views (The issue is due to invalid row modification counter for hypothetical indexed views.)

We confirmed by running checkdb that this is not due to any inconsistency in the database. From other side existence of _DTA_ objects affected optimizer behavior and server performance in general. So we may run into two issues because of existence of hypothetical indexes.

· Maintaining the rowmodctr on hypothetical indexed views, during calculation of number of modified rows.

· Performance issue caused by existence of hypothetical indexes

Resolution:

1. Take a Database Backup

2. Delete hypothetical indexes

--Use the following script to generate dynamic SQL:

set nocount on

go

select 'drop index ['+object_name(object_id)+'].['+name+']' from sys.indexes

where name like '%[_]dta[_]%' and name is not null

set nocount off

go

****Please note that deleting all the indexes suggested/created by DTA may cause performance degradation.

3. Delete views

- Use following script to generate dynamic SQL

set nocount on

go

select 'drop view ['+name+']' from sys.objects

where name like '%[_]dta[_]%'

set nocount off

go

4. Run dbcc checkdb

5. Re-index the entire Database

The above steps resolved both the issues, query compilation time for the queries and there were no more exceptions when selecting data from SYSINDEXES

Abhishek Soni
Support Engineer, Microsoft SQL Server.

Reviewed By
Mukesh Nanda
Technical Lead, Microsoft SQL Server.