My new favourite traceflag
- by Dave Ballantyne
As we are all aware, there are a number of traceflags. Some documented, some semi-documented and some completely undocumented. Here is one that is undocumented that Paul White(b|t) mentioned almost as an aside in one of his excellent blog posts. Much has been written about residual predicates and how a predicate can be pushed into a seek/scan operation. This is a good thing to happen, it does save a lot of processing from having to be done. For the uninitiated though: If we have a simple SELECT statement such as : the process that SQL Server goes through to resolve this is : The index IX_Person_LastName_FirstName_MiddleName is navigated to find the first “Smith” For each “Smith” the middle name is checked for being a null. Two operations!, and the execution plan doesnt fully represent all the work that is being undertaken. As you can see there is only a single seek operation, the work undertaken to resolve the condition “MiddleName is not null” has been pushed into it. This can be seen in the properties. “Seek predicate” is how the index has been navigated, and “Predicate” is the condition run over every row, a scan inside a seek!. So the question is: How many rows have been resolved by the seek and how many by the scan ? How many rows did the filter remove ? Wouldn’t it be nice if this operation could be split ? That exactly what traceflag 9130 does. Executing the query: That changes the plan rather dramatically, and should be changing how we think about the index seek itself. The Filter operator has been added and, unsurprisingly, the condition in this is “MiddleName is not null” So it is now evident that the seek operation found 103 Smiths and 60 of those Smiths had a non-null MiddleName. This traceflag has no place on a production system, dont even think about it
