SQL Server 2016 Public Preview (CTP2) – Live Query Statistics


I have spent a lot of time at customer locations working on resolving SQL Server performance issues on business critical environments. This also involves helping customers understand how to track down the performance bottlenecks and the remediation steps that need to be taken to remove identified performance bottlenecks. This involves two kinds of troubleshooting: post-mortem analysis and troubleshooting the issue as and when it is happening! Yes, I am talking about live troubleshooting which is a scary thing to do on a production server.

So if you share my deep rooted passion for working on SQL Server performance issues, the Live Query Statistics feature in SQL Server 2016 CTP2 is definitely worth knowing more about!

The Live Query Statistics can be enabled in one of the following ways:

1. Using Management Studio

The screenshot below shows the button which enables the Live Query Statistics. This can be a very powerful tool to troubleshoot bad query performance while the query is actually executing. You actually get insights into the plan and which part of the plan is actually taking time while the query executes.

Live Query Stats button on toolbar

You get a host of information from the Live Query Statistics as seen in the screenshot below. You will be able to pin point the part of the plan which is the culprit because you will have the completion percentage statistics for each and every operator in the plan. The completed operators show you the efficient parts of the plan. Additionally, you also get the time spent in each operator which gives you statistics for identifying the most time consuming part of the plan. And what’s more, you even know how much of the query is completed. This is one of the most common questions that I used to receive from customers while troubleshooting long running queries: “How long will the query take to complete“? Well now, there is an answer!

image

2. Use Activity Monitor

A new grid has been added in Activity Monitor named “Active Expensive Queries” which allows you to right-click a query and click on the “Show Live Execution Plan” option. Live Query Stats button in Activity Monitor

However, the “Show Live Execution Plan” option will only be enabled if the application  or user:

  • Executes SET STATISTICS XML ON; or SET STATISTICS PROFILE ON; in the target session.

  • The query_post_execution_showplan extended event has been enabled. This is a server wide setting that enable live query statistics on all sessions

And if you are developer, then you can use this feature in conjunction with the Transact-SQL debugger and pin point slow parts of the execution plan while the query is running. A truly powerful way to write and optimize queries! The debugging experience is now enhanced as the live query plan can be used along with breakpoints! The screenshot below shows what the debugging experience would look like.

image

Do keep in mind that this feature is provided for troubleshooting/debugging slow running query scenarios and should not be kept enabled in production environments. There is a moderate performance impact for collecting and displaying the above mentioned statistics.

Reference:

Live Query Statistics
https://msdn.microsoft.com/en-us/library/dn831878.aspx 

Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Advertisements

View query plans with non admin accounts


A question was posted on Twitter’s #sqlhelp hashtag on how to allow non-sysadmin accounts or accounts which do not have the View Server State privilege to fetch the cached query plans. This can be a very big deal if you are a hosting company for databases. You would want to restrict access to all non-admin users but at the same time you might need to give your customers and their developers access to their query plans to assist with their query tuning efforts.

The first thing that comes to mind is to use impersonation in SQL Server. Implementing it is where the fun is!

Let’s consider this hypothetical scenario. I have a SQL Server login named Sentinel which would be granted View Server State privileges only. I have another SQL Server login named Agent which is neither a sysadmin nor a login with View Server State privileges. The Agent login need to look up plans of stored procedures which are executing in the current database.

The first task would be to grant View Server State privileges to Sentinel and impersonate privileges to Agent.

USE [master]

GO

GRANT VIEW SERVER STATE TO [sentinel];

GRANT IMPERSONATE ON LOGIN::[sentinel] to [agent];

GO

Next I create a stored procedure which allows the login agent to see the cached plans which are available for the current database context.

CREATE PROCEDURE [dbo].[usp_SeePlan]

AS

BEGIN

    SET NOCOUNT ON

    EXECUTE AS LOGIN = ‘sentinel’;

    SELECT ‘Executed as user: ‘ + SUSER_NAME()

    SELECT * FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp

    CROSS APPLY sys.dm_exec_sql_text (plan_handle) qt

    WHERE qp.dbid = db_id();

    /* Add your diagnostic query here

    The above query is an example */

    REVERT;

    SELECT ‘Reverted back to user: ‘ + SUSER_NAME()

END

Next when I attempt to grant permissions to the login agent to execute the above stored procedure, I will get the error shown below:

Msg 15151, Level 16, State 1, Line 1
Cannot find the user ‘agent’, because it does not exist or you do not have permission.

