sql server 监视_监视SQL Server报告服务

sql server 监视

介绍 ( Introduction )

In our last get together I mentioned that oft times SQL Server reports are created due to a dire business need to be used once and never again. Further, some reports that we believe are not often used could be “top of the pops” unbeknown to us. A guess as to a number of times a report is used per month, in addition to the statistics behind each report should not be guesswork, but rather monitored actively to ensure that frequently used reports are both efficient and effective. Further, those reports that are either not used or have not been run in quite some time, should perhaps be removed in order to keep the server clean and not cluttered.

在我们的上一次聚会中，我提到，由于可怕的业务需要一次又一次地使用SQL Server报告，因此经常创建。 此外，一些我们认为不经常使用的报告可能是我们所不知道的“流行音乐之首”。 除了每个报告背后的统计信息之外，对于每个月使用报告次数的猜测不应该是猜测，而应进行积极监控以确保经常使用的报告既高效又有效。 此外，也许应该删除那些未使用或已经运行了相当长时间的报告，以保持服务器的清洁和整洁。

In today’s get together, we shall be constructing a quick and dirty SQL Server Reporting Services application that will track report usage and runtime statistics to ensure that your servers are offering your users the best of both worlds, i.e. effective reports, cleaner servers and faster access time.

在今天的聚会中，我们将构建一个快速且肮脏SQL Server Reporting Services应用程序，该应用程序将跟踪报告的使用情况和运行时统计信息，以确保您的服务器为用户提供两全其美的体验，即有效的报告，更清洁的服务器和更快的访问权限时间。

Our finished dashboard will look similar to the one shown below.

我们完成的仪表盘看起来类似于下面显示的仪表盘。

Let’s get started!!

让我们开始吧！！

入门 ( Getting started )

We start by opening Visual Studio and creating a new Reporting Services Project.

我们首先打开Visual Studio并创建一个新的Reporting Services项目。

We select “New” then “Project” (see above).

我们选择“新建”，然后选择“项目”（见上文）。

Next, we set the project type to Reporting Services and select a “Report Server Project” (see above). We click OK to continue.

接下来，我们将项目类型设置为Reporting Services，然后选择“ Report Server项目”（请参见上文）。 我们单击“确定”继续。

We now find ourselves at our Reporting Services workspace.

现在，我们可以在Reporting Services工作区中找到自己。

Our first task is to create a new “Shared Data Source”. As we have discussed in many past articles, a “Data Source” may be likened to a water hose connecting the house water tap (the database) to the pot plants 20m from the house (the datasets behind our charts/matrices etc.).

我们的首要任务是创建一个新的“共享数据源”。 正如我们在过去的许多文章中所讨论的那样，“数据源”可以比喻为将房屋水龙头（数据库）连接到距离房屋20m的盆栽植物（我们的图表/矩阵等的数据集）之后的水管。

To begin we right-click on the “Shared Data Sources” folder and select “Add New Data Source”.

首先，我们右键单击“共享数据源”文件夹，然后选择“添加新数据源”。

The “Shared Data Source” dialogue box is brought up. We give our data source a name “SQLShackMonitoring01”. Next, we click the “Edit” button to the right of the “Connection string” text box. The “Connection Properties” dialogue box is then opened and we configure our server name and NOTE that the database selected is our “ReportServer$STEVETOPMULTI” (see above and to the right).

出现“共享数据源”对话框。 我们将数据源命名为“ SQLShackMonitoring01”。 接下来，我们单击“连接字符串”文本框右侧的“编辑”按钮。 然后打开“连接属性”对话框，我们配置服务器名称，并注意选择的数据库是“ ReportServer $ STEVETOPMULTI”（见上和右图）。

Testing the connection, we see that all is in order (see above). We click OK, OK and OK again to exit the “Data source configuration” screens.

测试连接后，我们看到一切正常（请参见上文）。 我们单击“确定”，“确定”，然后再次单击“确定”退出“数据源配置”屏幕。

The reader will note that we have been returned to our work surface and the new data source is present in the top right of the screenshot above.

读者会注意到，我们已经返回工作界面，并且新数据源位于上面的屏幕截图的右上方。

