sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
Gets the stmt_sql_handle for a Transact-SQL statement under given parameterization type (simple or forced). This allows you to refer to queries stored in the Query Store by using their stmt_sql_handle when you know their text.
Transact-SQL syntax conventions
Syntax
sys.fn_stmt_sql_handle_from_sql_stmt
(
'query_sql_text' ,
[ query_param_type
) [;]
Arguments
query_sql_text
Is the text of the query in the query store that you want the handle of. query_sql_text is a nvarchar(max), with no default.
query_param_type
Is the parameter type of the query. query_param_type is a tinyint. Possible values are:
NULL - defaults to 0
0 - None
1 - User
2 - Simple
3 - Forced
Columns Returned
The following table lists the columns that sys.fn_stmt_sql_handle_from_sql_stmt
returns.
Column name | Type | Description |
---|---|---|
statement_sql_handle | varbinary(64) | The SQL handle. |
query_sql_text | nvarchar(max) | The text of the Transact-SQL statement. |
query_parameterization_type | tinyint | The query parameterization type. |
Return code values
0 (success) or 1 (failure)
Remarks
Permissions
Requires the EXECUTE permission on the database, and DELETE permission on the query store catalog views.
Examples
The following example executes a statement, and then uses sys.fn_stmt_sql_handle_from_sql_stmt
to return the SQL handle of that statement.
SELECT * FROM sys.databases;
SELECT * FROM sys.fn_stmt_sql_handle_from_sql_stmt('SELECT * FROM sys.databases', NULL);
Use the function to correlate Query Store data with other dynamic management views. The following example:
SELECT qt.query_text_id, q.query_id, qt.query_sql_text, qt.statement_sql_handle,
q.context_settings_id, qs.statement_context_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_id
CROSS APPLY sys.fn_stmt_sql_handle_from_sql_stmt (qt.query_sql_text, null) AS fn_handle_from_stmt
JOIN sys.dm_exec_query_stats AS qs
ON fn_handle_from_stmt.statement_sql_handle = qs.statement_sql_handle;
See also
- sp_query_store_force_plan (Transact-SQL)
- sp_query_store_remove_plan (Transact-SQL)
- sp_query_store_unforce_plan (Transact-SQL)
- sp_query_store_reset_exec_stats (Transact-SQL)
- sp_query_store_flush_db (Transact-SQL)
- sp_query_store_remove_query (Transact-SQL)
- Query Store Catalog Views (Transact-SQL)
- Monitoring Performance By Using the Query Store
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για