4 some more details - and speculation
source | link

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data). This would cover all similar queries. If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data) can be used by different queries, so I wouldn't remove it.

    Code to add the index:

    CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
      ON dbo.tbMovimentoConta
      (con_codigo_fk, mcon_data) ;
    
  • a filtered index on (mcon_data) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
    The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?


Another issue - that the index isn't trying to address - is that while the time needed for the query with the WHERE condition might be explained by a bad plan and lack of appropriate index, the time for the 1st query, the whole table count (9 seconds) sounds a bit too much. (and the 1 second for the query after the index added is high, too).

I would be speculating without further details as the explanation might be from numerous different reasons (general/memory SQL Server settings, high load on the server, wide clustered index, etc) so I suggest you either add more details on the question or post a new question (with the CREATE TABLE script if the issue is only for queries involving this table).

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data). This would cover all similar queries. If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data) can be used by different queries, so I wouldn't remove it.

    Code to add the index:

    CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
      ON dbo.tbMovimentoConta
      (con_codigo_fk, mcon_data) ;
    
  • a filtered index on (mcon_data) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
    The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data). This would cover all similar queries. If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data) can be used by different queries, so I wouldn't remove it.

    Code to add the index:

    CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
      ON dbo.tbMovimentoConta
      (con_codigo_fk, mcon_data) ;
    
  • a filtered index on (mcon_data) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
    The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?


Another issue - that the index isn't trying to address - is that while the time needed for the query with the WHERE condition might be explained by a bad plan and lack of appropriate index, the time for the 1st query, the whole table count (9 seconds) sounds a bit too much. (and the 1 second for the query after the index added is high, too).

I would be speculating without further details as the explanation might be from numerous different reasons (general/memory SQL Server settings, high load on the server, wide clustered index, etc) so I suggest you either add more details on the question or post a new question (with the CREATE TABLE script if the issue is only for queries involving this table).

3 added 90 characters in body
source | link

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data between). This would cover all similar queries.
      If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data between) can be used by different queries, so I wouldn't remove it.

    Code to add the index:

    CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
      ON dbo.tbMovimentoConta
      (con_codigo_fk, mcon_data) ;
    
  • a filtered index on (mcon_data between) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
    The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data between). This would cover all similar queries.
      If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data between) can be used by different queries, so I wouldn't remove it.

  • a filtered index on (mcon_data between) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data). This would cover all similar queries. If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data) can be used by different queries, so I wouldn't remove it.

    Code to add the index:

    CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
      ON dbo.tbMovimentoConta
      (con_codigo_fk, mcon_data) ;
    
  • a filtered index on (mcon_data) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
    The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

2 added 386 characters in body
source | link

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data between). This would cover all similar queries.
    If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data between) can be used by different queries, so I wouldn't remove it.

  • a filtered index on (mcon_data between) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data between). This would cover all similar queries.

  • a filtered index on (mcon_data between) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

There are 2 possibilities for a covering index of the query:

  • a composite index on (con_codigo_fk, mcon_data between). This would cover all similar queries.
    If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data between) can be used by different queries, so I wouldn't remove it.

  • a filtered index on (mcon_data between) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*) 
from tbMovimentoConta 
where con_codigo_fk = 1 
  and mcon_data >= '2015-01-05' 
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

1
source | link