To find more detailed instructions on configuring data sources and creating datasets, the reader is referred to one of my previous articles entitled “Life is full of choices”

要查找有关配置数据源和创建数据集的更详细的说明，请读者参阅我之前写的一篇标题为《生活充满选择》的文章。

/life-full-choices/

/生活充实的选择/

创建我们的“监控”报告 ( Creating our “monitoring” report)

We start the development process by creating our one and only report. We right-click on the “Reports” directory (see above and to the right) and select “Add” and “New Item” from the context menu (see above).

我们通过创建我们唯一的报告来开始开发过程。 我们右键单击“ Reports”目录（见上和右），然后从上下文菜单中选择“ Add”和“ New Item”（见上）。

The “Add New Item” window is brought up. We select “Report” and give our report the name “SQLShackReportMonitoring’. We click “Add”.

出现“添加新项”窗口。 我们选择“报告”并将报告命名为“ SQLShackReportMonitoring”。 我们点击“添加”。

Our report drawing surface is brought into view (see above).

我们的报告图纸表面已显示（见上文）。

Having resized the drawing surface, we are ready to get started.

调整图纸表面的大小后，我们就可以开始了。

创建必要的存储过程 ( Creating the necessary stored procedures )

As most of you know by now, my favorite approach to pulling report data from the database tables is via stored procedures as opposed to utilizing T-SQL commands. Stored procedures are definitely more optimal, clean and compact.

众所周知，到目前为止，我最喜欢的从数据库表中提取报表数据的方法是通过存储过程，而不是利用T-SQL命令。 存储过程绝对是最佳，干净和紧凑的。

In the screen dump shown below, we note that we once again utilize a report that we created in a previous “get together”.

在下面显示的屏幕转储中，我们注意到我们再次利用了在先前的“聚在一起”中创建的报告。

/life-full-choices/

/生活充实的选择/

This report permits the user to find the models, colors, and a number of doors of automobiles available for sale at “SQLShackFinancial”. Running reports from the Report Server creates statistical data and it is this data that we shall be pulling for our monitoring report.

该报告允许用户找到可在“ SQLShackFinancial”处出售的汽车的型号，颜色和许多车门。 从报表服务器运行报表会创建统计数据，正是我们要从中提取这些数据作为监控报告。

I have taken the liberty of running this report a few times to generate some data.

我已经多次运行此报告以生成一些数据。

存储过程1“每月执行的报告” ( Stored Procedure 1 “Reports Executed per Month” )

Running our “Reports Executed per Month” query, we find that a number of times that the “Cars” report was run three times in February 2015 (see below).

在运行“每月执行的报告”查询时，我们发现“汽车”报告在2015年2月运行了3次（见下文）。

Further, the query gives us the “reporting period” year and month, the report execution count, the time spent processing the reports, the byte count of data passed back, the number of rows returned by the query and finally the month name of the reporting period. All of which are interesting and valuable statistics.

此外，查询还为我们提供了“报告期”年和月，报告执行次数，处理报告所花费的时间，传回数据的字节数，查询返回的行数以及最终的月名称。报告期。 所有这些都是有趣且有价值的统计数据。

The code for this query may be found in Addenda 1.

该查询的代码可以在附录1中找到。

存储过程2“矩阵统计” ( Stored Procedure 2 “Statistics for Matrix” )

This query / stored procedure will pull the following statistics for each and every report run on our server for the current time period. The reader will note that the data returned includes the report year, the report month, the report day, the type of object that caused the counter to increment (2 is a report). The reader may follow up on this one with the following link.

该查询/存储过程将为服务器在当前时间段内运行的每个报告提取以下统计信息。 读者会注意到，返回的数据包括报告年份，报告月份，报告日期，导致计数器增加的对象类型（2是报告）。 读者可以通过以下链接来继续阅读。

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/60dd3392-42d8-4dc4-b8e6-15e9aeaad29e/table-explaination-for-dbocatalog-table-in-reportserver-database

https://social.msdn.microsoft.com/Forums/sqlserver/zh-CN/60dd3392-42d8-4dc4-b8e6-15e9aeaad29e/table-explaination-for-dbocatalog-table-in-reportserver-database

