SQL Diagnostic Manager allows you to customize your monitored SQL Server properties within PowerShell.
With PowerShell you can:
- Edit general settings
- Edit query monitor settings
- Exclude applications, databases, and SQL text within PowerShell
- Disable replication collection settings
- Customize maintenance mode settings
Edit general settings
Edit the general settings of your monitored SQL Server with the following PowerShell commands:
| Action | Cmdlets |
|---|---|
| Associate tags to a server | addtag |
Disassociate tags from a server | removetag |
Schedule data collection | ScheduledCollectionIntervalMinutes -DatabaseStatisticsIntervalMinutes |
Enable extended data collection | ExtendedSessionDataCollection 1 |
Disable extended data collection | ExtendedSessionDataCollection 0 |
Set SQL Authentication credentials | Credential sa |
Set Windows Authentication credentials | WindowsAuthentication |
Enable encrypt connection | EncryptConnection 1 |
Disable encrypt connection | EncryptConnection 0 |
Enable Trust server certificates | TrustServerCertificates 1 |
Disable Trust server certificates | TrustServerCertificates 0 |
Edit query monitor settings
Customize the query monitor settings of your monitor SQL Server with the following PowerShell commands:
| Action | Cmdlets |
|---|---|
Enable query monitoring | QMEnabled |
Disable query monitoring | QMDisabled |
Capture SQL batches | QMCaptureBatches 1 |
Capture SQL statements | QMCaptureStatements 1 |
Capture stored procedure and triggers | QMCaptureProcs 1 |
Setup Threshold -Duration | QMQueryDuration |
Setup Threshold -Logical disk reads | QMLogicalDiskReads |
Setup Threshold -CPU Usage | QMCpuUsage |
Setup Threshold -Physical disk writes | QMPhysicalDiskWrites |
Exclude applications, databases and SQL text within PowerShell
Exclude applications, databases, and SQL text with the following PowerShell commands:
| Action | Cmdlets |
|---|---|
Exclude applications | QMExcludedApps |
Exclude databases | QMExcludedDatabases |
Exclude SQL text | QMExcludedSql |
Disable replication collection
Disable replication statistics collection with the following PowerShell command:
ReplicationStatisticsDataCollection 0
Customize table statistics collection
Customize table statistics collection with the following PowerShell commands:
QTStartTime -QTDays -QTReorgMinTableSizeK -QTExcludedDatabases
Customize maintenance mode settings
Customize maintenance mode settings with the following PowerShell commands:
| Maintenance mode options | Cmdlets |
|---|---|
| Never | MMNever |
Until further notice | MMAlways |
Recurring every week at the specified time | MModeStartTime -MModeDuration |
Occurring once at the specified time | MMOnce -MModeStartDate -MModeEndDate |
PowerShell allows you to set up several properties in one command-line. On the PowerShell window, use Set-SQLdmMonitoredInstance followed by the corresponding cmdlets of the properties you want to edit. For example, consider the properties listed below:
- Enable Query Monitoring
- Capture Batches
- No capture procedures
- Set Query duration at 200 (ms)
- Set Logical disk reads at 20
- Set CPU usage at 100
- Set Physical disk writes at 10
- Exclude Application: MyExcludedApp
- Exclude Databases: DB1, DB2, and DB3
You can customize these settings with the following PowerShell command-line:
Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name QA- Name Server\Instance) -QMEnabled -QMCaptureBatches 1 –QMCaptureProc 0 -QMQueryDuration 200
-QMLogicalDiskReads 20 -QMCpuUsage 100 -QMPhysicalDiskWrites 10 -QMExcludedApps MyExcludedApp -QMExcludedDatabases DB1,DB2,DB3