Bring back the ability to list fragments of T-SQL to reduce Blocking Process alerts, please
By FAR the largest number of abject NUISANCE Blocking Process alerts I get across 17 servers is due to a simple "How is fragmentation going?" query from a 3rd party product (Idera Defrag Manager).
Looks like this:
use [DBName] declare @indexId int declare @dbId int declare @tblId int declare @partitionId int set @partitionId = 0 select @dbId = ISNULL(DBID(),-1) if @dbId=-1 RAISERROR( N'Database does not exist or permission denied: %s', 16, 1, '[DBName]' ) else begin select @tblId = ISNULL(OBJECTID('[DBName].[dbo].[TableName]') ,-1) if @tblId=-1 RAISERROR( N'Table does not exist or permission denied: %s', 16, 1, '[DBName].[dbo].[TableName]' ) else begin select @indexId=indexid from sys.indexes where objectid=@tblId and name='PKtblForecast' if @tblId=-1 OR @indexId IS NULL RAISERROR( N'Index does not exist or permission denied: %s', 16, 1, '[PKtblForecast]' ) else begin select avgrecordsizeinbytes, avgpagespaceusedinpercent, pagecount, avgfragmentationinpercent from sys.dmdbindexphysicalstats(@dbId, @tblId, @indexId,@partitionId,'LIMITED') WHERE ([allocunittypedesc] = 'INROWDATA') END END END
Back in the days of SQL Monitor 3x, and maybe 4x, we could list fragments-to-ignore, and that reduced HUGE volumes of false-positive Alerts.
Either my abilities to find the equivalent are sadly lacking, or RG has removed this feature.
Please, please, either tell me where it's buried, or restore it!
I honestly don't care if this type of monitoring query is being blocked. Neither, actually do I care if it is the victim of a Deadlock - "it will run at the next scheduled slot".
Thanks, either way.
Version 6.0.16 has now been released and this includes the ability to add regular expression filters to blocking process alerts. We hope this completes your suggestion.
Thanks, SQL Monitor Team
Thank You so much Adam and Team. A great help will this be, Yoda.
to the best of my knowledge, this only existed for the long running query alert - the ability to specify particular SQL process names or queries to exclude from that alert. However, the example that you give illustrates the usefulness of this for blocking process alerts, so we'll keep an eye on the demand for this.