The execution count, the total time for the data retrieval and processing time are also returned along with the number of bytes transferred and the TOTAL number of rows that were returned.

执行计数，数据检索和处理的总时间以及返回的字节数和返回的总行数也将返回。

The code for this procedure may be found in Addenda 2.

此过程的代码可以在附录2中找到。

存储过程3“前5个报告” ( Stored Procedure 3 “Top 5 Reports” )

Our next stored procedure will be utilized to obtain the five most popular reports. This is a great feature as it tells us which reports our users find most useful in their day to day activities.

我们的下一个存储过程将用于获取五个最受欢迎的报告。 这是一项很棒的功能，它可以告诉我们用户在日常活动中发现哪些报告最有用。

The code listing may be found in Addenda 3.

代码清单可以在附录3中找到。

存储过程4“未使用的报告” ( Stored Procedure 4 “Unused Reports” )

This stored procedure is perhaps the most important to us. It will tell us which reports have not been run during the current period of time. This “period” could be the current month. The stored procedure gives us the name of the report, the path of the report and the ID of the users that last called the report.

这个存储过程对我们来说也许是最重要的。 它会告诉我们在当前时间段内尚未运行哪些报告。 此“期间”可能是当前月份。 该存储过程为我们提供了报告的名称，报告的路径以及最后一次调用该报告的用户的ID。

The code listing for this stored procedure may be found in Addenda 4.

该存储过程的代码清单可以在附录4中找到。

同时，回到我们的Reporting Server项目 ( Meanwhile, back in our Reporting Server project )

Having now constructed the four necessary stored procedures that are required to complete our monitoring project, we must now add the first of four local / embedded data set. We right-click on the Dataset folder and select “Add Dataset” (see above).

现在已经构建了完成我们的监视项目所需的四个必要的存储过程，我们现在必须添加四个本地/嵌入式数据集的第一个。 我们右键单击“数据集”文件夹，然后选择“添加数据集”（请参见上文）。

The Dataset Properties dialogue box opens. We select the “Use a dataset embedded in my report” option. We then click the “New” button to the right of the “Data Source” drop down box to create a new LOCAL data source (see above).

将打开“数据集属性”对话框。 我们选择“使用报表中嵌入的数据集”选项。 然后，我们单击“数据源”下拉框右侧的“新建”按钮，以创建新的本地数据源（请参见上文）。

The “Change name, type, and connection options” dialogue box is brought into view. We give our connection a name “SQLShackFinancial02” and click the edit button to bring up the “Connection Properties” dialogue box where we enter our server name and “Select or enter a database name”. In our case, we select “ReportServer$STEVETOPMULTI” (see above). We click OK, OK and OK to exit the “Connection” dialogue pages.

出现“更改名称，类型和连接选项”对话框。 我们为连接指定一个名称“ SQLShackFinancial02”，然后单击“编辑”按钮以打开“连接属性”对话框，在其中输入我们的服务器名称和“选择或输入数据库名称”。 在本例中，我们选择“ ReportServer $ STEVETOPMULTI”（请参见上文）。 我们单击确定，确定和确定以退出“连接”对话框页面。

We find ourselves back at our drawing surface with our newly created dataset (see above). We must now configure this dataset.

我们使用新创建的数据集回到自己的绘图表面（请参见上文）。 现在，我们必须配置该数据集。

Double clicking on the dataset, brings up the “Dataset Properties” dialogue box (once again).

双击数据集，弹出“数据集属性”对话框（再次）。

We select the “Stored Procedure” radio button and then click “Refresh Fields” (see above).

我们选择“存储过程”单选按钮，然后单击“刷新字段”（请参见上文）。

Clicking on the fields tab (see above), we immediately see the fields that we pulled from our first code listing (see Addenda 1). We click OK to exit the “Data Properties” dialogue box.

单击字段选项卡（见上文），我们立即看到从第一个代码清单中拉出的字段（见附录1）。 我们单击确定退出“数据属性”对话框。

Opening the “Toolbox” (see above and immediately to the left) we add a “Chart” as may be seen below.

打开“工具箱”（请参见上方和左侧），我们将添加一个“图表”，如下图所示。

We resize our chart (see below).

我们调整图表大小（请参见下文）。

