Add OPTION(LABEL = 'some text') Hint to SMP Product - by Dan Guzman

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 833055 Comments
Status Resolved Workarounds
Type Suggestion Repros 0
Opened 3/12/2014 6:53:13 PM
Access Restriction Public


The OPTION (LABEL...) query hint currently exists, but is ignored except in PDW queries.  In PDW, the specified label is visible in the monitoring console, making it easy for a DBA to identify the purpose of a query with the option specified.  A query label is particularly useful in monitoring multi-step, long-running processes.
Sign in to post a comment.
Posted by NathanG on 9/13/2015 at 7:37 PM
For the benefit of others wondering about this: the context_info column of sys.dm_exec_requests is generally null or an empty string unless the calling application explicitly sets it using SET CONTEXT_INFO

You can see what this looks like using this example:

    @v_coninfo varbinary(128),
    @v_coninfotxt varchar(128)

set @v_coninfotxt = left('update_stage_big_transaction_history: step 100', 128)
set @v_coninfo = convert(varbinary(128), @v_coninfotxt)
set context_info @v_coninfo

select convert(varchar(128), context_info), context_info, * from sys.dm_exec_requests where context_info is not null ;

sys.dm_exec_query_profiles was added in SQL Server 2014 and so cannot be used in earlier versions.
Posted by Microsoft on 2/11/2015 at 10:38 AM
Thanks for submitting this suggestion.

Adding such a label to the cache key would be rather invasive and we don't have plans to do so at this stage.

You can use context_info in dm_exec_requests if you need to identify specific requests. sys.dm_exec_query_profiles can be used to investigate long-running queries.

Jos de Bruijn - SQL Server PM
Posted by Itzik Ben-Gan on 4/17/2014 at 9:10 PM
Totally agree with Paul. Would be great to see the label exposed in DMVs.
Posted by Paul White NZ on 4/17/2014 at 5:57 PM
This is a great suggestion. Having the label exposed as a column in different DMVs would allow more efficient filtering of interesting queries. Currently, we have to search the entire plan cache using sys.dm_exec_sql_text with a LIKE filter.