I have many queries on table dbo.tblSAPDespatchItem, some of them have the following condition in the where clause:

WHERE   b.lngDespatchItemStatusID = 2 
AND     b.decvalue > 0

for example

Partial list of a query:

            ,despatchDate= CAST(CAST( a.dtmUpdated AS DATE ) AS DATETIME)

    FROM    dbo.tblSAPDespatch a
    INNER JOIN dbo.tblSAPDespatchItem b on a.lngDespatchID = b.lngDespatchID
    INNER JOIN [dbo].[tblBorder] C on C.strBxOrderNo = A.strBxOrderNo

    INNER JOIN dbo.tblBAccountParticipantEmail D on C.lngOrderedByParticipantID = D.lngParticipantID

    INNER JOIN dbo.tblBAccountParticipant E on d.lngParticipantID = e.lngParticipantID
    INNER JOIN dbo.vwtblItem F on f.strItemNo = b.strItemNo
    INNER JOIN @DIS DIS ON A.lngDespatchStatusID = DIS.lngDespatchStatusID
    INNER JOIN dbo.tblBMarket H on H.sintMarketID = c.sintMarketID

    WHERE   b.lngDespatchItemStatusID = 2 
    AND     f.strStyle not in ( 'VOUCH', 'GG001', 'GG002' )
    AND     d.strEmail = @email 
    AND     a.sintMarket = @marketId 
    AND     a.[dtmUpdated] BETWEEN @BeginDt AND @EndDate
    AND     b.decvalue > 0

Can I use statistics to assess the benefit of adding a filtered index?

Using the scripts from this question here:

How to script statistics in Sql Server? (using T-SQL)

I get all stats on that table:

CREATE STATISTICS [_WA_Sys_00000006_33B97E11] ON [dbo].[tblSAPDespatchItem]( [strItemNo])
CREATE STATISTICS [_WA_Sys_00000007_33B97E11] ON [dbo].[tblSAPDespatchItem]( [strLocation])
CREATE STATISTICS [_WA_Sys_00000008_33B97E11] ON [dbo].[tblSAPDespatchItem]( [sintOrderItemSeqNo])
CREATE STATISTICS [_WA_Sys_0000000E_33B97E11] ON [dbo].[tblSAPDespatchItem]( [lngUserID])
CREATE STATISTICS [_WA_Sys_0000000F_33B97E11] ON [dbo].[tblSAPDespatchItem]( [dtmUpdated])
CREATE STATISTICS [_WA_Sys_00000010_33B97E11] ON [dbo].[tblSAPDespatchItem]( [decValue])
CREATE STATISTICS [_WA_Sys_00000011_33B97E11] ON [dbo].[tblSAPDespatchItem]( [sintOrderSeqNo])

then running the following command:

dbcc show_statistics ('tblSAPDespatchItem','_WA_Sys_00000010_33B97E11')

I get the following picture:

enter image description here

from the picture:


This gives me:

select 21996130 + 19777000
-- 41,773,130

Basically nearly 42 million rows I don't need to deal with

and when comparing to the actual values in the table, they approximately match:

sp_count 'tblSAPDespatchItem'
-- 175,918,697

select count(*)
from tblSAPDespatchItem with (nolock)
where decvalue <= 0

select count(*)
from tblSAPDespatchItem with (nolock)
where decvalue <= 0
or decvalue is null 
-- 43,238,039
  • 1
    What about the statistics for lngDespatchItemStatusID? That could change everything! – mendosi Nov 17 '16 at 12:28
  • I am double checking my script that generated the statistics - why I don't see any stats on lngDespatchItemStatusID – Marcello Miorelli Nov 17 '16 at 12:54

This is probably not the sort of case where filtered indexes really shine - typically that is when the index represents only a small portion of rows in the table (like, a quarter or less) and in this case the filter predicate matches about three quarters of the table.

That's not to say that you wouldn't get gains here, but it might be a bit of a tricky research project to get good designs. (Then again, indexing is always like that.)

What about the current indexes on the table. Are they being used at the moment? Are they only being used in queries that have the predicate that you mentioned? If so, then it might be an easy win to convert some of your existing indexes to have a filter condition (instead of adding a new, filtered index).

You probably already knew this, but filtered indexes won't be used by where the predicate that matches the filter uses a variable or parameter. https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.