Improve slow query performance on MS SQL SERVER

Question

The indexes are incorrect?

I’m having trouble optimizing a consultation. I had done another question for a simpler case that solved, but for this case I’m not getting a better result. The following query is taking 19 seconds:

select top 12
c.crec_codigo_pk, -- bigint
c.crec_dataInclusao, -- datetime
c.crec_dataVencimento, -- datetime
c.crec_dataPagamento, -- datetime
c.crec_restanteFinal, -- decimal
c.crec_valorPrincipal, -- decimal
c.crec_multaRestante, -- decimal
c.crec_jurosRestante, -- decimal
c.crec_acrescimoRestante, -- decimal
c.crec_desconto -- decimal
from tbContaReceber c
-- crec_isMovimentoConta  é bit
where crec_isMovimentoConta = 0 and crec_dataExclusao is null and
      crec_dataVencimento >= '2015-01-01' and crec_dataVencimento <= '2016-01-01' and
      crec_restanteFinal = 0 and pes_codigo_fk = 834

This query will search for accounts receivable for a period, filtering those that have not been deleted, as well as those that are "account movement", that have no remaining value, filtering the accounts of a specific customer/supplier.

Index:

CREATE NONCLUSTERED INDEX [ix_consulta_1] ON [dbo].[tbContaReceber]
(
    [crec_isMovimentoConta] ASC,
    [crec_dataExclusao] ASC,
    [crec_dataVencimento] ASC,
    [crec_restanteFinal] ASC,
    [pes_codigo_fk] ASC,
    [fpag_codigo_fk] ASC,
    [crec_documento] ASC
)
INCLUDE (   
    [crec_codigo_pk],
    [crec_dataInclusao],
    [crec_dataPagamento],
    [crec_valorPrincipal],
    [crec_multaRestante],
    [crec_jurosRestante],
    [crec_acrescimoRestante],
    [crec_desconto]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Comments that may be useful:

• there are a little over 5 million records in the table
• this table has more fields (about 50)
• most crec_dataExclusion records will have null value - in this test base 100%
• in this test 100% of the records of crec_isMovimentoConta are value "0". In production will wax 50%.
• the field pes_codigo_fk has about 1894 distinct values distributed to 5 million records.
• may still have two more query criteria, but I haven’t even put them on Where yet and it’s taking 19 seconds
• this delay occurs in the first query. However, if you restart the server, or run DBCC DROPCLEANBUFFERS will take the next query.

I really do not know if it is failure of structuring of this bank, fault of repeated data, or failure in the same index.

Answer 1

Try to change the clause where for the example below. Check if it improves something:

where 
pes_codigo_fk = 834
and crec_dataVencimento BETWEEN '2015-01-01' and '2016-01-01' 
and crec_dataExclusao is null 
and crec_isMovimentoConta = 0 
and crec_restanteFinal = 0 

Also, create a new item with the fields used in the above clause in the order they are in.