We then set the chart’s “DataSetName” property to the name of the dataset that we just created “ReportsExecutedPerMonth” (see above and to the right).

然后，将图表的“ DataSetName”属性设置为刚创建的“ ReportsExecutedPerMonth”数据集的名称（请参见上方和右侧）。

By clicking on one of the columns of the vertical bar chart, we reveal the “Chart Data” dialogue box (see above).

通过单击垂直条形图的一列，我们将显示“图表数据”对话框（请参见上文）。

For our “Values” we shall utilize only the “Execution Count” and the “TimeDataRetrievalSum” fields. Obviously, we could have chosen more fields. For the ‘Category Groups”, we select “YearMth” and “Monthee”. The field “YearMth” will ensure that the “Monthee’s” are correctly sorted. We do however have to make one alteration to the “YearMth” field and that is to ensure that it does not appear on our chart. Once again, its sole purpose is to ensure that the months are correctly sorted.

对于我们的“值”，我们将仅使用“执行次数”和“ TimeDataRetrievalSum”字段。 显然，我们可以选择更多的字段。 对于“类别组”，我们选择“年份”和“ Monthee”。 “ YearMth”字段将确保正确排序“ Monthee's”。 但是，我们必须对“ YearMth”字段进行一种更改，以确保它不会出现在我们的图表中。 再次，其唯一目的是确保正确排序月份。

We right-click on the “YearMth” Category Group and select the “Category Group Properties” option.

我们右键单击“年份”类别组，然后选择“类别组属性”选项。

The “Categories Group Properties” dialogue box is brought into view (see above). We click the function box to the right of the ”Label” dialogue box to bring up the “Expression” dialogue box (see below).

出现“类别组属性”对话框（请参见上文）。 我们单击“标签”对话框右侧的功能框，以调出“表达”对话框（如下所示）。

We change the value “ =Fields!YearMth.Value” to “=Nothing” (see below).

我们将值“ = Fields！YearMth.Value ”更改为“ = Nothing”（请参见下文）。

We click OK, OK to leave the dialogue boxes.
Let us now see what our report looks like thus far. We click the “Preview” button from our work space ribbon.

我们单击确定，确定离开对话框。
现在让我们看看我们的报告到目前为止是什么样的。 我们从工作区功能区中单击“预览”按钮。

We see our chart for February. Note that the total retrieval time is +/- 60 msec and 3 executions occurred.

我们看到了2月份的图表。 请注意，总检索时间为+/- 60毫秒，执行了3次。

Cleaning our report a tad, we add a title for the chart and label the X and Y axes (see above).

清理报告时，我们为图表添加标题并标记X和Y轴（请参见上文）。

Our chart is a bit simplistic and it would be most useful to have a look at many of the statistics behind the data.

我们的图表有点简单，查看一下数据背后的许多统计数据将非常有用。

We now add a “Matrix” component below our chart.

现在，我们在图表下方添加一个“矩阵”组件。

As with the “Chart”, we need to define a dataset with will provide the raw data to the matrix.

与“图表”一样，我们需要定义一个数据集，它将原始数据提供给矩阵。

Once again we right click upon the “Dataset” folder and select “Add Dataset” (see above).

我们再次右键单击“ Dataset”文件夹，然后选择“ Add Dataset”（请参见上文）。

We give our dataset a name “MatrixRawData” and select the “Data source” that we created for our chart. Once created, the local data source “SQLShackFinancial02” is available to be utilized for any other local / embedded datasets that require it.

我们为数据集命名为“ MatrixRawData”，然后选择为图表创建的“数据源”。 创建后，本地数据源“ SQLShackFinancial02”可用于需要它的任何其他本地/嵌入式数据集。

I select the “SteveTopMonitorSP” stored procedure as my data conduit from the database table to the report dataset (see above). We click OK and are returned to our work surface.

我选择“ SteveTopMonitorSP”存储过程作为从数据库表到报表数据集的数据管道（请参见上文）。 单击确定，然后返回到工作界面。

The newly created dataset may be seen in the screen dump above.

在上面的屏幕转储中可以看到新创建的数据集。

Clicking upon the matrix, we may now set its “DataSetName” property to our newly created dataset (see below).

