By ricardo347


2019-02-05 14:00:45 8 Comments

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:

Activity monitor - from my user

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

So, my questions is:

  1. Enabling This flag above (7412), considering the build version of my Sql Server, will it work? Or only in SQL Server 2016?

  2. If it works, I don't have the sa rights. What kind of permissions do I have to ask for?

1 comments

@Alexandr Volok 2019-02-05 14:37:38

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%.:

enter image description here

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 Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] T-SQL - OPTION (FAST x) and trace flag 8722

2 Answered Questions

[SOLVED] Filter not working on Showplan XML in SQL Server trace

1 Answered Questions

[SOLVED] SQL Server - best practice before applying trace flags?

1 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] Users running expensive queries

1 Answered Questions

[SOLVED] SQL Server - Check for opened Activity Monitor

Sponsored Content