4

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?

Improve this question
5
  • 1
    Live execution plans are supported from 2016 onwards. With 2014 you're simply out of luck -- the necessary data isn't exposed by the server, trace flags or no. (I haven't looked into exactly what SSMS is going to query on a 2014 instance if you pick this feature for your own session, but given that the required view isn't there, I don't see how that would work.)
    – Jeroen Mostert
    Feb 5, 2019 at 14:07
  • But if it not supported, why I can see a live execution plan ran from my user ???
    – ricardo347
    Feb 5, 2019 at 14:09
  • No idea. It would be interesting to find out. Hook up a Profiler and see what queries SSMS fires off. It may be using something undocumented, or else an alternate way that does work for your own session.
    – Jeroen Mostert
    Feb 5, 2019 at 14:10
  • Live execution plans are certainly available in SQL Server 2014. That's when sys.dm_exec_query_profiles was introduced
    – Martin Smith
    Feb 5, 2019 at 14:32
  • this is at best a question for superuser... or the sql support forum ;-)
    – Stefano
    Feb 5, 2019 at 17:37

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.