...
Anchor |
---|
| Edit general settings |
---|
| Edit general settings |
---|
|
Edit general settingsEdit 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 Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName stringName |
-FriendlyServerNameBlank | Clears the friendly name previously assigned to an instance. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -FriendlyServerNameBlank |
-InputBufferLimiter | Defines an Input Buffer Limiter value. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName 100 |
-InputBufferLimiterEnable | Enables the Input Buffer Limiter. |
SetSQLdmMonitoredInstance Path (EscapeSQLdmName Set-SQLdmMonitoredInstancePath (Escape-SQLdmName -Name |
-InputBufferLimiterEnable |
-InputBufferLimiterDisable | Disables the Input Buffer Limiter. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -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
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 Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QMEnableTraceMonitoring |
-QMEnableExtendedEvents | Enables "Collect Query Data using Extended Events". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QMEnableExtendedEvents |
-QMEnableCollectQueryPlan | Enables "Collect actual Query Plans". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QMEnableCollectQueryPlan |
-QMDisableCollectQueryPlan | Disables "Collect actual Query Plans". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QMDisableCollectQueryPlan |
-QMEnableCollectEstimatedQueryPlan | Enables “Collect Estimated Query Plans”. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QMEnableCollectEstimatedQueryPlan |
-QMDisableCollectEstimatedQueryPlan | Disables “Collect Estimated Query Plans”. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -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 Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QWExtendedEnable |
-QWExtendedDisable | Disables "Use Extended Events (SQL Server 2012 +)". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QWExtendedDisable |
-QWStatisticsDisable | Disables "Collect query wait statistics (SQL 2005+ only)". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QWStatisticsDisable |
-QWStatisticsDuration | Enables "Collect query wait statistics at a specified time and duration". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -QWStatisticsStartDate"yyyy-mm-dd-hh:mm" |
mm:ss |
-QWStatisticsCollectIndefinite | Enables "Collect query wait statistics with Collect indefinitely option". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName -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 Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName SRISHTIPUROHIT\SQLEXPRESS) |
-NQAEnableSQLTrace |
-NQAEnableExtendedEvent | Enables “Use extended event (SQL 2012+)”. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName SRISHTIPUROHIT\SQLEXPRESS) |
-NQAEnableExtendedEvent |
-NQADisableAutoGrow | Disables “Capture Autogrow”. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName SRISHTIPUROHIT\SQLEXPRESS) |
-NQADisableAutoGrow |
-NQAEnableAutoGrow | Enables “Capture Autogrow”. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName SRISHTIPUROHIT\SQLEXPRESS) |
-NQAEnableAutoGrow |
-NQAEnableCaptureBlocking | Enables “Capture Blocking”. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName SRISHTIPUROHIT\SQLEXPRESS) |
-NQAEnableCaptureBlocking |
-NQADisableCaptureBlocking | Disable “Capture Blocking”. |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName SRISHTIPUROHIT\SQLEXPRESS) |
-NQADisableCaptureBlocking |
-NQACaptureBlocking | Sets “blocking process threshold". |
SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path |
EscapeSQLdmName SRISHTIPUROHIT\SQLEXPRESS) |
Add alternate Windows usersAdd alternate Windows users with the following cmdlet in different environments.
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.
|
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"
|
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:
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 | 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 |
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >
...