Home
  •  

AKN631089

Monitor_AKN631089 (UnitMonitor)

Turning on trace flag 834 can cause instability of SQL Server that uses columnstore indexes

Knowledge Base article:

External

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

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
Turning on trace flag 834 can cause instability of SQL Server that uses columnstore indexes
<Details>
<Content>In this instance of SQL server, System Center Advisor detected the presence of one or more columnstore indexes and trace flag 834. This can cause SQL Server instability. We do not recommend enabling this trace flag when you are using columnstore indexes. Review the KB article and take corrective actions.</Content>
<CollectedInformation>
<Info>
<Name>SQL instance has at least one column store index example(database,table)</Name>
<Value>{0}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AKN631089" 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="MonitorMessage82256a8fb4984beebb532be9880182fc">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN631089.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 "StrColumnStoreIndexesXMLOutput" ""

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

# MAIN CODE SECTION

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

$scriptenv = New-Object psobject

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

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

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

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





#Private method to create affected databases entity object

function NewColumnStoreIndexes($strDatabaseName,$strTableName)

{

	$scriptCollectedInfo = New-Object psobject

	$scriptCollectedInfo | Add-Member NoteProperty "DatabaseNameCI" $strDatabaseName

	$scriptCollectedInfo | Add-Member NoteProperty "TableNameCI" $strTableName

	

	return $scriptCollectedInfo

}



#Private method to format XML data for UI display

function FormatXMLData([array]$arrayList)

{

	$xmlTemplate = "&lt;row&gt;&lt;DatabaseName&gt;{0}&lt;/DatabaseName&gt;&lt;TableName&gt;{1}&lt;/TableName&gt;&lt;/row&gt;"



	if($arrayList)

	{

		$arrayList | ForEach-Object{

			$scriptenv.ColumnStoreIndexesXMLEnv += ($xmlTemplate -f $_.DatabaseNameCI,$_.TableNameCI)

		}

	}

}



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 IsColumnStoreIndexPresent()

{	

	$DBConnectionString = GetDBConnectionString

	if($DBConnectionString)

	{

		#region sql statement

		

		$sql = "DECLARE @traceTb TABLE 

  ( 

     traceflag INT, 

     [status]  INT, 

     [global]  INT, 

     [session] INT 

  ) 

DECLARE @result TABLE 

  ( 

     objectname NVARCHAR(200), 

     dbname     NVARCHAR(200) 

  ) 



INSERT INTO @traceTb 

EXEC('DBCC tracestatus(-1)') 



IF EXISTS(SELECT a.traceflag 

          FROM   @traceTb AS a 

          WHERE  a.traceflag = 834 

                 AND a.status = 1 

                 AND a.global = 1) 

  BEGIN 

      DECLARE @index INT 

      DECLARE @total INT 

      DECLARE @dbName NVARCHAR(200) 

      DECLARE @sql NVARCHAR(max) 

      DECLARE @tempDb TABLE 

        ( 

           id     INT, 

           dbname NVARCHAR(200) 

        ) 



      SET @index = 1 



      SELECT @total = Count(1) 

      FROM   sys.databases 

      WHERE  state_desc = 'ONLINE' 

             AND user_access_desc = 'MULTI_USER' 

             AND database_id NOT IN 

                 (SELECT drs.database_id 

                  FROM   sys.dm_hadr_database_replica_states drs 

                         JOIN sys.dm_hadr_availability_replica_states ars 

                           ON ars.replica_id = drs.replica_id 

                              AND ars.group_id = drs.group_id 

                         JOIN sys.availability_replicas ar 

                           ON drs.group_id = ar.group_id 

                              AND drs.replica_id = ar.replica_id 

                  WHERE  ars.role_desc = 'SECONDARY' 

                         AND 

                 ar.secondary_role_allow_connections_desc &lt;&gt; 'ALL' 

                     ) 

             AND database_id &gt; 4 



      INSERT INTO @tempDb 

      SELECT Row_number() 

               OVER( 

                 ORDER BY name) AS id, 

             name 

      FROM   sys.databases 

      WHERE  state_desc = 'ONLINE' 

             AND user_access_desc = 'MULTI_USER' 

             AND database_id NOT IN 

                 (SELECT drs.database_id 

                  FROM   sys.dm_hadr_database_replica_states drs 

                         JOIN sys.dm_hadr_availability_replica_states ars 

                           ON ars.replica_id = drs.replica_id 

                              AND ars.group_id = drs.group_id 

                         JOIN sys.availability_replicas ar 

                           ON drs.group_id = ar.group_id 

                              AND drs.replica_id = ar.replica_id 

                  WHERE  ars.role_desc = 'SECONDARY' 

                         AND 

                 ar.secondary_role_allow_connections_desc &lt;&gt; 'ALL' 

                     ) 

             AND database_id &gt; 4 



      WHILE( @index &lt;= @total ) 

        BEGIN 

            SELECT @dbName = a.dbname 

            FROM   @tempDb AS a 

            WHERE  id = @index 



            SET @dbName = '[' + @dbName + ']' 

            SET @sql = + 'USE ' + @dbName 

                       + 

'SELECT TOP 1 OBJECT_NAME(object_id),DB_NAME() OBJECT_NAME FROM sys.indexes WHERE type_desc=''NONCLUSTERED COLUMNSTORE'''

 

    BEGIN try 

        INSERT INTO @result 

        EXEC(@sql) 

    END try 



    BEGIN catch 

        BREAK 

    END catch 



    IF EXISTS(SELECT dbname 

              FROM   @result) 

      BREAK 



    SET @index = @index + 1 

END 

END 



SELECT a.dbname, 

       a.objectname 

FROM   @result AS a"

		

		#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()

		$rowID = 0

		

		$ColumnStoreIndexArray = @()

		

		while($reader.Read())

		{ 						

			$ColumnStoreIndexArray += (NewColumnStoreIndexes $reader["dbname"] $reader["objectname"])			

			$rowID++									

		}

		

		if($rowID -ne 0)

		{

			FormatXMLData $ColumnStoreIndexArray

			$scriptenv.IsColumnStoreIndexPresentEnv = $true

		}



		# 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.StrColumnStoreIndexesXMLOutput = ""

	

	# Set parameter values	



	IsColumnStoreIndexPresent

	if($scriptenv.IsColumnStoreIndexPresentEnv -eq $true)

	{

		if($scriptenv.RuntimeError -eq $false)

		{

			$scriptoutput.HasIssue = $true

			$scriptoutput.StrColumnStoreIndexesXMLOutput = $scriptenv.ColumnStoreIndexesXMLEnv

		}

	}

}



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

{

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

}



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