Home
  •  

AKN638358

Monitor_AKN638358 (UnitMonitor)

SQL Server Memory leak when queries run against a temporary table in a nested stored procedure in SQL Server 2012

Knowledge Base article:

External

http://support.microsoft.com/kb/2803065

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.DBEngine
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityHigh
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server Memory leak when queries run against a temporary table in a nested stored procedure in SQL Server 2012
<Details>
<Content>In this instance of SQL Server, System Center Advisor detected the SQL Server build is lower than the fixed build for a Memory Leak issue. The issue can occur when you have a stored procedure that contains a child stored procedure which uses temporary tables, and then uses cursors to query against the temporary tables. You may notice MEMORYCLERK_SQLOPTIMIZER of sys.dm_os_memory_clerks and MEMOBJ_EXECCOMPILETEMP from sys.dm_os_memory_objects going very high.</Content>
<CollectedInformation>
<Info>
<Name>Current Build Number</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>Fixed Build Number</Name>
<Value>{1}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AKN638358" 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="High" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessage9033e19dbf95475bb93c80667e3bf439">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN638358.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 "StrCurrentBuildOutput" ""

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

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

# MAIN CODE SECTION

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

$scriptenv = New-Object psobject

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

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

$scriptenv | Add-Member NoteProperty "CurrentBuildNum" ""

$scriptenv | Add-Member NoteProperty "FixedBuild" "11.0.3349.0"



function GetDBConnectionString()

{

	if($scriptargs.ConnectionString)

	{

		$connStrBulider = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

        $ConnStrBulider["Data Source"] = $scriptargs.ConnectionString

        $ConnStrBulider["Database"] = "master"

        $ConnStrBulider["Trusted_Connection"] = "SSPI"

    

		$connStrBulider.ConnectionString

	}

}



function CheckSQLBuild()

{	

	$DBConnectionString = GetDBConnectionString

	if($DBConnectionString)

	{

		#region sql statement

		

		$sql = "SELECT&#xA0;SERVERPROPERTY('productversion') as BuildNum"

		

		#endregion

	

		# Create SqlConnection object and define connection string 

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

		$oConnection.ConnectionString = $DBConnectionString

		$oConnection.Open()



		# Create SqlCommand object, define command text, and set the connection

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

		$oSQLCommand.CommandText = $sql

		$oSQLCommand.Connection = $oConnection

		

		# Execute Command 

		$reader = $oSQLCommand.ExecuteReader()			

		

		while($reader.Read())

		{ 									

			if([version]($reader["BuildNum"]) -ge [version]"11.0.3000.0" -and [version]($reader["BuildNum"]) -lt [version]"11.0.3349.0")

			{				

				$scriptenv.IsIssueBuild = $true

				$scriptenv.CurrentBuildNum = $reader["BuildNum"]

			}																

		}				



		# Close reader object

		$reader.Close()



		# Close SqlConnection object

		$oConnection.Close()

	}

}





# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{ 

	# All parameters should be populated outside of the main function. 

	# The main function should only include the detection logic so that it can be easily reused by the Atlanta authoring tool.



	trap [Exception] {

		$scriptenv.RuntimeError = $true

      	continue; 

   	}

	

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.StrCurrentBuildOutput = ""

	$scriptoutput.StrFixedBuildOutput = ""

	

	# Set parameter values	



	CheckSQLBuild

	if($scriptenv.IsIssueBuild -eq $true)

	{

		if($scriptenv.RuntimeError -eq $false)

		{

			$scriptoutput.HasIssue = $true

			$scriptoutput.StrCurrentBuildOutput = $scriptenv.CurrentBuildNum

			$scriptoutput.StrFixedBuildOutput = $scriptenv.FixedBuild

		}

	}

}

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.StrCurrentBuildOutput -ne $null)

{

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

}



if ($scriptoutput.StrFixedBuildOutput -ne $null)

{

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

}



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