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 statements 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:
Cmdlet Name | Description | Examples |
---|---|---|
-addtag | Associates tags to a server. Note that any tags that are specified have to already exist. |
|
| Disassociates tags from a server. Note that any tags that are specified have to already exist. |
|
| Specifies how often (in minutes) scheduled data collection takes place. |
|
-DatabaseStatisticsIntervalMinutes | Specifies how often (in minutes) database statistics collection takes place. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name |
| Enables or disables extended data collection. |
|
| Sets SQL Authentication credentials. |
|
| Enables or disables encrypt connection. |
|
| Enables or disables trust server certificates. |
|
Edit query monitor settings
Customize the query monitor settings of your monitor SQL Server with the following PowerShell commands:
Cmdlet Name | Description | Examples |
---|---|---|
| Enables query monitoring. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name |
| Disables query monitoring. |
|
| Captures SQL batches. |
|
| Captures SQL statements. |
|
| Captures stored procedure and triggers. |
|
| Sets up threshold Duration (in milliseconds). |
|
| Sets up threshold Logical disk reads. |
|
| Sets up threshold CPU Usage (in milliseconds). |
|
| Sets up threshold Physical disk writes. |
|
Exclude applications, databases and SQL statements within PowerShell
Exclude applications, databases, and SQL statements with the following PowerShell commands:
Cmdlet Name | Description | Examples |
---|---|---|
| Excludes applications from the query monitor. |
|
| Excludes databases from the query monitor. |
|
| Excludes SQL statements from the query monitor. |
|
Disable replication collection
Disable replication statistics collection with the following PowerShell command:
Cmdlet Name | Description | Example |
---|---|---|
| Disables replication statistics collection. |
|
Customize table statistics collection
Customize table statistics collection with the following PowerShell commands:
Cmdlet Name | Description | Example |
---|---|---|
| Specifies the time of day for quiet time collection to occur. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name |
| Specifies the days of the week to perform quiet time collection. | |
| Specifies the quiet time minimum table size (in kilobytes) to collect reorganization statistics. | |
| Specifies the list of databases to exclude from quite time data collection. |
Customize maintenance mode settings
Customize maintenance mode settings with the following PowerShell commands:
Cmdlet Name | Description | Examples |
---|---|---|
| Sets maintenance mode to Never. Note that SQL Diagnostic Manager will collect data according to its normal scheduled collection interval. |
|
| Sets maintenance mode to Until further notice. Schedule collection will not occur. |
|
| Sets maintenance mode to Recurring every week at the specified time. |
|
-MModeDays | Specifies the days of the week recurring maintenance mode will be effective. | |
-MModeStartTime | Specifies the start time for recurring maintenance mode. | |
-MModeDuration | Specifies the length of time for recurring maintenance mode. | |
| Sets maintenance mode to Occurring once at the specified time. |
|
-MModeStartDate | Specifies the start date and time for one time maintenance mode. | |
-MModeEndDate | Specifies the end date and time for one time maintenance mode. |
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