SQL Diagnostic Manager allows you to customize your monitored SQL Server properties within PowerShell.
With PowerShell you can:
To use SQL Diagnostic Manager snapin, you have to enable the provider and register the SQLdm drive. |
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 |
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 with the following PowerShell commands:
Action | Cmdlets |
---|---|
Exclude applications | QMExcludedApps |
Exclude databases | QMExcludedDatabases |
Exclude SQL text | QMExcludedSql |
Disable replication statistics collection with the following PowerShell command:
ReplicationStatisticsDataCollection 0
Customize table statistics collection with the following PowerShell commands:
QTStartTime -QTDays -QTReorgMinTableSizeK -QTExcludedDatabases
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:
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 |
Idera Website | Products | Purchase | Support | Community | About Us | Resources | Legal |
---|