单击矩阵，我们现在可以将其“ DataSetName”属性设置为新创建的数据集（见下文）。

We now note that numerous interesting data fields are available to our Matrix (see below). The code listing providing this data may be seen in Addenda 2.

现在我们注意到，Matrix可以使用许多有趣的数据字段（请参见下文）。 在附录2中可以看到提供此数据的代码清单。

The astute reader will note that we have a column grouping aspect which may be seen in the screen dump above. We do not require this and therefore we shall now remove the column grouping PRIOR to adding any of the available data fields to our matrix.

精明的读者会注意到，我们有一个列分组方面，可以在上面的屏幕转储中看到。 我们不需要这样做，因此在将任何可用数据字段添加到矩阵之前，我们现在将删除列分组。

We right click on the “ColumnGroup” and select “Delete Group” (see above).

我们右键单击“ ColumnGroup”，然后选择“ Delete Group”（参见上文）。

We are asked if we want to delete the data and the grouping OR just the grouping. We select “Delete group only” and click OK.

询问我们是否要删除数据和分组或仅删除分组。 我们选择“仅删除组”，然后单击“确定”。

As we have eleven fields that we wish to display in our matrix, we shall add nine more columns to the right of the two columns that we were given when we added the matrix to our work surface.

由于我们希望在矩阵中显示11个字段，因此，当将矩阵添加到工作表面时，我们将在两列的右边再增加9列。

Our modified matrix (with the extra columns) may be seen above.

我们的修改矩阵（带有额外的列）可以在上方看到。

Clicking on the ellipsis in the second row of the matrix (which I have highlighted in grey) we can see the fields that are available to us. Further, I have taken the liberty of populating the fields of the matrix to reflect our needs.

单击矩阵第二行中的省略号（已用灰色突出显示），我们可以看到可用的字段。 此外，我自由地填充矩阵的字段以反映我们的需求。

When run, our report now appears as follows (see below).

运行时，我们的报告现在显示如下（如下所示）。

以类似的方式 ( In a similar manner)

In a similar manner, we may add the remaining two matrices that will complete our report and hook them up to the code listings in Addenda 3 and Addenda 4.

以类似的方式，我们可以添加剩下的两个矩阵来完成我们的报告，并将它们连接到附录3和附录4中的代码清单。

The dataset and matrix for the “Top 5” may be seen above.

“ Top 5”的数据集和矩阵可以在上方看到。

The dataset and matrix for the “Unused reports” may be seen above.

上面可以看到“未使用报告”的数据集和矩阵。

Our completed report may be seen above. Clicking the “Preview” button on the report ribbon, we may see our report results.

我们的完整报告可以在上方看到。 单击报告功能区上的“预览”按钮，我们可能会看到报告结果。

结论 ( Conclusions )

SQL Server Reporting Servers often become clogged with reports of minimal importance and /or minimal usage. Regular maintenance and good house cleaning help us ensure that our servers are being both efficient and effective.

SQL Server报表服务器经常被重要性最低和/或用法最少的报表所阻塞。 定期维护和良好的房屋清洁有助于我们确保服务器高效运行。

Developing a quick and dirty Reporting Services monitor can help us keep our finger on the pulse and inform us of end-user utilization.

开发一个快速而肮脏的Reporting Services监视器可以帮助我们及时掌握最新情况，并告知我们最终用户的使用情况。

The SQL Server Reporting Services database has a wealth of valuable metrics with which to perform our analysis. The “catalog” table contains many of the criteria that we have utilized above.

SQL Server Reporting Services数据库具有大量有价值的指标，可用来执行我们的分析。 “目录”表包含我们上面已经使用的许多标准。

Whilst we have only touched upon a few of the plethora of available fields, we have amazing possibilities of adding more charts and matrices to our monitoring tool.

尽管我们仅涉及了众多可用字段，但我们仍有惊人的可能性将更多的图表和矩阵添加到我们的监视工具中。

Happy programming!!

编程愉快！

附录1 ( Addenda 1 )

 
USE [ReportServer$STEVETOPMULTI]
GO
 
