Versions Compared

Old Version 6

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

Saved on

compared with

New Version 7

changes.mady.by.user Wara Hermosa

Saved on

Key

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

...

Note

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

...

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.

Anchor
Edit query monitor settings
Edit query monitor settings
Edit query monitor settings

...

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

Anchor
Edit query waits settings
Edit query waits settings
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

Anchor
Edit activity monitor settings
Edit activity monitor settings
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


Anchor
Excludeapp
Excludeapp
Exclude applications, databases and SQL statements within PowerShell

...

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

...

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.
Panel
titleExample: Editing several properties at once within PowerShell
borderStyledashed

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