SQL Diagnostic Manager allows you to customize your monitored SQL Server properties within PowerShell.

With PowerShell you can:

To use SQL Diagnostic Manager snap-in, 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 NameDescriptionExamples
-FriendlyServerName

Sets friendly name to an instance.

SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -FriendlyServerName stringName
-FriendlyServerNameBlank
Clears the friendly name previously assigned to an instance.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -FriendlyServerNameBlank
-InputBufferLimiter
Defines an Input Buffer Limiter value.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -InputBufferLimiter 100
-InputBufferLimiterEnable
Enables the Input Buffer Limiter.
SetSQLdmMonitoredInstance Path (EscapeSQLdmName -Name ServeA\Instance1) -InputBufferLimiterEnable
-InputBufferLimiterDisable
Disables the Input Buffer Limiter.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -InputBufferLimiterDisable
-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

-DatabaseStatisticsIntervalMinutesSpecifies 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

    • Enables trust server certificates.Note that when specified with the EncryptConnection parameter, SQL Server will not validate the certificates sent from the client but the data will still be encrypted.

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 NameDescriptionExamples
-QMEnableTraceMonitoring

Enables "Collect Query Data using SQL Trace".

SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -QMEnableTraceMonitoring
-QMEnableExtendedEvents
Enables "Collect Query Data using Extended Events".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -QMEnableExtendedEvents
-QMEnableCollectQueryPlan
Enables "Collect actual Query Plans".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -QMEnableCollectQueryPlan
-QMDisableCollectQueryPlan
Disables "Collect actual Query Plans".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -QMDisableCollectQueryPlan
-QMEnableCollectEstimatedQueryPlan
Enables “Collect Estimated Query Plans”.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -QMEnableCollectEstimatedQueryPlan
-QMDisableCollectEstimatedQueryPlan

Disables “Collect Estimated Query Plans”.

SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name ServeA\Instance1) -QMDisableCollectEstimatedQueryPlan

-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

Edit query waits settings

Customize the query waits settings of your monitor SQL Server with the following PowerShell commands:

Cmdlet NameDescriptionExamples
-QWExtendedEnable
Enables "Use Extended Events (SQL Server 2012 +)".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name INSTANCE_NAME) -QWExtendedEnable
-QWExtendedDisable
Disables "Use Extended Events (SQL Server 2012 +)".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name INSTANCE_NAME) -QWExtendedDisable
-QWStatisticsDisable
Disables "Collect query wait statistics (SQL 2005+ only)".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name INSTANCE_NAME) -QWStatisticsDisable
-QWStatisticsDuration
Enables "Collect query wait statistics at a specified time and duration".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name INSTANCE_NAME) -QWStatisticsStartDate"yyyy-mm-dd-hh:mm" -QWStatisticsDuration mm:ss
-QWStatisticsCollectIndefinite
Enables "Collect query wait statistics with Collect indefinitely option".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name INSTANCE_NAME) -QWStatisticsCollectIndefinite

Edit activity monitor settings

Customize the activity monitor settings of your monitor SQL Server with the following PowerShell commands:

Cmdlet NameDescriptionExamples
-NQAEnableSQLTrace
Enables “Use SQL trace”.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableSQLTrace
-NQAEnableExtendedEvent
Enables “Use extended event (SQL 2012+)”.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableExtendedEvent
-NQADisableAutoGrow
Disables “Capture Autogrow”.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQADisableAutoGrow
-NQAEnableAutoGrow
Enables “Capture Autogrow”.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableAutoGrow
-NQAEnableCaptureBlocking
Enables “Capture Blocking”.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableCaptureBlocking
-NQADisableCaptureBlocking
Disable “Capture Blocking”.
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQADisableCaptureBlocking
-NQACaptureBlocking
Sets “blocking process threshold".
SetSQLdmMonitoredInstance -Path (EscapeSQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQACaptureBlocking 100

Add alternate Windows users

Add alternate Windows users with the following cmdlet in different environments.

Cmdlet NameDescriptionExamples

-WindowsAlternateAuthentication

Add alternate  Windows users.

New-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name Instance1) -WindowsAlternateAuthentication Domain1\User1

  • Adds the instance named Instance1 with a valid Windows user named User1.

New-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name Instance1) -Cluster Cluster1 -WindowsAlternateAuthentication domain1\User1

  • Adds the instance named Instance1 with a valid Windows user named User1 in a DMSO environment.

Exclude applications, databases, and SQL statements within PowerShell

 Exclude applications, databases, and SQL statements with the following PowerShell commands:

Cmdlet NameDescriptionExamples

-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 NameDescriptionExample

-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 NameDescriptionExample

-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 NameDescriptionExamples

-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

-MModeDaysSpecifies the days of the week recurring maintenance mode will be effective.
-MModeStartTime Specifies the start time for recurring maintenance mode.
-MModeDurationSpecifies 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"

-MModeStartDateSpecifies the start date and time for one-time maintenance mode.
-MModeEndDateSpecifies the end date and time for one-time maintenance mode.
Example: Editing several properties at once within PowerShell

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



IDERA | Products | Purchase | Support | Community | Resources | About Us | Legal