Home
  •  

AKN418746

Monitor_AKN418746 (UnitMonitor)

SQL Server has multiple active traces which can impact performance

Knowledge Base article:

External

http://go.microsoft.com/fwlink/?LinkId=262802

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.DBEngine
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server has multiple active traces which can impact performance
<Details>
<Content>Advisor detected the presence of one or more profiler traces that are currently active and capturing information. If the traces are saved to a UNC location, it can result in performance problems for applications that are executing queries. Also if you are tracing events that are expensive to generate, you might encounter similar performance issue. Review the list of traces provided below and take corrective actions. Refer to the KB article for more details.

Note: If the “TraceFileName” value below is NULL, this implies client-side tracing. This might negatively impact performance. See the KB article for more details.</Content>
<CollectedInformation>
<Info>
<Name>Number Of Active Traces</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>Location Of Non Default Traces </Name>
<Value>{1}</Value>
</Info>
<Info>
<Name>Number Of Traces To UNC </Name>
<Value>{2}</Value>
</Info>
<Info>
<Name>Location Of UNC</Name>
<Value>{3}</Value>
</Info>
<Info>
<Name>Number Of Expensive Events</Name>
<Value>{4}</Value>
</Info>
<Info>
<Name>Location Of Expensive Traces Events</Name>
<Value>{5}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AKN418746" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessage67c553a54a484d7499aa3269116e130f">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='Num_Traces']$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='Trace_Locations']$</AlertParameter2>

      <AlertParameter3>$Data/Context/Property[@Name='Num_Traces_UNC']$</AlertParameter3>

      <AlertParameter4>$Data/Context/Property[@Name='Trace_Locations_UNC']$</AlertParameter4>

      <AlertParameter5>$Data/Context/Property[@Name='Num_Expensive_Trace_Events']$</AlertParameter5>

      <AlertParameter6>$Data/Context/Property[@Name='Expensive_Trace_Path']$</AlertParameter6>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN418746.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

        <Value>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "ConnectionString" $ConnectionString



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

$scriptoutput | add-member NoteProperty "Num_Traces" 0

$scriptoutput | add-member NoteProperty "Trace_Locations" ""

$scriptoutput | add-member NoteProperty "Num_Traces_UNC" 0

$scriptoutput | add-member NoteProperty "Trace_Locations_UNC" ""

$scriptoutput | add-member NoteProperty "Num_Expensive_Trace_Events" 0

$scriptoutput | add-member NoteProperty "Expensive_Trace_Path" ""

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment



$scriptenv = New-Object psobject

$scriptenv | Add-Member NoteProperty "ClientSide_Trace_Check" $false

$scriptenv | Add-Member NoteProperty "Num_Traces" 0

$scriptenv | Add-Member NoteProperty "Trace_Locations" $null

$scriptenv | Add-Member NoteProperty "Num_Traces_UNC" 0

$scriptenv | Add-Member NoteProperty "Trace_Locations_UNC" $null



$scriptenv | Add-Member NoteProperty "Num_Expensive_Trace_Events" 0

$scriptenv | Add-Member NoteProperty "Expensive_Trace_Path" $null



$scriptenv | Add-Member NoteProperty "Expensive_Trace_PresentCheck" $false

$scriptenv | Add-Member NoteProperty "RuntimeError" $false



# Helper functions



function GetActiveTraces()

{

	#Get active traces

	$num_Traces 		 = 0

	$trace_Locations 	 = ""

	$num_Traces_UNC 	 = 0

	$trace_Locations_UNC = ""

   	$queryString = "SELECT traceid, property, CONVERT (varchar(1024), value) AS value FROM sys.traces b CROSS APPLY :: fn_trace_getinfo(b.id) AS a WHERE a.traceid &gt; 1 AND b.status&lt;&gt; 0 AND a.property = 2"

	

	$oConnection = New-Object System.Data.SqlClient.SqlConnection 

	$oConnection.ConnectionString = "Server=" + $scriptargs.ConnectionString + ";" + "Database=master;Integrated Security=true" 

	$oConnection.Open()

	

	$oSQLCommand = New-Object System.Data.SqlClient.SqlCommand

	$oSQLCommand.CommandText = $queryString

	$oSQLCommand.Connection = $oConnection 

	$oReader = $oSQLCommand.ExecuteReader()

	

	while ($oReader.Read())

	{

		$num_Traces = $num_Traces + 1

		if($oReader["value"] -eq [System.DBNull]::Value)

		{

			if($scriptenv.ClientSide_Trace_Check -eq $false)

			{

				$scriptenv.ClientSide_Trace_Check = $true	

			}

		}

		else

		{

			if($oReader["value"].SubString(0,2) -eq "\\")

			{

				#UNC Location List

				$trace_Locations_UNC += [string]::Format("&lt;row&gt;&lt;TraceId&gt;{0}&lt;/TraceId&gt;&lt;TraceFileName&gt;{1}&lt;/TraceFileName&gt;&lt;/row&gt;",$oReader["traceid"],$oReader["value"])

				$num_Traces_UNC = $num_Traces_UNC + 1

			}

			else

			{

				#None UNC Locations

				$trace_Locations += [string]::Format("&lt;row&gt;&lt;TraceId&gt;{0}&lt;/TraceId&gt;&lt;TraceFileName&gt;{1}&lt;/TraceFileName&gt;&lt;/row&gt;",$oReader["traceid"],$oReader["value"])

			}

		}

	}

	

	$oReader.Close()

	

	#Check for expensive events

	$countExpensiveEvents = 0

	$expensive_Trace_Path = ""

	$expensive_Trace_PresentCheck = $false

	

	$queryString2 = @"

	  select DISTINCT trace_id,

      status,

      path,

      trace_event_id, 

      trace_event_name, 

      expensive_event 

      from 

      (SELECT t.id AS trace_id, 

      row_number() over (partition by t.id order by te.trace_event_id, tc.trace_column_id) as row_number, 

      t.status, 

      t.path, 

      t.is_default,

      te.trace_event_id, 

      te.name AS trace_event_name, 

      case when te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) then cast(1 as bit) else cast(0 as bit) end as expensive_event

      FROM sys.traces t 

      CROSS apply ::fn_trace_geteventinfo(t .id) AS e 

      JOIN sys.trace_events te ON te.trace_event_id = e.eventid 

      JOIN sys.trace_columns tc ON e.columnid = trace_column_id) as x

      where trace_id&gt;1 and expensive_event=1 and Status &lt;&gt;0