/****** Object:  StoredProcedure [dbo].[SteveTopMonitorChartSP]    Script Date: 2/8/2015 3:57:00 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
--alter procedure [dbo].[SteveTopMonitorChartSP]
--as
declare @Yearr varchar(4)
declare @LowYearr varchar(4)
declare @decider int
declare @BeginFiscal as date
declare @endfiscal as date
declare @YearIncoming as varchar(4)
--declare @YearIncoming as varchar(4)
set @decider = datepart(Month,convert(date,getdate()))
set @Yearr = datepart(YEAR,Convert(date,Getdate()))
set @Lowyearr = @Yearr 
set @Lowyearr = case when @decider > 6 then datepart(YEAR,Convert(date,Getdate())) else @LowYearr -1 end
set @Yearr    = case when @decider >= 7 then datepart(YEAR,Convert(date,Getdate())) + 1 else @Yearr  end
set @YearIncoming = @yearr
set @Beginfiscal = convert(varchar(4),@LowYearr) + '0701'
set @Endfiscal   = convert(varchar(4),@Yearr) + '0630'
select YearMth,Type,ExecutionCount,TimeDataRetrievalSum,TimeProcessingSum,TimeRenderingSum,ByteCountSum,RowCountSum,
case when right(YearMth,2) = '07' then 'Jul'
     when right(YearMth,2) = '08' then 'Aug'
     when right(YearMth,2) = '09' then 'Sep'
     when right(YearMth,2) = '10' then 'Oct'
     when right(YearMth,2) = '11' then 'Nov'
     when right(YearMth,2) = '12' then 'Dec'
     when right(YearMth,2) = '01' then 'Jan'
     when right(YearMth,2) = '02' then 'Feb'
     when right(YearMth,2) = '03' then 'Mar'
     when right(YearMth,2) = '04' then 'Apr'
     when right(YearMth,2) = '05' then 'May'
     when right(YearMth,2) = '06' then 'Jun' end as Monthee
from (
SELECT   
    convert(varchar(4),datepart(Year,convert(date,timestart))) +
	case when Len(convert(varchar(2),datepart(Month,convert(date,timestart)))) = 1 then '0' + convert(varchar(2),datepart(Month,convert(date,timestart)))
	else convert(varchar(2),datepart(Month,convert(date,timestart))) end as Yearmth
    , Type
    , COUNT(Name) AS ExecutionCount
    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
    , SUM(TimeProcessing) AS TimeProcessingSum
    , SUM(TimeRendering) AS TimeRenderingSum
    , SUM(ByteCount) AS ByteCountSum
    , SUM([RowCount]) AS RowCountSum
FROM
(
    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,
  TimeProcessing, TimeRendering, ByteCount, [RowCount]
    FROM
    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID =
       ExecutionLog.ReportID LEFT OUTER JOIN
    Users ON Catalog.CreatedByID = Users.UserID
    WHERE ExecutionLog.TimeStart BETWEEN @BeginFiscal AND @EndFiscal
) AS A
GROUP BY
       convert(varchar(4),datepart(Year,convert(date,timestart))) +
	case when Len(convert(varchar(2),datepart(Month,convert(date,timestart)))) = 1 then '0' + convert(varchar(2),datepart(Month,convert(date,timestart)))
	else convert(varchar(2),datepart(Month,convert(date,timestart))) end
    , Type) B
ORDER BY
         Type
 
GO
 

附录2“报告运行时统计信息” ( Addenda 2 “Report run time statistics” )

 
USE [ReportServer$STEVETOPMULTI]
GO
 
/****** Object:  StoredProcedure [dbo].[SteveTopMonitorSP]    Script Date: 2/8/2015 4:31:31 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
--CREATE procedure [dbo].[SteveTopMonitorSP]
--as
DECLARE @DateFrom1 Varchar(8)
DECLARE @DateTo1 Varchar(8)
DECLARE @DateFrom date
DECLARE @DateTo Date
SET @DateFrom1 = Convert(varchar(8),datepart(Year,convert(date,Getdate()))) + '0101'
SET @DateTo1   = Convert(varchar(8),datepart(Year,convert(date,Getdate()))) + '1231'
SET @DateFrom  = Convert(date,@DateFrom1)
SET @DateTo    = Convert(date,@DateTo1)
 
SELECT   
      DATEPART(Year, convert(Date,TimeStart)) AS ReportYear
    , DATEPART(Month, TimeStart) AS ReportMonth
    , DATEPART(Day, TimeStart) AS ReportDay
    , DATEPART(Hour, TimeStart) AS ReportHour
    , Type 
    , COUNT(Name) AS ExecutionCount
    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
    , SUM(TimeProcessing) AS TimeProcessingSum
    , SUM(TimeRendering) AS TimeRenderingSum
    , SUM(ByteCount) AS ByteCountSum
    , SUM([RowCount]) AS RowCountSum
FROM
(
    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,
  TimeProcessing, TimeRendering, ByteCount, [RowCount]
    FROM
    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID =
       ExecutionLog.ReportID LEFT OUTER JOIN
    Users ON Catalog.CreatedByID = Users.UserID
    WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo
) AS RE
GROUP BY
      DATEPART(Year, convert(Date,TimeStart))
    , DATEPART(Month, TimeStart)
    , DATEPART(Day, TimeStart)
    , DATEPART(Hour, TimeStart)
    , Type
ORDER BY
      ReportYear
    , ReportMonth
    , ReportDay
    , ReportHour
    , Type
GO
 

附录3“前五名报告” ( Addenda 3 “Top 5 Reports” )

 
USE [ReportServer$STEVETOPMULTI]
GO
 
/****** Object:  StoredProcedure [dbo].[SteveMiddleMonitorSP]    Script Date: 2/8/2015 4:45:57 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 --Top 5 Most Frequent report that are run
 --create procedure [dbo].[SteveMiddleMonitorSP]
 --as
DECLARE @DateFrom1 Varchar(8)
DECLARE @DateTo1 Varchar(8)
DECLARE @DateFrom date
DECLARE @DateTo Date
SET @DateFrom1 = Convert(varchar(8),datepart(Year,convert(date,Getdate()))) + '0101'
SET @DateTo1   = Convert(varchar(8),datepart(Year,convert(date,Getdate()))) + '1231'
SET @DateFrom  = Convert(date,@DateFrom1)
SET @DateTo    = Convert(date,@DateTo1)
 
SELECT TOP 5 
      COUNT(Name) AS  [Times Executed] 
    , Name
    , SUM(TimeDataRetrieval) AS DataRetrievalTime
    , SUM(TimeProcessing) AS DataProcessingTime
    , SUM(TimeRendering) AS DataRenderingTime
    , SUM(ByteCount)   AS BytesOfData
    , SUM([RowCount])  AS NumberOfFullRowsPulled 
FROM
(
    SELECT TimeStart, Catalog.Type, Catalog.Name,
    TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]
    FROM
    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
     WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2
) AS B
GROUP BY Name
ORDER BY COUNT(Name) DESC,Name
GO
 

附录4 ( Addenda 4 )

 
USE [ReportServer$STEVETOPMULTI]
GO
 
/****** Object:  StoredProcedure [dbo].[SteveBottomMonitorSP]    Script Date: 2/9/2015 8:28:52 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
--CREATE procedure [dbo].[SteveBottomMonitorSP]
-- as
DECLARE @DateFrom1 Varchar(8)
DECLARE @DateTo1 Varchar(8)
DECLARE @DateFrom date
DECLARE @DateTo Date
SET @DateFrom1 = Convert(varchar(8),(datepart(Year,convert(date,Getdate()))) -1)+ '0701'
SET @DateTo1   = Convert(varchar(8),datepart(Year,convert(date,Getdate()))) + '0131'
SET @DateFrom  = Convert(date,@DateFrom1)
SET @DateTo    = Convert(date,@DateTo1)
--select @DateFrom as [Start Date], @DateTo as [End Date]
SELECT Name, Path, UserName
FROM Catalog INNER JOIN dbo.Users ON Catalog.CreatedByID = Users.UserID
WHERE Type = 2 AND
    Catalog.ItemID NOT IN
    (
        SELECT ExecutionLog.ReportID
        FROM ExecutionLog
         WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo
    )
    ORDER BY Name
GO
 

翻译自: https://www.sqlshack.com/monitoring-sql-server-reporting-services/

sql server 监视