Sql-server – SQL Server 2014 View any Live execution plan in Activity Monitor

activity-monitorsql serversql server 2014

I have a SQL Server 2014 Build(12.0.5205.0) running here.

Would It be possible to view any Live execution plan (from queries being executed by other users) in Activity Monitor ?

My problem is:

When I open the Activity Monitor – > Active Expensive Queries, I can see all expensive queries, but clicking right button the "Show Live execution Plan" is only enabled for queries that my user is running, for the any other user this menu is disabled:

An example:

I heard about Trace flags, enable a global trace flag using DBCC traceon(7412,-1).

So, my questions is:

Enabling This flag above (7412), considering the build version of my Sql Server, will it work? Or only in SQL Server 2016?
If it works, I don't have the sa rights. What kind of permissions do I have to ask for?

Best Answer

~~You have to enable trace flag 7412~~

~~DBCC TRACEON (7412, -1)~~

According to blog post, you have to enable xEvent: query_post_execution_showplan

Warning, despite that this feature works starting SQL Server 2014 SP2, it can introduce significant overhead - up to 75%.:

Starting SQL Server 2016 SP1, this was changed and the overhead is only 1-2% (in case of trace flag 7412) or 15% if query_post_execution_showplan xEvent enabled

In 2019 it is enabled by default

Details:

https://blogs.msdn.microsoft.com/sql_server_team/query-progress-anytime-anywhere/

Permissions:

Requires the database level SHOWPLAN permission to populate the Live Query Statistics results page, the server level VIEW SERVER STATE permission to see the live statistics, and requires any permissions necessary to execute the query.

Related Solutions

T-SQL Performance – Using OPTION (FAST x) and Trace Flag 8722

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember 8722 and 8755 being effective was in SQL Server 2000, so it is not surprising you find they are ignored in SQL Server 2012. Trace flag 8602 is still functional in SQL Server 2019 as a global or start-up flag to ignore index hints specifically.

For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is to get the AX developers to fix it on the application side of things.

SQL Server 2008 – Identifying Users Running Expensive Queries

A much better way to monitor is using sp_whoisactive - from Adam Machanic. I use sp_whoisactive with below parameters :

You can even log the output to a physical table for future analysis. It allows you to capture query plan, blocking, etc as well.

EXEC sp_WhoIsActive 
    @filter = '', 
    @filter_type = 'session', 
    @not_filter = '', 
    @not_filter_type = 'session', 
    @show_own_spid = 0, 
    @show_system_spids = 0, 
    @show_sleeping_spids = 1, 
    @get_full_inner_text = 1, 
    @get_plans = 1, 
    @get_outer_command = 1, 
    @get_transaction_info = 0, 
    @get_task_info = 1, 
    @get_locks = 0, 
    @get_avg_time = 0, 
    @get_additional_info = 0, 
    @find_block_leaders = 0, 
    @delta_interval = 0, 
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]', 
    @sort_order = '[start_time] ASC', 
    @format_output = 1, 
    @destination_table = '', 
    @return_schema = 0, 
    @schema = NULL, 
    @help = 0

Best Answer

Related Solutions

T-SQL Performance – Using OPTION (FAST x) and Trace Flag 8722

SQL Server 2008 – Identifying Users Running Expensive Queries

Related Question