Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor SQL Server Performance with SQL Server Mobile Reports


By:   |   Last Updated: 2019-01-15   |   Comments   |   Related Tips: > 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' 
query results

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'
query results

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
query results

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.

report server project

In the Solution Explorer tab, right click Shared Data Sources and select Add New Data Source.

add 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.

add shared data source

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.

dataset properties

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.

solution explorer

Set the TargetServerURL property in the project Property Page.

target server url

Right click the project and select Deploy.

deploy project

After the deployment process we can see the data sources in the report server web portal.

ssrs web page

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.

report design

Fill the reporting server web URL address in the server address bar and fill the required credentials with user name and password.

Click Connect.

connect to server

Click Add data and then select Report Server.

new mobile report

Select the dataset folder and then add datasets which we created in the previous section.

add data from server

These datasets will be shown in the Data tab of SQL Server Mobile Report Publisher.

report layout

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.

report design

Navigate to the Data tab and change the value to DsGetBatchRequest and select BatchRequestsPerSec column.

report design

Add a Category chart to the design panel and change the Chart orientation to Horizontal.

report design

Then change the Series name field as DsGetWaitStat and field name as wait_type.

Change the Main Series field as waiting_tasks.

report layout

Change the right corner combo box icon to a phone design and then the phone design panel will appear.

report layout

Drag and drop the visuals to the design panel and design the mobile report.

report design

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.

save mobile report

Fill the SQL Server Reporting Service connection details and then click Save.

save mobile report

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.

report output

When we click double side arrow icon, it opens the graphic detail.

report output

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


Last Updated: 2019-01-15


next webcast button


next tip button



About the author
MSSQLTips author Esat Erkec Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools