• Created by Unknown User (6092b4ded35380006886a5ee), last modified on Jan 06, 2016

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

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

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

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

 

SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
Idera WebsiteProductsPurchaseSupportCommunityAbout UsResources Legal
  • No labels