asked
Server error - Using Solarwinds
My org has recently implemented Solarwinds to monitor various aspect of SQL (plus others). Yesterday morning we started monitoring with the basic template. This template has a number of counters, but one of them is an index stats capture. DECLARE @db_id SMALLINT; SET @db_id = DB_ID(N'master'); BEGIN IF EXISTS ( SELECT IPS.avg_fragmentation_in_percent AS [Fragmentation ( %)] , OBJECT_NAME(IPS.object_id) AS [TableName with fragmentation] , SI.name AS [IndexName] , IPS.Index_type_desc , IPS.avg_fragment_size_in_pages , IPS.avg_page_space_used_in_percent , IPS.record_count , IPS.ghost_record_count , IPS.fragment_count FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') IPS JOIN sys.tables ST WITH ( NOLOCK ) ON IPS.object_id = ST.object_id JOIN sys.indexes SI WITH ( NOLOCK ) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id WHERE IPS.avg_fragmentation_in_percent > 0 ) BEGIN SELECT IPS.avg_fragmentation_in_percent AS [Fragmentation (%)] , OBJECT_NAME(IPS.object_id) AS [TableName with fragmentation] , SI.name AS [IndexName] , IPS.Index_type_desc , IPS.avg_fragment_size_in_pages , IPS.avg_page_space_used_in_percent , IPS.record_count , IPS.ghost_record_count , IPS.fragment_count FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') IPS JOIN sys.tables ST WITH ( NOLOCK ) ON IPS.object_id = ST.object_id JOIN sys.indexes SI WITH ( NOLOCK ) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id WHERE IPS.avg_fragmentation_in_percent > 0 ORDER BY 1 DESC END ELSE SELECT '0' END Whats the problem you ask... When this is executed after the 4th time as SQL Server dump is created. Even via SSMS I get **"A severe error occurred on the current command"** [link text][1]. I have run the same script on 2005/2008/R2 and I am unable to reproduce the error. However, it happens on all SQL 2012 instance (with varying builds). Even on 11.0.3381. One an old build of 2012 I get a different error **Location: sosmemobj.cpp:2763 Expression: pvb->FInUse () SPID: 73 Process ID: 1552** For now I have obviously disabled the monitoring.Any help would be appreciated? [1]: /storage/temp/1155-sqldump0081.txt
This site works by voting. If any of the answers below was helpful, please indicate each that was helpful by clicking on the thumbs up symbol next to them. If any one of the answers below lead to a solution, please indicate that by clicking on the check box next to that one answer. Even if the answer is your answer.
Less speed etc etc Looks like it is a confirmed [bug][1]. [1]:
https://connect.microsoft.com/SQLServer/feedback/details/796382/sql-2012-sys-dm-db-index-physical-stats-produce-errors-in-exists-clause
Couple suggestion by glancing your query 1) You are using Mode = “Detail “ that will take more seconds and load to execute than "limited" mode . I would suggest to use limited if possible 2) You are also running query twice a) First Trying to find out if data exists and if exists than rerun same query with order by ( sort operation ) I would suggest run query one time and store result in CTE or temp table than run if exists command out of CTE this will reduce load on server also dual execution Hope this help
16 People are following this question.
