Use the trace tag to check whether the filter statistics are used

In some cases, when there is a large amount of data in the table and the distribution is uneven, the estimated number of rows in the execution plan tends to be very different. Therefore, SQL Server 2008 introduces the filter statistics information, this means that statistics are calculated only for specific data in the table. So how do I know whether the statement uses filter statistics during optimization query? SQL Server provides a hidden trace tag to support this process. The following demo shows how to use the trail tag.

 

First, we will make a simple query for salesorderdetail of adventureworks. The code is shown in Listing 1.

SELECT DISTINCT
        OrderQty,ProductID,SpecialOfferID
FROM    [AdventureWorks].[Sales].[SalesOrderDetail] AS c
WHERE   [c].OrderQty = 1
        AND [c].ProductID = 800
        AND [c].SpecialOfferID = 1

Code List 1. A simple query

 

Let's look at the corresponding execution plan, as shown in 1.

Figure 1. The estimated number of rows is 264.474. The actual number of rows is 328.

 

Therefore, for this specific query, we create a filter statistics. The code is shown in Listing 2.

CREATE STATISTICS [filter_statistics]
ON [Sales].[SalesOrderDetail] (OrderQty,ProductID)
WHERE SpecialOfferID = 1;

Code List 2. Create filter statistics

 

Let's look at the execution plan, as shown in figure 2.

Figure 2. The number of rows estimated after applying the filter index is more accurate

 

When tuning a statement, we want to know whether the statement uses the filter statistics. We can view the code by tracking 9204, as shown in code listing 3.

SELECT DISTINCT
        OrderQty,ProductID,SpecialOfferID
FROM    [AdventureWorks].[Sales].[SalesOrderDetail] AS c
WHERE   [c].OrderQty = 1
        AND [c].ProductID = 800
        AND [c].SpecialOfferID = 1
OPTION  ( QUERYTRACEON 3604, QUERYTRACEON 9204); 

Code List 3. Use tracking to mark 9204

 

Through the information returned in Figure 3, we can see that the filtering statistics have been correctly used.

Figure 3. Filter statistics correctly

 

 

 

Use the trace tag to check whether the filter statistics are used