SQL Server Database Engine Counter Analysis Report
How does this report work?
The SQL Database Engine Counters report displays one chart with these performance items:
Number of Deadlocks Per Second
Lock Timeouts Per Second
Transactions Per Second
SQL Recompiles Per Second
SQL Compilations Per Second
Buffer Cache Hit Ratio
When choosing objects to run this report against be aware of the following
The report will only show data for classes of type "SQL DB Engine" that represent SQL 2000 installations.
The most direct way to run this report against all SQL 2005 DB Engine instances is to click "Add Group..." and to select the "SQL 2005 DB Engine Group"
You can also use the "Add Object..." button to choose particular SQL DB Engine instances that you wish to run the report against, but you will need to know the names of those instances and which ones are SQL 2005 specifically for this report to work.
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
Base | Microsoft.SystemCenter.DataWarehouse.Report.Performance |
Target | Microsoft.SQLServer.2005.DBEngine |
Accessibility | Public |
Visible | True |
<LinkedReport ID="Report.SQLServer.2005.DatabaseCounters" Target="SQL2005Core!Microsoft.SQLServer.2005.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>
<Data>
<Chart ObjectJoin="ChartPerObject">
<Series>
<Rule>$MPElement[Name="Microsoft.SQLServer.2005.NumberDeadlocksPerSecond"]$</Rule>
<Scale>1</Scale>
<Type>Line</Type>
<Color>63,63,255</Color>
</Series>
<Series>
<Rule>$MPElement[Name="Microsoft.SQLServer.2005.LockTimeoutsPerSecond"]$</Rule>
<Scale>1</Scale>
<Type>Line</Type>
<Color>0,159,0</Color>
</Series>
<Series>
<Rule>$MPElement[Name="Microsoft.SQLServer.2005.TransactionsPerSecond"]$</Rule>
<Scale>1</Scale>
<Type>Line</Type>
<Color>255,31,31</Color>
</Series>
<Series>
<Rule>$MPElement[Name="Microsoft.SQLServer.2005.SQLRecompilesPerSecond"]$</Rule>
<Scale>1</Scale>
<Type>Line</Type>
<Color>255,221,0</Color>
</Series>
<Series>
<Rule>$MPElement[Name="Microsoft.SQLServer.2005.SQLCompilationsPerSecond"]$</Rule>
<Scale>1</Scale>
<Type>Line</Type>
<Color>0,0,0</Color>
</Series>
<Series>
<Rule>$MPElement[Name="Microsoft.SQLServer.2005.BufferCacheHitRatio"]$</Rule>
<Scale>1</Scale>
<Type>Line</Type>
<Color>63,255,63</Color>
</Series>
</Chart>
</Data>
</Value>
</Property>
<Property name="ContextObjectBinding">
<Value>Containment</Value>
</Property>
<Property name="TypeFilter">
<Value>Microsoft.SQLServer.2005.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.ComboBox">
<ReportParameters>
<ReportParameter name="AggregationType">
<Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.Histogram</Prompt>
</ReportParameter>
</ReportParameters>
</Control>
<Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.BooleanPicker">
<ReportParameters>
<ReportParameter name="Enable3D">
<Prompt>Microsoft.SystemCenter.DataWarehouse.Report.Library!Microsoft.SystemCenter.DataWarehouse.Report.ParameterPrompt.Chart3D</Prompt>
</ReportParameter>
</ReportParameters>
</Control>
</Controls>
</ParameterBlock>
</LinkedReport>