Use Hyper-Q Hints for an Entire Session
Hints are an addition to SQL that allow you to instruct Hyper-Q how to execute a given query. Hints are typically embedded in each statement that references the objects listed in the hints. Session hints let you specify hints at the session level, as opposed to passing them for each individual statement. Thus, all queries sent to the database using a given session will use the hints you provide. The hint will affect only those queries sent from the time the session hint is set. Any earlier queries in the same session are unaffected.
You specify session hints by including a scope in your hint comment. The possible scopes are session and query. If no scope is specified, the default is to use query.
The session hint affects all queries starting from when the parameter is first specified, until either the end of the session, or until a new session value for the same parameter is specified at a later time within the session.
Note
Hints specified at the query level override the session-level hint for the duration of a particular query.
The syntax for session hints is:
-- @dtm_param_name = value for session | query ;
where:
Parameter | Description |
|---|---|
| The name of the hint you want to specify for either an entire session or a query. |
| The value to assign to the hint. |
| The scope of the hint. You can specify either a session or a query level scope. If no scope is specified, the defaults is to use query. |
| When specifying multiple parameters in a single statement, separate them with a semicolon. |
To improve performance, it can be useful to execute UPDATE statements using the native Azure Synapse SQL semantics instead of the Hyper-Q emulation. You can enable Azure Synapse UPDATE statements using the passthru_upd_with_join hint within a session.
-- @dtm_passthru_upd_with_join=on for session
The following example disables transactions in the session. Statements in the transaction will not be rolled back if an error occurs.
--@dtm_disable_user_transactions=true for session BT; delete from tbl_employee; insert into tbl_employees values(1,2,3) ET;