"@

	

	$oSQLCommand2 = New-Object System.Data.SqlClient.SqlCommand

	$oSQLCommand2.CommandText = $queryString2

	$oSQLCommand2.Connection = $oConnection 

	$oReader2 = $oSQLCommand2.ExecuteReader()

	

	$isUniquePath = @()

	while ($oReader2.Read())

	{

		if(!($isUniquePath -contains $oReader2["trace_id"]))

		{

			if($oReader2["path"] -eq [System.DBNull]::Value)

			{

				$expensive_Trace_Path += [string]::Format("&lt;row&gt;&lt;TraceId&gt;{0}&lt;/TraceId&gt;&lt;TraceFileName&gt;{1}&lt;/TraceFileName&gt;&lt;/row&gt;",$oReader2["trace_id"],"NULL")

			}

			else

			{

				$expensive_Trace_Path += [string]::Format("&lt;row&gt;&lt;TraceId&gt;{0}&lt;/TraceId&gt;&lt;TraceFileName&gt;{1}&lt;/TraceFileName&gt;&lt;/row&gt;",$oReader2["trace_id"],$oReader2["path"])

			}

			$isUniquePath += $oReader2["trace_id"]

		}

		$countExpensiveEvents = $countExpensiveEvents + 1

	}

	if($countExpensiveEvents -gt 0)

	{

		$expensive_Trace_PresentCheck = $true

	}

	

	$oReader2.Close()

	$oConnection.Close()

	

	$scriptenv.Num_Traces  			= $num_Traces

	$scriptenv.Trace_Locations      = $trace_Locations

	$scriptenv.Num_Traces_UNC 		= $num_Traces_UNC

	$scriptenv.Trace_Locations_UNC 	= $trace_Locations_UNC

	

	$scriptenv.Expensive_Trace_Path = $expensive_Trace_Path

	$scriptenv.Num_Expensive_Trace_Events = $countExpensiveEvents

	

	$scriptenv.Expensive_Trace_PresentCheck = $expensive_Trace_PresentCheck

}



# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{

	trap [System.Exception] {

		$scriptenv.RuntimeError = $true

      	continue; 

   	}

	# Initialize parameters

	$scriptoutput.HasIssue = $false



	# Set parameter values

	

	GetActiveTraces

	

	if($scriptenv.RuntimeError -eq $false)

	{

		if(($scriptenv.Expensive_Trace_PresentCheck -eq $true) -or ($scriptenv.Num_Traces_UNC -gt 0) -or ($scriptenv.Num_Traces -gt 3) -or ($scriptenv.ClientSide_Trace_Check -eq $true))

		{

			$scriptoutput.HasIssue = $true

			

			$scriptoutput.Num_Traces 			= $scriptenv.Num_Traces

			$scriptoutput.Trace_Locations 		= $scriptenv.Trace_Locations;

			$scriptoutput.Num_Traces_UNC 		= $scriptenv.Num_Traces_UNC

			$scriptoutput.Trace_Locations_UNC 	= $scriptenv.Trace_Locations_UNC

			

			$scriptoutput.Num_Expensive_Trace_Events = $scriptenv.Num_Expensive_Trace_Events

			$scriptoutput.Expensive_Trace_Path 	     = $scriptenv.Expensive_Trace_Path

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



if ($scriptoutput.Num_Traces -ne $null)

{

	$bag.AddValue("Num_Traces", $scriptoutput.Num_Traces)

}



if ($scriptoutput.Trace_Locations -ne $null)

{

	$bag.AddValue("Trace_Locations", $scriptoutput.Trace_Locations)

}



if ($scriptoutput.Num_Traces_UNC -ne $null)

{

	$bag.AddValue("Num_Traces_UNC", $scriptoutput.Num_Traces_UNC)

}



if ($scriptoutput.Trace_Locations_UNC -ne $null)

{

	$bag.AddValue("Trace_Locations_UNC", $scriptoutput.Trace_Locations_UNC)

}



if ($scriptoutput.Num_Expensive_Trace_Events -ne $null)

{

	$bag.AddValue("Num_Expensive_Trace_Events", $scriptoutput.Num_Expensive_Trace_Events)

}



if ($scriptoutput.Expensive_Trace_Path -ne $null)

{

	$bag.AddValue("Expensive_Trace_Path", $scriptoutput.Expensive_Trace_Path)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>