I would like to describe useful queries for extended events in more detail in this post Extended events get description. I will follow my previous post where I put few basic queries to get some extended events info.
Extended events are triggers occurring in SQL system. Each extended event has its own set of properties -data you get from result. Extended events are collected in packages, events can be combined from different packages. List of packages defined in SQL server instance
SELECT * FROM sys.dm_xe_packages
Beside of that each event can fire other actions synchronously int the same thread within event was invoked. Action extend extended events with own data in response.
For example, if you wold like to get sql text of a query it is defined by an action.
SELECT *
FROM sys.dm_xe_objects o
WHERE o.object_type='action'
To filter event data, you can define Predicates. There is a list of objects you can use as predicate source.
SELECT *
FROM sys.dm_xe_objects o
WHERE o.object_type='pred_source'
List of operators
SELECT *
FROM sys.dm_xe_objects o
WHERE o.object_type='pred_compare'
To start collecting data you must define session object which is set of actions, event data, predicates and target object.
Target object collects output event data by synchronous or asynchronous way. Data can be collected for example to buffer, file, Windows Events, etc.
SELECT * FROM sys.dm_xe_objects o WHERE o.object_type='target'
User – friendly description of internal key words which could be returned from events data
SELECT *
FROM sys.dm_xe_objects o
WHERE o.object_type='map'
Relationship between events implemented in created sessions and actions
SELECT * FROM sys.dm_xe_session_event_actions
Data types you can expect from data collection.
SELECT *
FROM sys.dm_xe_objects o
WHERE o.object_type='type'
Next time I would like to share some solutions implemented using extended events. Stay tuned.
With my friend Mark, we recently discussed how the SQL server can deal with data retrieval, if the data are sorted by index in specified direction and query requests data in the opposite direction. As you can see below SQL server optimizer can deal with this request without the sort operator, which brings less costs in query execution. But it is very important that query meets with index design, otherwise sort operator occurs in query plan.
From samples bellow it is clear how it is important to pay attention to query with comparation to index design to avoid Sort operator
Let’s create a simple table with non-clustered index – ordered in ASC direction.
CREATE TABLE dbo.TestTable(id INT,sec_no INT)
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable (sec_no ASC)
Let’s fill it with data.
INSERT INTO dbo.TestTable SELECT object_id,s.schema_id FROM sys.objects s
Now let’s look at execution plans of queries bellow. The first one ordered in ASC direction and the second one in DESC.
SELECT sec_no FROM dbo.TestTable ORDER BY sec_no ASC
SELECT sec_no FROM dbo.TestTable ORDER BY sec_no DESC
As you can see on both execution plans there are scans of non-clustered index and no sort operators.
Picture 1 – Execution plan
If we look closer on Index Scan operators F4 we can see that optimizer used Scan Direction Forward on the first query execution plan and Backward in plan with DESC ordering.
Forward scan direction
Picture 2 – Execution plan FORWARD scan direction
Backward scan direction
Picture 3 – Execution plan BACKWARD scan direction
Now if we remove non-clustered index we and run above mentioned queries we can see that Sort operator appears in our execution plans.
DROP INDEX IX__TestTable__sec_No ON dbo.TestTable
Picture 4 – Execution plan – sort operator
Create non-clustered index again and change little bit its design. Now the data are ordered by ID column first.
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable (id,sec_no)
Picture 5 – Execution plan – sort operator
Now query optimizer uses index to retrieve data again but because index design does not correspond with our query (ordering of query does not fit to index), data has to be ordered in Sort operator. If we look by F4 on Index scan operator we can see that data are not returned in requested order – Ordered attribute is set to false.
Picture 6 – Execution plan index scan
We can get similar situation if we go back to our first example with right index design.
DROP INDEX IX__TestTable__sec_No ON dbo.TestTable
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable(sec_no ASC)
But now we change our query little bit.
SELECT sec_no,id FROM dbo.TestTable ORDER BY sec_no ASC
From picture we can see that execution plan is similar like the one where we removed index. Here index exist on table but cannot be used to retrieve data because we changed our query the way index cannot be used and sort operator occurs again.
Lets create second table with index designed in right way as our first examples states. And compare both execution plans.
SELECT * INTO dbo.TestTableII FROM dbo.TestTable
CREATE NONCLUSTERED INDEX IX__TestTableII__sec_No ON dbo.TestTableII (sec_no)
Picture 7 – Execution plans comparation
From picture above, we can see that sort operator is a resource consuming operation from the perspective of query optimizer. In case you see it in the execution plan it is necessary to pay attention to it. If we want to avoid sorting in the execution plan, a suitable index design matching our query can help. However, this is not all yet. In the next posts I want to look at the Sort operator in more detail. And we will show that in some cases it also depends on the ordering direction of the index.
