Page History

Versions Compared

Old Version 1

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

Saved on Nov 13, 2015

compared with

New Version Current

changes.mady.by.user Alejandra Barrientos

Saved on Oct 03, 2022

Key

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

...

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

Action	Cmdlets
Associate tags to a server	addtag
Disassociate tags from a server	removetag
Schedule data collection	ScheduledCollectionIntervalMinutes -DatabaseStatisticsIntervalMinutes
Enable extended data collection	ExtendedSessionDataCollection 1
Disable extended data collection	ExtendedSessionDataCollection 0
Set SQL Authentication credentials	Credential sa
Set Windows Authentication credentials	WindowsAuthentication
Enable encrypt connection	EncryptConnection 1
Disable encrypt connection	EncryptConnection 0
Enable Trust server certificates	TrustServerCertificates 1
Disable Trust server certificates	TrustServerCertificates 0

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:

Action	Cmdlets
Enable query monitoring	QMEnabled
Disable query monitoring	QMDisabled
Capture SQL batches	QMCaptureBatches 1
Capture SQL statements	QMCaptureStatements 1
Capture stored procedure and triggers	QMCaptureProcs 1
Setup Threshold -Duration	QMQueryDuration
Setup Threshold -Logical disk reads	QMLogicalDiskReads
Setup Threshold -CPU Usage	QMCpuUsage
Setup Threshold -Physical disk writes	QMPhysicalDiskWrites

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
Add_User
Add_User
Add alternate Windows users

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

Cmdlet Name Description Examples

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

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

Anchor
Excludeapp
Excludeapp
Exclude applications, databases, and SQL

...

statements within PowerShell

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

Cmdlet Name	Description

Action

Examples

Cmdlets

-QMExcludedApps

Exclude

Excludes applications from the query monitor.

Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ServeA\Instance1) -QMExcludedApps

Exclude databases

QMExcludedDatabases

Exclude SQL text

`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"`

QMExcludedSql

Anchor
Disablerep
Disablerep
Disable replication collection

Disable replication statistics collection with the following PowerShell command:

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.

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:

Maintenance mode optionsCmdletsNever

MMNever

Until further notice

MMAlways

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.

MModeStartTime -MModeDuration

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

MMOnce -MModeStartDate -MModeEndDate

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

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

Scroll pdf ignore

Newtabfooter
alias IDERA
url http://www.idera.com
|
Newtabfooter
alias Products
url https://www.idera.com/productssolutions/sqlserver
|

...

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

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 1

New Version Current

Key

Anchor
Edit general settings
Edit general settings
Edit general settings

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
Add_User
Add_User
Add alternate Windows users

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

Newtabfooter
alias IDERA
url http://www.idera.com
|
Newtabfooter
alias Products
url https://www.idera.com/productssolutions/sqlserver
|

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

Content

Space Tools

Page History

Versions Compared

Old Version 1

New Version Current

Key

AnchorEdit general settingsEdit general settings Edit general settings

AnchorEdit query monitor settingsEdit query monitor settings Edit query monitor settings

AnchorEdit query waits settingsEdit query waits settingsEdit query waits settings

AnchorEdit activity monitor settingsEdit activity monitor settingsEdit activity monitor settings

AnchorAdd_UserAdd_UserAdd alternate Windows users

AnchorExcludeappExcludeappExclude applications, databases, and SQL

statements within PowerShell

AnchorDisablerepDisablerep Disable replication collection

AnchorCustomizetableCustomizetable Customize table statistics collection

AnchorCustomizemaintenanceCustomizemaintenance Customize maintenance mode settings

NewtabfooteraliasIDERAurlhttp://www.idera.com | NewtabfooteraliasProductsurlhttps://www.idera.com/productssolutions/sqlserver|

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

Anchor
Edit general settings
Edit general settings
Edit general settings

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
Add_User
Add_User
Add alternate Windows users

Anchor
Excludeapp
Excludeapp
Exclude applications, databases, and SQL

Anchor
Disablerep
Disablerep
Disable replication collection

Anchor
Customizetable
Customizetable
Customize table statistics collection

Anchor
Customizemaintenance
Customizemaintenance
Customize maintenance mode settings

Newtabfooter
alias IDERA
url http://www.idera.com
|
Newtabfooter
alias Products
url https://www.idera.com/productssolutions/sqlserver
|