Versions Compared

Old Version 4

changes.mady.by.user Unknown User (6092b4ded35380006886a5ee)

Saved on

compared with

New Version 5

changes.mady.by.user Unknown User (6092b4ded35380006886a5ee)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Note

To use SQL Diagnostic Manager snapin, you have to enable the provider and register the SQLdm drive.

Anchor
Edit general settings
Edit general settings
Edit general settings

Edit the general settings of your monitored SQL Server with the following PowerShell commands:

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

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

-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

Anchor
Excludeapp
Excludeapp
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"

Anchor
Disablerep
Disablerep
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.

Anchor
Customizetable
Customizetable
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.

Anchor
Customizemaintenance
Customizemaintenance
Customize maintenance mode settings

Customize maintenance mode settings with the following PowerShell commands:

...