SQL Diagnostic Manager allows you to customize your monitored SQL Server properties within PowerShell.
With PowerShell you can:
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. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1) -addtag NewTag,Tag2
|
-removetag
| Disassociates tags from a server. Note that any tags that are specified have to already exist. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -removetag NewTag,Tag2
|
-ScheduledCollectionIntervalMinutes
| Specifies how often (in minutes) scheduled data collection takes place. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -ScheduledCollectionIntervalMinutes 10
|
-DatabaseStatisticsIntervalMinutes | Specifies how often (in minutes) database statistics collection takes place. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 )-DatabaseStatisticsIntervalMinutes 120 |
-ExtendedSessionDataCollection
| Enables or disables extended data collection. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -ExtendedSessionDataCollection 1
- Enables extended data collection.
Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -ExtendedSessionDataCollection 0
- Disables extended data collection.
|
-Credential
| Sets SQL Authentication credentials. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -Credential sa
- Uses SQL authentication. Note that PowerShell will prompt for the password.
Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) –WindowsAuthentication
- Uses the credentials of the SQLDM Collection Service to connect to the monitored instance when collecting statistics.
|
-EncryptConnection
| Enables or disables encrypt connection. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -EncryptConnection 1
- Enables encrypt connection.
Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -EncryptConnection 0
- Disables encrypt connection.
|
-TrustServerCertificates
| Enables or disables trust server certificates. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -TrustServerCertificates 1
Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -TrustServerCertificates 0
- 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 |
---|
-QMEnabled
| Enables query monitoring. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMEnabled |
-QMDisabled
| Disables query monitoring. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMDisabled
|
-QMCaptureBatches
| Captures SQL batches. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMEnabled -QMCaptureBatches 1
|
-QMCaptureStatements
| Captures SQL statements. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMEnabled -QMCaptureStatements 1
|
-QMCaptureProcs
| Captures stored procedure and triggers. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMEnabled -QMCaptureProcs 1
|
-QMQueryDuration
| Sets up threshold Duration (in milliseconds). | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMQueryDuration 20000
|
-QMLogicalDiskReads
| Sets up threshold Logical disk reads. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMLogicalDiskReads 20
|
-QMCpuUsage
| Sets up threshold CPU Usage (in milliseconds). | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMCpuUsage 500
|
-QMPhysicalDiskWrites
| Sets up threshold Physical disk writes. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMPhysicalDiskWrites 20
|
Exclude applications, databases and SQL statements within PowerShell
Exclude applications, databases, and SQL statements with the following PowerShell commands:
Cmdlet Name | Description | Examples |
---|
-QMExcludedApps
| Excludes applications from the query monitor. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMExcludedApps excludedApp1, excludedApp2, excludedApp3
|
-QMExcludedDatabases
| Excludes databases from the query monitor. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMExcludedDatabases DB1,DB2,DB3
|
-QMExcludedSql
| Excludes SQL statements from the query monitor. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QMExcludedSql "Select 1","Select * from Table where ID=5"
|
Disable replication collection
Disable replication statistics collection with the following PowerShell command:
Cmdlet Name | Description | Example |
---|
-ReplicationStatisticsDataCollection
| Disables replication statistics collection. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -ReplicationStatisticsDataCollection 0
- Disables replication statistics collection for server named ServerA\Instance1.
|
Customize table statistics collection
Customize table statistics collection with the following PowerShell commands:
Cmdlet Name | Description | Example |
---|
-QTStartTime
| Specifies the time of day for quiet time collection to occur. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -QTStartTime 02:30:00 -QTDays Monday,Tuesday -QTReorgMinTableSizeK 200 -QTExcludedDatabases Test |
-QTDays
| Specifies the days of the week to perform quiet time collection. |
-QTReorgMinTableSizeK
| Specifies the quiet time minimum table size (in kilobytes) to collect reorganization statistics. |
-QTExcludedDatabases
| 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 |
---|
-MMNever
| Sets maintenance mode to Never. Note that SQL Diagnostic Manager will collect data according to its normal scheduled collection interval. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -MMNever
|
-MMAlways
| Sets maintenance mode to Until further notice. Schedule collection will not occur. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -MMAlways
|
-MMRecurring
| Sets maintenance mode to Recurring every week at the specified time. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -MMRecurring -MModeDays Monday,Wednesday,Friday -MModeStartTime 01:35:00 -MModeDuration 01:00
|
-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. |
-MMOnce
| Sets maintenance mode to Occurring once at the specified time. | Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1 ) -MMOnce -MModeStartDate "2013-03-25 15:00" -MModeEndDate "2013-03-25 15:30"
|
-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 |
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >