Page History
...
- Edit general settings
- Edit query monitor settings
- Exclude applications, databases, and SQL text statements within PowerShell
- Disable replication collection settings
...
Edit the general settings of your monitored SQL Server with the following PowerShell commands:
Cmdlet Name | DescriptionAction | Examples | Cmdlets||
---|---|---|---|---|
-addtag | Associate Associates tags to a server. Note that any tags that are specified have to already exist. |
| ||
| Disassociates Disassociate tags from a server. Note that any tags that are specified have to already exist. |
| ||
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 | |||
| ||||
| 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. |
| Disable Trust server certificates | TrustServerCertificates 0
AnchorEdit query monitor settings Edit query monitor settings
Edit query monitor settings
Edit query monitor settings | |
Edit query monitor settings |
Customize the query monitor settings of your monitor SQL Server with the following PowerShell commands:
Cmdlet Name | Description | Examples | ||
---|---|---|---|---|
| Enables | |||
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 | |||
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. |
| Setup Threshold -Physical disk writes | QMPhysicalDiskWrites
Anchor Excludeapp Excludeapp
Exclude applications, databases and SQL
Excludeapp | |
Excludeapp |
...
statements within PowerShell
Exclude applications, databases, and SQL text 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. |
|
Action | Cmdlets | |
Exclude applications | QMExcludedApps | |
Exclude databases | QMExcludedDatabases | |
Exclude SQL text | QMExcludedSql
AnchorDisablerep Disablerep
Disable replication collection
Disablerep | |
Disablerep |
Disable replication statistics collection with the following PowerShell command:
Cmdlet Name | Description | Example |
---|---|---|
|
...
Disables replication statistics collection. |
|
AnchorCustomizetable Customizetable
Customize table statistics collection
Customizetable | |
Customizetable |
Customize table statistics collection with the following PowerShell commands:
Cmdlet Name | Description | Example |
---|---|---|
| Specifies the time of day for quiet time collection to occur. |
QTStartTime -QTDays -QTReorgMinTableSizeK -QTExcludedDatabases
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. |
AnchorCustomizemaintenance Customizemaintenance
Customize maintenance mode settings
Customizemaintenance | |
Customizemaintenance |
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. |
|
Maintenance mode options | Cmdlets | |
Never | MMNever | |
Until further notice | MMAlways | |
| Sets maintenance mode to | |
Recurring every week at the specified time. | MModeStartTime -MModeDuration |
|
-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. MMOnce -MModeStartDate -MModeEndDate |
|
-MModeStartDate | Specifies the start date and time for one time maintenance mode. | |
-MModeEndDate | Specifies the end date and time for one time maintenance mode. |
Panel | ||||
---|---|---|---|---|
| ||||
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:
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 |
...
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
...