问题描述
我想监控一些性能指标,但不想购买第三方监控产品。有没有方法采集性能指标数据并通过SQL Server Reporting Services展示图形化的趋势数据。
解决方案
有些不同的方法去采集特定的性能指标数据,但是这里我们将专注于用sys.dm_os_performance_counters DMV,以及用SQL Server Reporting Services图表去查看更具可读性的数据。这个动态性能视图不包含所有的性能计数器数据,但是它包含与SQL Server相关的计数值。(注意:在这个DMV中的有些计数值是从上次重启之后的累加值)
你可以查询这个DMV用下面的查询语句:
1
|
SELECT
*
FROM
sys.dm_os_performance_counters
|
从上面的截屏中,看到数据是不易阅读的。它仅显示了在你查询时刻返回的当前值,所以你不知道之前的值,所以你不知道在数据库运行期间性能数据的波动情况。
采集SQL Server监控报表数据
出于简化目的,我们专注于一个计数器值,Page Life Expectancy (PLE)。我将显示如何去采集数据和创建供分析的图表。
首先,我们将创建存储性能指标的表:
1
2
3
4
5
6
|
CREATE
TABLE
[dbo].[CounterCollections](
[ID] [
int
] IDENTITY(1,1)
NOT
NULL
,
[object_name] [
varchar
](128)
NOT
NULL
,
[counter_name] [
varchar
](128)
NOT
NULL
,
[cntr_value] [
bigint
]
NOT
NULL
,
[collection_datetime] [datetime]
NOT
NULL
)
|
接下来,我们需要一个脚本,用于插入数据到这个表中:
1
2
3
4
5
|
INSERT
INTO
CounterCollections
SELECT
object_name, counter_name, cntr_value, GETDATE() collection_datetime
FROM
sys.dm_os_performance_counters
WHERE
object_name =
'SQLServer:Buffer Manager'
AND
counter_name =
'Page life expectancy'
|
最后,我们需要创建一个SQL Server Agent作业,我们将会运行这个脚本在计划任务中:
我将会每5分钟运行该作业:
创建SQL Server监控报表
在该表采集数据的时候,我们可以利用Business Intelligence Development Studio (BIDS) 或 SQL Server Data Tools (SSDT) 创建一个显示数据的SSRS报表。
在这个例子,我们用SSDT。选择File,New Project。在New Project对话框,选择Report Server Project,并输入项目的名字:
右击Shared Data Sources,点击Add New Data Source:
对这个例子,我们使用localhost:
下一步,右击Shared Dataset,点击Add New Dataset:
在此,我们构建在报表中使用到的查询语句。我将用下列语句,WHERE从句创建StartTime和EndTime时间参数:
接下来,点击Reports,Add,New Item:
点击Report,输入报表名称:
一个新的报表创建了。在Report Data窗口,右击Datasets并点击Add Dataset:
点击之前我们创建的Dataset,点击OK:
展开Parameters,双击每个参数并设置参数的类型为Date/Time:
从Toolbox窗口,点击Chart并拖拽到设计视图:
选择Line图表:
点击Chart,将会看到Chart Data窗口出现。添加cutr_value到Values,添加collection_datatime到Category Groups:
我们现在点击Preview去预览一下Page Life Expectancy相关的趋势图:
一旦数据正确显示,你可以格式化图表让它更友好显示:
这只是一个使用Page Life Expectany计数器的例子。你可以创建这种类型的报表用任何SQL Server提供的计数器。这种报表作为一种监控解决方案或用来定位SQL Server性能瓶颈。
另一个多计数器SSRS监控报表示例
这是另一个报表示例,你可以创建含有多个性能计数器的报表。
本文转自UltraSQL51CTO博客,原文链接:http://blog.51cto.com/ultrasql/1580156 ,如需转载请自行联系原作者