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
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:
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.
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
1. Take a Database Backup
2. Delete hypothetical indexes
--Use the following script to generate dynamic SQL:
set nocount on
select 'drop index ['+object_name(object_id)+'].['+name+']' from sys.indexes
where name like '%[_]dta[_]%' and name is not null
set nocount off
****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
select 'drop view ['+name+']' from sys.objects
where name like '%[_]dta[_]%'
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.
Though it's wouldn't be a best practice to do so, to ensure the objects weren't created by users, you can consider interrogating for is_hypothetical:
SET NOCOUNT ON
SELECT 'DROP INDEX ' + OBJECT_NAME(OBJECT_ID) + '.' + name as SQLtext
WHERE name LIKE '%[_]dta[_]%'
AND name IS NOT NULL
AND is_hypothetical = 1
SET NOCOUNT OFF