This error is reported because there is no database user mapped to the login agent. So I create a database user for agent and grant execute permissions to the login agent for the stored procedure using the commands below:

CREATE USER [agent] FOR LOGIN [agent] WITH DEFAULT_SCHEMA=[dbo];
GRANT EXECUTE ON OBJECT::[usp_SeePlan] TO agent;

Next I will try to execute the stored procedure with the agent login. Everything should work now! But alas.. I get another error as shown below:

Msg 916, Level 14, State 1, Procedure usp_SeePlan, Line 5
The server principal “sentinel” is not able to access the database “Test” under the current security context.

This is because of the fact the login sentinel does not exist in the database Test in which the stored procedure exists. Next I will create a database user mapped to the login sentinel.

CREATE USER [sentinel] FOR LOGIN [sentinel] WITH DEFAULT_SCHEMA=[dbo]

Now when I used the stored procedure, everything works!! Screenshot of the output is shown below.

image
A few other caveats to note is that if the stored procedure is created with the WITH EXECUTE AS option, then on execution of the procedure, you will be reported with the following error:

Msg 297, Level 16, State 1, Procedure usp_SeePlan, Line 8
The user does not have permission to perform this action.

So the summary is the following sequence:

1. Grant impersonate rights to Agent for Sentinel

2. Create a stored procedure which does the work which requires View Server State privilege using the EXECUTE AS LOGIN = ‘Sentinel’ statement

3. Create two database users in the database which are mapped to Agent and Sentinel

4. Grant execute privileges on the stored procedure which was created in Step #2.

Voila… You are now ready to impersonate and view the query plan!

XML Plans Saga–Breaking Dawn Part 2


I just completed this series and now there are four posts which points out four different ways of tracking down XML plans for troubleshooting or monitoring query performance:

XML Plans Saga –Twilight – A gist of what graphical execution plans look like.
XML Plans Saga –New Moon – Getting your hands dirty with the actual XML document representing the query plan
XML Plans Saga – Eclipse – Retrieving information about query plans using profiler trace events
XML Plans Saga – Breaking Dawn Part 1 – Fetching information from the cached plans in the SQL Server procedure cache

In this blog post (Breaking Dawn Part 2), I shall talk about the latest release in the SQL family… SQL Server 2012. With the new avatar of the Management Studio, you now have an Extended Event (XEvents) configuration wizard. Even though Extended Events have been around since SQL Server 2008, there wasn’t a wizard available for the configuration of the same. I am going to explain how XEvents can be used to capture execution plans and the existing XEvent Viewer in SQL Server 2012 Management Studio (SSMS) gives you a nice view.

I configured a XEvent trace with an asynchronous ring buffer target and captured only the query_post_execution_showplan event. The XEvent defition is shown below:

CREATE EVENT SESSION [QueryPlanCapture]
ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlos.cpu_id, sqlos.scheduler_id, sqlos.worker_address, sqlserver.database_name, sqlserver.is_system, sqlserver.nt_username, sqlserver.plan_handle, sqlserver.request_id, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username))
ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF)
GO

image_thumb10I used the Watch Live Data option in SSMS (Screenshot 1) to view the data being captured by the configured XEvent session active on the server. Now the XEvent viewer gives you a lot of options to aggregate and group data collect by the XEvent session. There are three things that I did with the GUI:

1. I first used the Choose Columns option to pick the columns that I wanted to be available in the events pane.

2. Then I picked the Grouping option to group on the object_name column value. You can only group on events which are visible in the events pane.

3. Then I picked the Aggregation option to provide me with a SUM of duration and cpu_time values for each of the event occurrences.

You can see the final output in Screenshot 2 below.

You can now see that I have all the execution plans for the execution of the stored procedure sp_helpdb. The Details pane actually gives you the option to view the graphical query plan. Another tip is that if you double-click on any of the events in the details pane, then either open up in a new pop-up window or a new query window depending on the field (eg. showplan_xml).

image_thumb18

As you see from the Screenshot below, there is hordes of information I can pickup from the output without having to start a profiler trace. Since you get the plan_handle and the actual query plan, you can use the queries from the previous posts in the series to get the set options, missing indexes, compilation and execution statistics for the query as well. However, the aggregation option in the XEvent Viewer gives you a head start on a lot of this activity already. image_thumb12

Hope all of you have some fun reading these posts on XML plans and put all this info to good use in your SQL environments!

Till next time Ciao and happy query plan spotting!!