I have a table with a billion records. It has these columns:
Id bigint, MobileNumber varchar(100), Date datetime, Message nvarchar(400)
And it has non-clustered indexes on
I want to find what a
MobileNumber has sent us in a specified period. Thus I run this query:
select Message from ReceivedMessages where [Date] > 'from-time' and [Date] < 'to-time' and MobileNumber = 'number-to-filter'
And this query works lightening-speed fast for past 2 years. But when I change the
from-time part to a closer date, it freezes out and takes more than 2 minutes to complete. In other words, based on different inputs, it behaves differently, sometimes even hanging out and not returning for more than 10 minutes.
I expected a consistent behavior. What do I miss about indexing? What can cause this inconsistent performance?
Update: I changed names of columns and table, so I can't attach execution plan as a picture. But here's the issue. Thanks for guiding me.
when I change value of date parameter, SQL changes index seek from
IX_Date. I never thought that SQL creates execution plan based on the value of parameters. How that could be?