SQL Server 2016 R Services Monitoring with DMVs
These administrative views give us information about the amount of scripts that are running at this time as well as the duration of each of them, in this way we can determine what is the impact of running scripts of R within our SQL Server.
The following are the most common administrative views for monitoring the performance of R Scripts.
- DMV sys. dm_os_performance_counters
- Total Executions: Indicates the number of processes initiated locally or remotely- Parallel Executions: Number of times a script includes a parallel execution- Streaming Executions: Number of times the function was used
- SQL CC Executions: This column indicates the amount of R scripts that were initiated remotely and are utiliz or to SQL Server as a context of execution
- Total execution Time (Ms): This column indicates the time of the execution
- Execution Errors: Number of errors that occurred during the execution of the script.
SELECT * from sys. dm_os_performance_countersWHERE object_name LIKE '% Scripts%'
- DMV sys. dm_external_script_requestsThis administrative view returns one row for each process account being used to execute an external script. It must be emphasized that the account with which it executes the process is not the same credentials of the person who is sending the script for its execution.
This means that if a person sends several jobs to be executed inside the server all these jobs are going to be executed under the same process using a single work account that was assigned to all the scripts sent by the user. The output of this administrative view returns the following information:
Unique identifier what is used to identify the script that is runningLanguage in which the script is written, in our case it would be the R languageAn indicator that specifies whether parallel operations are runningThe name of the working account that is being used for execution, this account is used by the LaunchPad service
SELECT external_script_request_id , [Language] , degree_of_parallelism , external_user_nameFROM sys. dm_external_script_requests;
The result of the execution is seen in the following figure, we can notice that at the moment only one script is running and that the programming language is R.
Get information about the scripts that are runningThe following query returns information about all R language operations that are currently running. It returns valuable information, such as session ID, information about locks, database being used where the script is running, also information about timeout, time taken to execute Information about number of readings and writes that the query is performing.
select r. session_id, r. blocking_session_id, r. status , Db_name (s. Database_id) as database_name, s. login_name , R. wait_time, r. wait_type, r. last_wait_type , R. total_elapsed_time, r. reads, r. logical_reads, r. writes , Er. language as script_type, er. degree_of_parallelism, er. external_user_name from sys. dm_exec_requests as rsys join. dm_external_script_requests as eron r. external_script_request_id = er. external_script_request_idsys join. dm_exec_sessions as son s. session_id = r. session_id
- DMV sys. dm_external_script_execution_statsThis administrative view allows us to know what the advanced analytic functions have been used in the execution of Script that include R language. The result of this query is a list of the different advanced analysis libraries and the number of times they have been executed during the entire period that the sql server instance has been running. In the following image the result of the execution is shown and we can see that the forest algorithm has been executed at least 132 times.
SELECT counter_name, counter_valueFROM sys. dm_external_script_execution_statsWHERE language = 'R';
Regards,
Ing. Eduardo Castro, PhDMicrosoft Data Platform MVP
Comments