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 MobileNumber
and Date
fields.
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_MobileNumber
to IX_Date
. I never thought that SQL creates execution plan based on the value of parameters. How that could be?
(MobileNumber, Date)
would be better than the single index on(MobileNumber)
or the single index on(Date)
. The difference in performance may be explained by the plan changing from using one (single index) to another. Provide more details, ie. execution plans for both, slow and fast, cases and the version of SQL Server.. – ypercubeᵀᴹ Jun 16 '16 at 10:22MobileNumber
predicate, it's like 600 rows. Without it, more than 100 million rows. I continued Google search, and found a concept called parameter sniffing. I think that's our case. – Saeed Neamati Jun 18 '16 at 12:45