SQL Server Team has been adding bunch of new features and ways for developers to use data in SQL Server. SQL Server 2016 lets developers to run R Scripts in SQL Server. 2017 version added Python scripts and Graph Database Tables. This is great news for developers but DBAs have a lot of concerns about all of these new functions.
As you may know, SQL Server has bunch of DMV tables to help us to find all kind of information about the queries, tables, structures and performance issues in SQL Server. I don't know if you ever try to use these DMV tables before, May be you try to find missing indexes in your database. I am sure you figured out very fast that there a lot of DMV tables out there with all kind of weird column names and definitions, even with the documentation from Microsoft some of these columns still doesn't make any sense. So what do we do in that situation, search Internet for a solution, copy and paste from a forum and hope it works right? ☺
Adding all of these new features makes the DMV tables more complex. Also some of the new DMV tables had to be created to watch these new features. SQL Server 2016 has R Services now and it has been out for some time. But there are many companies out there that upgraded their SQL Servers to 2016 version but their monitoring tools are not ready to monitor R Scripts. Also most of the DBAs are not ready/trained to monitor R Services. Because of this reason, Companies do not want to turn the R Services on until their DBAs are trained or they have some kind of tool they can use to monitor the R Scripts.
Microsoft created couple of ways to monitor R Services scripts. First one is the DMV tables, and if you like to learn about them and create some SQL queries for all of us that's great. If you don't have time for that, Then your second option is to use one of the custom reports created by Microsoft. You can import them into your SSMS and monitor the R Services in that way. Here are the reports.
- R Services - Active Sessions
- R Services - Configuration
- View the installation settings of R Services and properties of the R runtime. You can configure R Services after installation by this report.
- R Services - Configure Instance
- R Services - Execution Statistics
- View the execution statistics of R Services. (Total number of R Scripts executions, number of parallel executions...)
- R Services - Extenden Events
- View the extended events that are available to get more insights into R scripts execution.
- R Services - Packages
- Lists the installed R Packages.
- R Services - Resourse Usage
- View the CPU, Memory, IO consumption of SQL Server and R scripts execution. Also you can view the memory setting of external resource pools.
- R Services - Scripts
All you need to do is download them and import them to your SSMS. (Don't forget if you right click on a report link on the github page and try to save, link will be saved as HTML file and SSMS will give you an error when you try to choose that file to import. Look at the instructions under Run This Sample section to download it in rdl format so SSMS will not give any problems.)
Here is how to import custom reports to SSMS step by step.
I am going to import the R Services Active Sessions report.
I have executed a R Script before I ran this report and here how it looks like with a running R script in the background.
No comments:
Post a Comment