Whenever there is new model of any vehicle launched in the market, it is expected it should have some enhancements or new features which would make it superior from previous model. If the features are appealing, generally customers are positive and forthcoming to pay little more than previous model.
Above theory holds good for SQL Server product as well. It is expected that SQL Server 2014 should have some new enhancements as compared to the predecessor SQL Server 2012 version. In SQL Server 2014, there are many features which have been added. In this blog we will talk about a feature which allows a developer to see query plan in the run time of the query.
If you are a SQL developer, you might have heard that to tune a query we should look at the query execution plan, find out costly operations and see if there is any chance to tune those pieces. There are two kind of execution plans which we can see with SQL Server Management Studio:
1. Estimated Execution Plan
2. Actual Execution Plan
Estimated plan can be seen without executing the query. SQL optimizer would look at query, table schema, index statistics, index, join and then it would calculate the optimal plan. On the other hand, the actual execution plan shows the plan when query execution is complete. To get the actual execution plan we have to execute the query. Actual execution plan shows the number of rows estimated by optimizer and the actual number of rows after execution of the query operator.
Now imagine a situation where we have to execute a query which takes 20 minutes to execute. As a developer we wanted to “see” what exactly the query is doing in those 20 minutes. Till SQL Server 2014, there was no way to look at the real time query execution plan. It was either estimated or actual which we discussed earlier.
In SQL Server 2014, we have capability to look at query execution plan and status in the real time. There is a new dynamic management view (DMV) called sys.dm_exec_query_profiles introduced with this release. With the help of this DMV, we can now track execution statistics at operator level while query is getting executed.
To get data populated in the query, we first need to enable the actual plan before running the query. Any one of the below MUST be enabled before query execution.
· SET STATISTICS PROFILE ON
· SHOWPLAN XML ON
· SET STATISTICS XML ON
· Include Actual Execution Plan (Ctrl+M) in Management Studio
Here is the quick demo about the usage of DMV. Below script would create a DemoDatabase.
SET NOCOUNT ON
GO
CREATE DATABASE DemoDatabase
GO
USE DemoDatabase
GO
CREATE TABLE myTable(intColumn INT, CharColumn char(4000) DEFAULT 'Name')
GO
DECLARE @intLoop INT
SET @intLoop = 1
WHILE @intLoop <= 100000
BEGIN
INSERT INTO myTable(intColumn) VALUES (@intLoop)
SET @intLoop = @intLoop + 1
END
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_myTable ON myTable
GO
The above script would take some time to run as it has to insert a lot of row. On my machine it took around 3 minutes.
Let’s execute a select query from the table
USE DemoDatabase
GO
SELECT * FROM myTable WHERE intColumn > 50
While above is executing, let’s have a look we look at DMV
SELECT physical_operator_name, row_count, estimate_row_count, *
FROM sys.dm_exec_query_profiles
We would NOT get any results. Any guesses? If you read completely so far, it’s easy to guess that we have not enabled actual execution plan by any of four methods above. Let’s use first method and check.
SET STATISTICS PROFILE ON
GO
SELECT * FROM myTable WHERE intColumn > 50
GO
SET STATISTICS PROFILE ON
GO
Now we can notice that the data is populate in the DMV. As soon as query execution completes, we would see it getting cleared up again.
Now, as a developer, if you want to capture the live plans for the queries which are coming from application then we need to use a trick which I will explain now. Since we can’t control the queries coming from application, we can “inject” query and enable the query plan. We can use any one of the below to achieve this.
1. Start a profiler trace and capture “Showplan Statistics Profile” event.
2. Start a server side trace using sp_trace_create and add eventid = 98. This event is again for SHOWPLAN statistics profile event.
3. Start an extended event session using below query.
CREATE EVENT SESSION [Capture_Query_Plan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO
Once we use any of above method, we can look at the live plan for ALL the queries coming from anywhere. We must point out that there would be little degradation of the performance while running any profiling method. We believe this feature would help developer in digging little deeper and find the cause of slowness.
Click to learn more about Embarcadero database tools related to this post:
- Rapid SQL – The Intelligent IDE for SQL Development
- DB PowerStudio – The ultimate cross-platform database tools