SQL Broker Performance

Report.SQLServer.2016.BrokerPerformance (LinkedReport)

SQL Broker Performance Report

Knowledge Base article:

Summary

How does this report work?

The SQL Broker Performance report displays one chart with these performance items:

Activation Tasks aborted Total Transaction Rollbacks SQL RECEIVEs per sec Activation Task Limit Reached Transport Message Fragment Tasks Started sec Transport receive I/Os per sec Transport Open Connection Count Statistics Enqueued Messages per sec Activation Task Limit Reached per sec Statistics Enqueued Transport Msgs per sec Transport Send I/Os per sec Transport Message Fragment Receives sec Activation Stored Procedures Invoked per sec Statistics SQL SENDs per sec

The report finds data if the objects supplied are of type

SQL DB Engine

Search for objects with contains and “SQL DB Engine”

The report displays for every selected object a separate chart. Data is aggregated to days of a month.

How to use this report?

When run from the Reporting space:

The second column in the search results shows of which type the displayed objects are. Make sure you select objects of type SQL DB Engine.

When run from the Monitoring space:

Create a state view or performance view by choosing the “SQL DB Engine” type.

What Parameters are offered?

Date/Time selection: Allows selecting a relative or fixed date and time range and time zone.

Object: Allows adding objects to run this report for

Element properties:

Base	Microsoft.SystemCenter.DataWarehouse.Report.Performance
Target	Microsoft.SQLServer.2016.DBEngine
Accessibility	Public
Visible	True

Source Code:

<LinkedReport ID="Report.SQLServer.2016.BrokerPerformance" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" Accessibility="Public" Visible="true" Base="ReportLibrary!Microsoft.SystemCenter.DataWarehouse.Report.Performance">

  <ParameterBlock xmlns="http://schemas.microsoft.com/mom/reporting/2007/ReportParameterSettings" columns="6">

    <Controls>

      <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.ComboBox" columnSpan="2">

        <ReportParameters>

          <ReportParameter name="DataAggregation">

            <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.DataAggregation</Prompt>

          </ReportParameter>

        </ReportParameters>

      </Control>

      <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.LinkedPerformanceChartObjectPicker" columnSpan="4" rowSpan="3">

        <ReportParameters>

          <ReportParameter name="ObjectList">

            <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.ObjectList</Prompt>

          </ReportParameter>

          <ReportParameter name="ManagementGroupId" binding="GroupList"/>

        </ReportParameters>

        <Properties>

          <Property name="ValueTemplate">

            <Value>

                        &lt;Data&gt;

                            &lt;Chart ObjectJoin="ChartPerObject"  TitleString="Report.SQLServer.2016.BrokerPerformance.ChartTitle1" &gt;

                                &lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Activation_Tasks_Aborted_sec__Total_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;112,33,85&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Statistics_Broker_Transaction_Rollbacks_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;34,68,155&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Statistics_SQL_RECEIVEs_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;74,60,143&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Activation_Task_Limit_Reached__Total_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;72,147,102&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Transport_Message_Fragment_Sends_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;120,116,66&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Activation_Tasks_Started_sec__Total_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;204,122,209&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Transport_Receive_I_Os_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;128,161,155&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Transport_Open_Connection_Count_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;110,80,14&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Statistics_Enqueued_Messages_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;136,172,20&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Activation_Task_Limit_Reached_sec__Total_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;24,117,14&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Statistics_Enqueued_Transport_Msgs_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;223,44,207&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Transport_Send_I_Os_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;44,111,84&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Transport_Message_Fragment_Receives_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;110,150,69&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Activation_Stored_Procedures_Invoked_sec__Total_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;142,176,142&lt;/Color&gt;

                                &lt;/Series&gt;

								&lt;Series&gt;

                                    &lt;Rule&gt;$MPElement[Name="Microsoft.SQLServer.2016.SQLServer_Broker_Statistics_SQL_SENDs_sec_15.0_minutes_5_Rule"]$&lt;/Rule&gt;

                                    &lt;Scale&gt;1&lt;/Scale&gt;

                                    &lt;Type&gt;Line&lt;/Type&gt;

                                    &lt;Color&gt;125,126,36&lt;/Color&gt;

                                &lt;/Series&gt;

								

                            &lt;/Chart&gt;

                        &lt;/Data&gt;

                        </Value>

          </Property>

          <Property name="ContextObjectBinding">

            <Value>Containment</Value>

          </Property>

          <Property name="TypeFilter">

            <Value>Microsoft.SQLServer.2016.DBEngine</Value>

          </Property>

        </Properties>

      </Control>

      <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.BusinessRelativeDateTimePicker" columnSpan="2">

        <ReportParameters>

          <ReportParameter name="TimeZone" binding="TimeZone">

            <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.TimeZone</Prompt>

          </ReportParameter>

          <ReportParameter name="TimeZoneName" binding="TimeZoneName"/>

          <ReportParameter name="StartDate_BaseType" binding="StartDate_BaseType"/>

          <ReportParameter name="StartDate_BaseValue" binding="StartDate_BaseValue">

            <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.StartDateTime</Prompt>

          </ReportParameter>

          <ReportParameter name="StartDate_OffsetType" binding="StartDate_OffsetType"/>

          <ReportParameter name="StartDate_OffsetValue" binding="StartDate_OffsetValue"/>

          <ReportParameter name="EndDate_BaseType" binding="EndDate_BaseType"/>

          <ReportParameter name="EndDate_BaseValue" binding="EndDate_BaseValue">

            <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.EndDateTime</Prompt>

          </ReportParameter>

          <ReportParameter name="EndDate_OffsetType" binding="EndDate_OffsetType"/>

          <ReportParameter name="EndDate_OffsetValue" binding="EndDate_OffsetValue"/>

          <ReportParameter name="TimeType" binding="TimeType"/>

          <ReportParameter name="TimeWeekMap" binding="TimeWeekMap"/>

        </ReportParameters>

      </Control>

      <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.BooleanPicker" columnSpan="2">

        <ReportParameters>

          <ReportParameter name="Enable3D">

            <Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.Chart3D</Prompt>

          </ReportParameter>

        </ReportParameters>

      </Control>

    </Controls>

  </ParameterBlock>

  <Parameters>

    <Parameter Name="AggregationType">

      <Value>0</Value>

    </Parameter>

  </Parameters>

</LinkedReport>