MDW Appliance v2 SQL Table Index Fragmentation Data Source

Microsoft.SQLServerAppliance.MDW2.TableIndexFragmentationProvider (DataSourceModuleType)

MDW Appliance v2 SQL Table Index Fragmentation Data Source.

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServerAppliance.MDW2.TableIndexFragmentationProvider" Accessibility="Internal">

  <Configuration>

    <xsd:element name="IntervalSeconds" type="xsd:integer"/>

    <xsd:element name="SyncTime" type="xsd:string"/>

    <xsd:element name="TimeoutSeconds" type="xsd:int"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="IntervalSeconds" ParameterType="int" Selector="$Config/IntervalSeconds$"/>

    <OverrideableParameter ID="SyncTime" ParameterType="string" Selector="$Config/SyncTime$"/>

    <OverrideableParameter ID="TimeoutSeconds" ParameterType="int" Selector="$Config/TimeoutSeconds$"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="Scheduler" TypeID="System!System.Scheduler">

          <Scheduler>

            <SimpleReccuringSchedule>

              <Interval>$Config/IntervalSeconds$</Interval>

              <SyncTime>$Config/SyncTime$</SyncTime>

            </SimpleReccuringSchedule>

            <ExcludeDates/>

          </Scheduler>

        </DataSource>

        <ProbeAction ID="ProbeAction" TypeID="Microsoft.SQLServerAppliance.MDW2.ScriptPropertyBagProbeAction">

          <ApplicationName/>

          <WorkingDirectory/>

          <CommandLine>powershell.exe -NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt 'RemoteSigned') {set-executionpolicy remotesigned} &amp; '$$file/SQLServerTableIndexFragmentationDS.ps1$$' '$Target/Host/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$' '$Target/Host/Host/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$'</CommandLine>

          <SecureInput/>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

          <RequireOutput>true</RequireOutput>

          <Files>

            <File>

              <Name>SQLServerTableIndexFragmentationDS.ps1</Name>

              <Contents><Script>                    

                      # Constants



$ErrorActionPreference = "Stop"

$GenericScriptErrorEventID = 4200

$DebugEventID = 4201



$EventType = @{ 

	"Info" = 0; 

	"Error" = 1;

	"Warning" = 2

}



# Functions



function logDiscoveryError($reason) {

	$description = "Discovery error on computer {0}.{1}Reason: {1}$reason" -f  $env:COMPUTERNAME, [Environment]::NewLine													

	$api.LogScriptEvent($LogEventHeader, $GenericScriptErrorEventID, $EventType.Error, $description)

}



function logMonitoringError($reason) {

	$description = "Monitoring error on computer {0}.{1}Reason: {1}$reason" -f  $env:COMPUTERNAME, [Environment]::NewLine													

	$api.LogScriptEvent($LogEventHeader, $GenericScriptErrorEventID, $EventType.Error, $description)

}



function logDebugEvent($message) {

	$api.LogScriptEvent($LogEventHeader, $DebugEventID, $EventType.Info, $message)

}



function executeSQLQuery {

	param(`

		[string]$query,

		[string]$serverName = ".",

		[string]$instance = "MSSQLSERVER",

		[hashtable]$parameters = $null,		

		[string]$database = "master"`

	)

	

	if ($instanceName -eq "MSSQLSERVER") {

		$dataSource = $serverName

	} else {

		$dataSource = "$serverName\$instance"

	}



	$connectionString = "Data Source=$dataSource; Initial Catalog=$database;Integrated Security=SSPI"	

	$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

		

	$connection.Open()

	$command = $connection.CreateCommand()

	$command.CommandText = $query



	if ($parameters -ne $null) {

		foreach ($key in $parameters.Keys) {

			$command.Parameters.AddWithValue($key, $parameters.$key) &gt; $null

		}

	}

	

	$reader = $command.ExecuteReader()



	$results = @()	

	while ($reader.Read()) {				

		$item = @{} 

		for ($i = 0; $i -lt $reader.FieldCount; $i++) {			

			$item.Add($reader.GetName($i), $reader.GetValue($i))

		}		

		

		$results +=  $item

	}



	# IV: Comma is used to force powershell to return an empty array

	return ,$results

}

                      $computerName = $args[0]

