By: Esat Erkec | Updated: 2019-01-15 | Comments (2) | Related: > Reporting Services Development
Problem
In today's world, mobile devices offer a range of benefits and mobility supports productivity in business operations. Mobile devices increase the ability of communication and access to our apps any place at any time. Mobile application stores (App Store, Play Store) offer tons of solutions for different cases. From this idea, we will try to find the answer to the question "Can we monitor SQL Server performance metrics with mobile devices?".
Solution
The answer to the above question is definitely "YES". In the market, you can find a lot of SQL Server performance monitoring tools and these tools offers different features and options. On the other hand, if your organization has a limited budget you can find some free monitoring tools or you can develop your own monitoring tool. In this tip we will develop our own custom performance monitoring mobile report with help of Microsoft SQL Server Mobile Report Publisher. Mobile Report Publisher allows us to design mobile report solutions and allows us to publish these reports on SQL Server Reporting Services.
What will we do in this tip?
In this tip we will complete three steps:
- Generate SQL Server performance monitoring queries
- Create a dataset of performance monitoring queries on SQL Server Reporting Services
- Design and virtualize the performance monitoring report on Mobile Report
Performance Monitoring Queries
The common methodology to monitoring SQL Server Performance is to use DMVs (Dynamic Management Views). We can find several DMVs to inform us about SQL Server performance metrics and other details.
Batch Requests/sec
In this tip we will measure Batch Requests/sec which indicates the total number of T-SQL commands received by SQL Server per second. This metric is very handy to getting information when SQL Server receives busy commands. This counter value can be found through the following query.
select * from sys.dm_os_performance_counters where LTRIM(RTRIM(OBJECT_NAME)) LIKE '%:SQL Statistics' AND counter_name='Batch Requests/sec'
The cntr_value column indicates the cumulative value of Batch Requests/sec, so we need to make a simple calculation to find out the Batch Requests/sec. The calculation methodology is very simple, we capture 2 batch request values with a 5 second difference, then subtract the second value from the first value and then divide this difference by the time interval.
DECLARE @FirstMesaure INT;DECLARE @FirstMesaureTime DATETIME; SELECT @FirstMesaure = cntr_value, @FirstMesaureTime = getdate() FROM sys.dm_os_performance_counters WHERE LTRIM(RTRIM(OBJECT_NAME)) LIKE '%:SQL Statistics' AND counter_name='Batch Requests/sec' WAITFOR DELAY '00:00:05' SELECT (cntr_value - @FirstMesaure) / DATEDIFF(ss,@FirstMesaureTime, GETDATE()) as BatchRequestsPerSec, GETDATE() AS [Date] FROM sys.dm_os_performance_counters WHERE LTRIM(RTRIM(OBJECT_NAME)) LIKE '%:SQL Statistics' AND counter_name='Batch Requests/sec'
Wait Stats
The second performance metric will be using in the mobile report design is SQL Server wait types. Wait types help us determine performance and other bottleneck problems of SQL Server. Wait types are very detailed topic in SQL Server performance tuning and monitoring, therefore I suggest you check out the tips in the Next Steps section.
The following query will return the wait types in SQL Server. This is a very simple approach to gather this data. You can find several queries on the web that will help further define the results and waits that are of importance.
select wait_type, waiting_tasks_count, wait_time_ms/1000 as wait_time_second from sys.dm_os_wait_stats where wait_time_ms > 0 order by wait_time_ms desc
Create Dataset for SQL Server Mobile Reports
In this section, we will explore how to create a dataset for mobile reports and will create a dataset for SQL Server Mobile Report. We will use Visual Studio 2017 SSDT to complete this step. First of all, launch Visual Studio and then create a Report Server Project.
In the Solution Explorer tab, right click Shared Data Sources and select Add New Data Source.
Click Edit in the Shared Data Source Properties tab and then create the database connection.
Click Test Connection and ensure the proper settings of the database connection credentials and then click OK.
In this step we will create two datasets and these datasets assist to get metrics and performance details about SQL Server. The first dataset gets the Batch Requests/sec value and second dataset gets the wait stats and their values. Now, we will create these two datasets.
Right click Shared Dataset Folder and then paste the following query into the Query textbox.
DECLARE @FirstMesaure INT;DECLARE @FirstMesaureTime DATETIME; SELECT @FirstMesaure = cntr_value, @FirstMesaureTime = getdate() FROM sys.dm_os_performance_counters WHERE LTRIM(RTRIM(OBJECT_NAME)) LIKE '%:SQL Statistics' AND counter_name='Batch Requests/sec' WAITFOR DELAY '00:00:05' SELECT (cntr_value - @FirstMesaure) / DATEDIFF(ss,@FirstMesaureTime, GETDATE()) as BatchRequestsPerSec, GETDATE() AS [Date] FROM sys.dm_os_performance_counters WHERE LTRIM(RTRIM(OBJECT_NAME)) LIKE '%:SQL Statistics' AND counter_name='Batch Requests/sec'
Click Refresh Fields then click OK.
Create a second dataset for the following query and repeat the previous steps which created the dataset using the below query.
select top 10 wait_type, waiting_tasks_count, wait_time_ms/1000 as wait_time_second from sys.dm_os_wait_stats where wait_time_ms > 0 order by wait_time_ms desc
In this step, we will deploy the datasets to SQL Server Reporting Services. However, before starting the deployment process we need to set the Reporting Services target server URL.
Right click on the project and select the Properties tab.
Set the TargetServerURL property in the project Property Page.
Right click the project and select Deploy.
After the deployment process we can see the data sources in the report server web portal.
Designing mobile report on SSRS Mobile Report Publisher
In this section, we will make a server connection in mobile report and get data from reporting services and then we will design the mobile report.
Launch Microsoft SQL Server Mobile Report Publisher and click Server Connections.
Fill the reporting server web URL address in the server address bar and fill the required credentials with user name and password.
Click Connect.
Click Add data and then select Report Server.
Select the dataset folder and then add datasets which we created in the previous section.
These datasets will be shown in the Data tab of SQL Server Mobile Report Publisher.
Now we can start to design the mobile report. In the first step of our report design, we will add a Number visual and we will use it to show average Batch Requests/sec value per hour.
Drag and drop a Number visual to the design panel.
Navigate to the Data tab and change the value to DsGetBatchRequest and select BatchRequestsPerSec column.
Add a Category chart to the design panel and change the Chart orientation to Horizontal.
Then change the Series name field as DsGetWaitStat and field name as wait_type.
Change the Main Series field as waiting_tasks.
Change the right corner combo box icon to a phone design and then the phone design panel will appear.
Drag and drop the visuals to the design panel and design the mobile report.
Finally, we will save our mobile report to SQL Server Reporting Server and connect with any mobile device.
Double click on floppy disk icon in the design panel and select Save to report server.
Fill the SQL Server Reporting Service connection details and then click Save.
After all these steps, we can use the mobile report in any mobile device. In this "SSRS Mobile Reports Connection Settings" tip you can find all details how to make connections and authenticate to SQL Server Reporting Services with a Power BI Mobile App. When we run the mobile report from a mobile device, it will look like the bellow image.
When we click double side arrow icon, it opens the graphic detail.
Summary
In this tip we designed a mobile report that can be used by database administrators to visualize some SQL Server performance counters. You can add new counters and queries to your report design according to your needs.
Next Steps
- Check out the below tips to learn details about SQL Server Wait Stats and DMVs.
- Read the below tutorials to explore all details about SQL Server Mobile Report.
About the author
Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-01-15
