CheckDB and i/o metric alert suppression windows

Luke L

Hello, I have a large database (Tb's) with many files as part of a DW system.  When I perform a CheckDB on that database we see significant Avg Disk read and write alerts.  We know this is going to occur, it's happening during a low use timeframe and we've done as much as possible to help out the i/o subsystem (local ssd's in a raid5 - yes I know raid 10 would be better but we don't have that many disks).   

Is it possible to add just the Disk avg read/write alerts to an alert suppression window for a scheduled timeframe? 

We still want any other alerts, but we're ok with this i/o load during this timeframe for this process.

I haven't found a way to incorporate this other than possibly updating the MaintenanceWindow-AlertsToExclude rows in settings.keyvaluepairs.  This seems like not a great solution as it is global and we want to see other i/o alerts form other systems.  

Does anyone have any suggestions on how to filter these alerts?
Thanks
-Luke.

Luke L

@Alex B, Thanks for the response and the confirmation.  PS looked like the best option for us and is what we'll be moving forward with.  

Thanks again,
-Luke.

Luke L

Rather than updating the alert suppression window in the database, I think we're going to go with just disabling this alert via the powershell module... for anyone else interested in this, the code will look something like this...

We'll put this code in the first agent job step and the reverse in the last job step, with the DBCC CheckDB in between.

<div>using module "RedgateSQM"</div><div><br></div><div>#Params</div><div>[string]$MonitorServer = "monitorservername"</div><div>[string]$ServerNameToUpdate = "myserver"</div><div>[string]$AlertName = "DiskReadTime"<br>[string]$Status = "Inherited"&nbsp; ## or "Disabled"<br></div><div><br>##Setup connection to sqlmonitor</div><div>$ErrorActionPreference = "Stop"</div><div>$MonitorServerURL = "http://$MonitorServer`:8080"</div><div>Connect-SqlMonitor -ServerUrl $MonitorServerURL -AuthToken "myauthtoken"</div><div><br></div><div><div>##Get the AlertType</div><div>$alertType = $AlertTypes.Contains($AlertName)</div><div><br></div><div>##Get the Monitored Object current alert settings</div><div>$MonitoredObject = Get-SqlMonitorMachine -Name $ServerNameToUpdate</div><div>$alertSettings = Get-SqlMonitorAlertSettings -MonitoredObject $MonitoredObject -AlertType $alertType&nbsp;</div><div><br></div><div>Write-Host "Current Setting: " $alertSettings.Status</div><div>##Make the update</div><div>Update-SqlMonitorAlertSettingsStatus -MonitoredObject $MonitoredObject -AlertType $alertType -Status $Status</div><div><br></div><div>##Get the updated settings</div><div>$alertSettings = Get-SqlMonitorAlertSettings -MonitoredObject $MonitoredObject -AlertType $alertType&nbsp;</div><div><br></div><div>Write-Host "Updated Setting: " $alertSettings.Status</div></div>

Thanks,
-Luke.

Alex B

Hi @Luke L

Disabling the specific alert for the specific entity with PowerShell is the best option I can think of as well, it's the only way to only disable the one alert for the one entity - as you have seen, the suppression windows are a group of alerts, and the setting for those group of alerts is global. 

The only alternative would be something more convoluted like having a separate base monitor for that one entity where you could change the suppression window settings (which would apply only for that base monitor as the repositories are separate), but again, that's convoluted and it's must more straightforward to run a bit of PowerShell before and after as you've mentioned.

Kind regards,
Alex