title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | monikerRange |
---|---|---|---|---|---|---|---|---|
Monitor T-SQL with extended events |
Learn how to use extended events to monitor and troubleshooting PREDICT T-SQL statements in SQL Server Machine Learning Services. |
WilliamDAssafMSFT |
wiassaf |
09/24/2019 |
sql |
machine-learning-services |
how-to |
>=sql-server-2017||>=sql-server-linux-ver15||=azuresqldb-mi-current |
[!INCLUDE SQL Server 2017 SQL MI]
Learn how to use extended events to monitor and troubleshooting PREDICT T-SQL statements in SQL Server Machine Learning Services.
The following extended events are available in all versions of SQL Server that support the PREDICT T-SQL statement.
name | object_type | description |
---|---|---|
predict_function_completed | event | Builtin execution time breakdown |
predict_model_cache_hit | event | Occurs when a model is retrieved from the PREDICT function model cache. Use this event along with other predict_model_cache_* events to troubleshoot issues caused by the PREDICT function model cache. |
predict_model_cache_insert | event | Occurs when a model is insert into the PREDICT function model cache. Use this event along with other predict_model_cache_* events to troubleshoot issues caused by the PREDICT function model cache. |
predict_model_cache_miss | event | Occurs when a model is not found in the PREDICT function model cache. Frequent occurrences of this event could indicate that SQL Server needs more memory. Use this event along with other predict_model_cache_* events to troubleshoot issues caused by the PREDICT function model cache. |
predict_model_cache_remove | event | Occurs when a model is removed from model cache for PREDICT function. Use this event along with other predict_model_cache_* events to troubleshoot issues caused by the PREDICT function model cache. |
To view a list of all columns returned for these events, run the following query in SQL Server Management Studio:
SELECT *
FROM sys.dm_xe_object_columns
WHERE object_name LIKE 'predict%'
To capture information about performance of a scoring session using PREDICT:
- Create a new extended event session, using Management Studio or another supported tool.
- Add the events
predict_function_completed
andpredict_model_cache_hit
to the session. - Start the extended event session.
- Run the query that uses PREDICT.
In the results, review these columns:
- The value for
predict_function_completed
shows how much time the query spent on loading the model and scoring. - The boolean value for
predict_model_cache_hit
indicates whether the query used a cached model or not.
In addition to the events specific to PREDICT, you can use the following queries to get more information about the cached model and cache usage:
View the native scoring model cache:
SELECT *
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_NATIVESCORING';
View the objects in the model cache:
SELECT *
FROM sys.dm_os_memory_objects
WHERE TYPE = 'MEMOBJ_NATIVESCORING';
For more information about extended events (sometimes called XEvents), and how to track events in a session, see these articles: