Versions Compared

Old Version 13

changes.mady.by.user Alejandra Barrientos

Saved on

compared with

New Version Current

changes.mady.by.user Alejandra Barrientos

Saved on

Key

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

...

Cmdlet NameDescriptionExamples
-FriendlyServerName

Sets friendly name to an instance.

SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -FriendlyServerName stringName
-FriendlyServerNameBlankClears the friendly name previously assigned to an instance.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -FriendlyServerNameBlank
-InputBufferLimiterDefines an Input Buffer Limiter value.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -InputBufferLimiter 100
-InputBufferLimiterEnableEnables the Input Buffer Limiter.SetSQLdmMonitoredInstance Path (EscapeSQLdmName Set-SQLdmMonitoredInstancePath (Escape-SQLdmName -Name ServeA\Instance1) -InputBufferLimiterEnable
-InputBufferLimiterDisableDisables the Input Buffer Limiter.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -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.

...

Cmdlet NameDescriptionExamples
-QMEnableTraceMonitoring

Enables "Collect Query Data using SQL Trace".

SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -QMEnableTraceMonitoring
-QMEnableExtendedEventsEnables "Collect Query Data using Extended Events".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -QMEnableExtendedEvents
-QMEnableCollectQueryPlanEnables "Collect actual Query Plans".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -QMEnableCollectQueryPlan
-QMDisableCollectQueryPlanDisables "Collect actual Query Plans".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -QMDisableCollectQueryPlan
-QMEnableCollectEstimatedQueryPlanEnables “Collect Estimated Query Plans”.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name ServeA\Instance1) -QMEnableCollectEstimatedQueryPlan
-QMDisableCollectEstimatedQueryPlan

Disables “Collect Estimated Query Plans”.

SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -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

...

Cmdlet NameDescriptionExamples
-QWExtendedEnableEnables "Use Extended Events (SQL Server 2012 +)".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name INSTANCE_NAME) -QWExtendedEnable
-QWExtendedDisableDisables "Use Extended Events (SQL Server 2012 +)".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name INSTANCE_NAME) -QWExtendedDisable
-QWStatisticsDisableDisables "Collect query wait statistics (SQL 2005+ only)".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name INSTANCE_NAME) -QWStatisticsDisable
-QWStatisticsDurationEnables "Collect query wait statistics at a specified time and duration".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name INSTANCE_NAME) -QWStatisticsStartDate"yyyy-mm-dd-hh:mm" -QWStatisticsDuration mm:ss
-QWStatisticsCollectIndefiniteEnables "Collect query wait statistics with Collect indefinitely option".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name INSTANCE_NAME) -QWStatisticsCollectIndefinite

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

...

Cmdlet NameDescriptionExamples
-NQAEnableSQLTraceEnables “Use SQL trace”.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableSQLTrace
-NQAEnableExtendedEventEnables “Use extended event (SQL 2012+)”.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableExtendedEvent
-NQADisableAutoGrowDisables “Capture Autogrow”.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQADisableAutoGrow
-NQAEnableAutoGrowEnables “Capture Autogrow”.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableAutoGrow
-NQAEnableCaptureBlockingEnables “Capture Blocking”.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQAEnableCaptureBlocking
-NQADisableCaptureBlockingDisable “Capture Blocking”.SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQADisableCaptureBlocking
-NQACaptureBlockingSets “blocking process threshold".SetSQLdmMonitoredInstance Set-SQLdmMonitoredInstance-Path (EscapeSQLdmName Escape-SQLdmName -Name SRISHTIPUROHIT\SQLEXPRESS) -NQACaptureBlocking 100

Anchor
Add_User
Add_User
Add alternate Windows users

...

Panel
borderStyledashed
titleExample: 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



Scroll pdf ignore
Newtabfooter
aliasIDERA
urlhttp://www.idera.com
| 
Newtabfooter
aliasProducts
urlhttps://www.idera.com/productssolutions/sqlserver
| 
Newtabfooter
aliasPurchase
urlhttps://www.idera.com/buynow/onlinestore
|
Newtabfooter
aliasSupport
urlhttps://idera.secure.force.com/
|
Newtabfooter
aliasCommunity
urlhttp://community.idera.com
|
Newtabfooter
aliasResources
urlhttp://www.idera.com/resourcecentral
|
Newtabfooter
aliasAbout Us
urlhttp://www.idera.com/about/aboutus
|
Newtabfooter
aliasLegal
urlhttps://www.idera.com/legal/termsofuse