Page History

Versions Compared

Key

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

...

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

Cmdlet Name	Description	Examples
-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`
`-DatabaseStatisticsIntervalMinutes`	Specifies 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 Name	Description	Examples
-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 Name	Description	Examples
-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 Name	Description	Examples
-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

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

Cmdlet Name	Description	Examples
`-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 Name Description Example

Cmdlet Name	Description	Example
`-ReplicationStatisticsDataCollection`	Disables replication statistics collection.	`Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1) -ReplicationStatisticsDataCollection 0` Disables replication statistics collection for server named ServerA\Instance1.

-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 Name	Description	Example
`-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:

Cmdlet Name	Description	Examples
`-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`
`-MModeDays`	Specifies the days of the week recurring maintenance mode will be effective.
`-MModeStartTime`	Specifies the start time for recurring maintenance mode.
`-MModeDuration`	Specifies 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"`
`-MModeStartDate`	Specifies the start date and time for one-time maintenance mode.
`-MModeEndDate`	Specifies the end date and time for one-time maintenance mode.

Panel

borderStyle	dashed
title	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

Legal

Scroll pdf ignore

SQL

SQLDiagnostic Manager

identifies and resolves SQL Server performance problems before they happen. Learn more > >

for SQL Server performance monitoring, alerting, and diagnostics for SQL Server.

Newtabfooter
alias IDERA
url http://www.idera.com
|
Newtabfooter
alias Products
url https://www.idera.com/productssolutions/sqlserver
|
Newtabfooter
alias Purchase
url https://www.idera.com/buynow/onlinestore
|
Newtabfooter
alias Support
url https://idera.secure.force.com/
|
Newtabfooter
alias Community
url http://community.idera.com
|
Newtabfooter
alias Resources
url http://www.idera.com/resourcecentral
|
Newtabfooter
alias About Us
url http://www.idera.com/about/aboutus
|
Newtabfooter
alias Legal
url https://www.idera.com/legal/termsofuse

Content

Space Tools

Versions Compared

Old Version 9

New Version 10

Key

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

Anchor
Edit query waits settings
Edit query waits settings
Edit query waits settings

Anchor
Edit activity monitor settings
Edit activity monitor settings
Edit activity monitor settings

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

Anchor
Disablerep
Disablerep
Disable replication collection

Anchor
Customizetable
Customizetable
Customize table statistics collection

Anchor
Customizemaintenance
Customizemaintenance
Customize maintenance mode settings

SQLDiagnostic Manager

for SQL Server performance monitoring, alerting, and diagnostics for SQL Server.

Content

Space Tools

Page History

Versions Compared

Old Version 9

New Version 10

Key

AnchorEdit query monitor settingsEdit query monitor settingsEdit query monitor settings

AnchorEdit query waits settingsEdit query waits settingsEdit query waits settings

AnchorEdit activity monitor settingsEdit activity monitor settingsEdit activity monitor settings

AnchorExcludeappExcludeappExclude applications, databases and SQL statements within PowerShell

AnchorDisablerepDisablerepDisable replication collection

AnchorCustomizetableCustomizetableCustomize table statistics collection

AnchorCustomizemaintenanceCustomizemaintenanceCustomize maintenance mode settings

SQLDiagnostic Manager

for SQL Server performance monitoring, alerting, and diagnostics for SQL Server.

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

Anchor
Edit query waits settings
Edit query waits settings
Edit query waits settings

Anchor
Edit activity monitor settings
Edit activity monitor settings
Edit activity monitor settings

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

Anchor
Disablerep
Disablerep
Disable replication collection

Anchor
Customizetable
Customizetable
Customize table statistics collection

Anchor
Customizemaintenance
Customizemaintenance
Customize maintenance mode settings