$instanceName = $args[1]



# Constants



$LogEventHeader = "MDW Appliance Monitoring"

$TableFragmentationQuery = @"

	DECLARE @GetTables nvarchar(MAX)

	SELECT 

		@GetTables = COALESCE(@GetTables + char(13) + char(10) + ' UNION ALL ','') + '

		SELECT	

			tables.name COLLATE SQL_Latin1_General_CP1_CI_AS AS tableName, 

			databases.name COLLATE SQL_Latin1_General_CP1_CI_AS AS databaseName,

			schemas.name COLLATE SQL_Latin1_General_CP1_CI_AS AS schemaName,

			avg_fragment_size_in_pages AS value

		FROM

			sys.dm_db_index_physical_stats(DB_ID(' + QUOTENAME(name, '''') + '), NULL, NULL, NULL, ''SAMPLED'') AS IndexStats

			INNER JOIN ' + QUOTENAME(name) + '.sys.tables ON

				tables.object_id = IndexStats.object_id

			INNER JOIN ' + QUOTENAME(name) + '.sys.schemas ON

				schemas.schema_id = tables.schema_id

			INNER JOIN sys.databases ON

				databases.database_id = IndexStats.database_id

		WHERE

			IndexStats.index_type_desc = ''CLUSTERED INDEX'' 

			AND 

			IndexStats.index_level = 0

			AND

			IndexStats.avg_fragment_size_in_pages IS NOT NULL'	

	FROM

		sys.databases

	WHERE

		state = 0

		AND

		name NOT IN ('master', 'tempdb', 'model', 'msdb')



	EXEC (@GetTables)

"@



# Main



try {

	$api = New-Object -comObject "MOM.ScriptAPI"	



	$stats = executeSQLQuery $TableFragmentationQuery $computerName $instanceName

	foreach ($fragmentation in $stats) {

		$bag = $api.CreateTypedPropertyBag(2)

		$bag.AddValue("DatabaseName", $fragmentation.databaseName)

		$bag.AddValue("TableName", $fragmentation.schemaName + ":" + $fragmentation.tableName)

		$bag.AddValue("Value", $fragmentation.value)		



		$api.AddItem($bag)

	}

}

catch {	

	logMonitoringError $_.Exception.Message	

}



$api.ReturnItems()



                    </Script></Contents>

              <Unicode>true</Unicode>

            </File>

          </Files>

          <OutputType>System.PropertyBagData</OutputType>

        </ProbeAction>

        <ConditionDetection ID="DataFilter" TypeID="System!System.ExpressionFilter">

          <Expression>

            <And>

              <Expression>

                <SimpleExpression>

                  <ValueExpression>

                    <XPathQuery Type="String">Property[@Name='DatabaseName']</XPathQuery>

                  </ValueExpression>

                  <Operator>Equal</Operator>

                  <ValueExpression>

                    <Value Type="String">$Target/Host/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$</Value>

                  </ValueExpression>

                </SimpleExpression>

              </Expression>

              <Expression>

                <SimpleExpression>

                  <ValueExpression>

                    <XPathQuery Type="String">Property[@Name='TableName']</XPathQuery>

                  </ValueExpression>

                  <Operator>Equal</Operator>

                  <ValueExpression>

                    <Value Type="String">$Target/Property[Type="Discovery!Microsoft.SQLServerAppliance.MDW2.Table"]/Schema$:$Target/Property[Type="Discovery!Microsoft.SQLServerAppliance.MDW2.Table"]/TableName$</Value>

                  </ValueExpression>

                </SimpleExpression>

              </Expression>

            </And>

          </Expression>

        </ConditionDetection>

      </MemberModules>

      <Composition>

        <Node ID="DataFilter">

          <Node ID="ProbeAction">

            <Node ID="Scheduler"/>

          </Node>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.PropertyBagData</OutputType>

</DataSourceModuleType>