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
To use SQL Diagnostic Manager snapin, you have to enable the provider and register the SQLdm drive.
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