6

Could someone please guide me to a better understanding of index seeks with multiple inequality predicates. Consider the following table:

CREATE TABLE [dbo].[table_1](
[DBINVHDID] [int] IDENTITY(1,1) NOT NULL,
[TYPE] [nvarchar](1) NULL,
[INV_NO] [nvarchar](13) NULL,
[ACCOUNT] [nvarchar](10) NULL,
[NAME] [nvarchar](40) NULL,
[ADDR1] [nvarchar](25) NULL,
[ADDR2] [nvarchar](25) NULL,
[ADDR3] [nvarchar](25) NULL,
[CONTACT] [nvarchar](25) NULL,
[GST_NO] [nvarchar](15) NULL,
[CR_TERMS] [nvarchar](10) NULL,
[OREF] [nvarchar](20) NULL,
[YREF] [nvarchar](20) NULL,
[SALESREP] [nvarchar](20) NULL,
[DISCOUNT] [decimal](18, 2) NULL,
[TRANSPORT] [decimal](18, 2) NULL,
[INS] [decimal](18, 2) NULL,
[GST] [decimal](18, 2) NULL,
[TOTAL] [decimal](18, 2) NULL,
[DATE] [smalldatetime] NULL,
[LINE] [smallint] NULL,
[PSLIP] [nvarchar](10) NULL,
[VAT] [nvarchar](1) NULL,
[STORE] [nvarchar](3) NULL,
[STKSTR] [nvarchar](3) NULL,
[POST] [smalldatetime] NULL,
[PAID] [decimal](18, 2) NULL,
[WEIGHT] [decimal](18, 4) NULL,
[INSURE] [nvarchar](15) NULL,
[HAND] [decimal](18, 2) NULL,
[CODC] [decimal](18, 2) NULL,
[TOPAY] [decimal](18, 2) NULL,
[IND1] [nvarchar](1) NULL,
[IND2] [nvarchar](1) NULL,
[IND3] [nvarchar](1) NULL,
[COST] [decimal](18, 2) NULL,
[REPC] [nvarchar](6) NULL,
[DISPATCH] [nvarchar](1) NULL,
[TILLNO] [nvarchar](2) NULL,
[USER] [nvarchar](6) NULL,
[RCOMM] [decimal](18, 2) NULL,
[DISP1] [nvarchar](1) NULL,
[DISP2] [nvarchar](7) NULL,
[PRNSEQ] [int] NULL,
[TIME] [nvarchar](20) NULL,
[ROUND] [decimal](18, 2) NULL,
[MESSAGE1] [nvarchar](50) NULL,
[MESSAGE2] [nvarchar](50) NULL,
[MESSAGE3] [nvarchar](50) NULL,
[PRINT] [nvarchar](1) NULL,
[PDATE] [smalldatetime] NULL,
[FILENO] [nvarchar](20) NULL,
[sched6] [nvarchar](1) NULL,
[BUYACC] [nvarchar](10) NULL,
[DBCLW] [nvarchar](20) NULL,
[PRINTCODE] [nvarchar](3) NULL,
[INV_HOLD] [nvarchar](1) NULL,
[CURRATE] [decimal](18, 4) NULL,
[CURCODE] [nvarchar](3) NULL,
[DEPOS] [smalldatetime] NULL,
[ROUTE] [nvarchar](5) NULL,
[DEL_METHOD] [nvarchar](1) NULL,
[DROPNO] [int] NULL,
[SEATNO] [int] NULL,
[DDATE] [smalldatetime] NULL,
[COMM] [nvarchar](20) NULL,
[ROT] [nvarchar](1) NULL,
[KNAPP] [nvarchar](1) NULL,
[OSEC] [nvarchar](1) NULL,
[OSECT] [nvarchar](3) NULL,
[UPDEXT] [nvarchar](1) NULL,
[EORDREFNO] [nvarchar](32) NULL,
[FSI] [nvarchar](1) NULL,
[MESSAGE_LINES] [nvarchar](500) NULL) 

This is the index:

CREATE INDEX [Index_1] ON table_1 
([PRINT], [DEL_METHOD], [TYPE], [INV_NO], [DBINVHDID], [OSECT], [OSEC]) 
INCLUDE 
( [BUYACC], [CR_TERMS], [DISP1], [NAME], [ROUTE], [SEATNO], [STORE]) 

This is the query:

SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE] = '3' OR [TYPE]='5') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1' 
ORDER BY [INV_NO] ASC

The query plan:

enter image description here

The issue I have is that there is a SEEK predicate as well as a PREDICATE on the inequalities:

enter image description here

How can I recreate this index so that there is no residual predicate?

Initially, the index was created with the OSECT & OSEC columns further to the left in the definition which caused residual IO warnings and lots of reads - I then moved those columns to after the equality columns which cleared up the IO issues.

What I am trying to understand is how I can get this index to completely satisfy the query without a residual predicate?

Can the query be re-written?

Also, there is an excessive memory grant warning - SQL is allocating too much memory for this query - STATS are up to date with FULLSCAN

Is the excessive grant related to the residual predicate?

0

2 Answers 2

8

How can I recreate this index so that there is no residual predicate?

It seems likely you would require the identity to be unique. This needs to be enforced:

CREATE UNIQUE CLUSTERED INDEX CX ON dbo.table_1 (DBINVHDID);

Then create a filtered index:

CREATE INDEX [Index_1] 
ON table_1 ([PRINT], [DEL_METHOD], [TYPE], [INV_NO]) 
INCLUDE (
    [DBINVHDID], [OSECT], [OSEC], [STORE], [DISP1], [SEATNO], [BUYACC],
    [NAME], [ROUTE], [CR_TERMS])
WHERE 
    OSECT <> N'KNP'
    AND OSEC <> N'1';

Express the query as:

SELECT 
    U.[DBINVHDID],
    U.[STORE],
    U.[INV_NO],
    U.[DISP1],
    U.[SEATNO], 
    U.[BUYACC],
    U.[NAME],
    U.[ROUTE],
    U.[CR_TERMS],
    U.[DEL_METHOD] 
FROM 
(
    SELECT T1.*
    FROM dbo.table_1 AS T1
    WHERE
        T1.[PRINT] = N'N'
        AND T1.DEL_METHOD = N'C'
        AND T1.[TYPE] = N'3'

    UNION ALL

    SELECT T2.*
    FROM dbo.table_1 AS T2
    WHERE
        T2.[PRINT] = N'N'
        AND T2.DEL_METHOD = N'C'
        AND T2.[TYPE] = N'5'
) AS U
WHERE
    U.OSECT <> N'KNP'
    AND U.OSEC <> N'1'
ORDER BY
    U.INV_NO ASC;

This will give an execution plan with no residual predicate and no sort:

Plan without a residual or sort

You can omit the filtering portion of the index if those conditions might vary in different queries. The two inequality predicates on OSECT and OSEC will then be applied as residual predicates.

Is the excessive grant related to the residual predicate?

The memory grant is for the sort. It is estimated as being proportional to the number and size of rows requiring sorting. The predicates (seekable and residual) do affect cardinality estimation. It also seems your table is empty. SQL Server often emits a meaningless 'excessive memory' warning in that case, even though the memory reservation is tiny.

Without a sort, there is no memory requirement.


You should not create tables in the master database.

Your single table is a heap of unorganized data. A relational design with multiple tables to represent the different entities would likely be much easier to query. You should consider a redesign.

Related reading:

1
  • Thanks you very much for the thorough response. By my own foolishness, I left out quite a bit of the DDL - There is a clustered index on DBINVHDID. In fact, the entire query has a join to another table - My aim was to try and figure out how to do away with the residual predicate. I created the table in master to get a subset of the data for testing this. It's not empty though - Has 100k rows in it - Original table has 20mil+ rows The filtered index works brilliantly - The filtering conditions in the query never change so that's that. Oct 20, 2022 at 5:00
1

Your query has sargable conditions such as <>, OR operators. These operators have a bad impact on query cost. To decrease query cost, You can remove the 'OR' condition by dividing your query into 2 queries like this:

SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE] = '3') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1'
UNION 
SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE]='5') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1' 
ORDER BY [INV_NO] ASC 

To better understand please view this link: https://medium.com/codex/sargable-predicates-and-null-values-in-sql-server-c43ec3d8b108

1
  • Do predicates using the <> or or operators have a negative impact on query cost? My impression is that they rarely provide a positive impact. Your link correctly points out that transforming a non-sargable predicate to a sargable predicate will help with index usage, but I can't see anything about a negative impact from the <> or or operators?
    – Ben
    Oct 20, 2022 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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