6

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?

| improve this question | |
  • 7
    An index on (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:22
  • 7
    Possibly the ascending date problem causing it to underestimate the number of rows in the date range and give a plan with lookups. For a table with a billion rows the default statistics update thresholds will be large. – Martin Smith Jun 16 '16 at 10:36
  • What is the estimated amount of rows returned from the seek in the slow plan? How many rows actually match the date condition (ignoring the mobile phone number predicate)? – Martin Smith Jun 18 '16 at 12:22
  • With MobileNumber 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
2

What you have to remember is that SQL Server generates a Query Plan for each query. Each time it generates a plan it takes into account the values you pass into it and the statistics for the table. This includes how many rows it expects to get back based on your parameters etc.

Based on your edit you are getting a different query plan based on your statistics. SQL expects a different number of rows back depending on your date range and makes its decision based on that (and on the statistics for MobileNumber as well of course).

Let's say for example for date range A it expects back 10 rows out of 100,000. The MobileNumber on the other hand will give you back 1,000 rows. SQL is going to use your date index and then scan the results for the correct MobileNumber.

Now for date range B it expects back 10,000 rows and the same 1,000 for the MobileNumber. Now it makes more sense to pull the data from the MobileNumber index first then scan that for the correct date range.

In general you would probably do better with an index on the two combined. Probably MobileNumber, Date. That way SQL can not only pull the 1,000 rows based on the first part it can then quickly get back the date information based on the same index.

| improve this answer | |
2

We have had the similar issue when dealing with big (billions of records) tables. As Martin Smith suggested in comment - you would probably need to periodically rebuild statistic data on that table. This worked for us:

UPDATE STATISTICS <TABLE NAME> WITH FULLSCAN

This solution is kind of brute-force and being too simple but still it may help.

| improve this answer | |
  • 1
    A FULLSCAN on a large table may cause issues (You may want to try WITH SAMPLE XX PERCENT) but keeping statistics up-to-date with regular maintenance is important. – James Anderson Aug 22 '16 at 15:46
0

Depending on how often your statement is executed, you could try adding the OPTION(RECOMPILE) to your SELECT

select Message
from ReceivedMessages
where [Date] > 'from-time'
and [Date] < 'to-time'
and MobileNumber = 'number-to-filter'
OPTION(RECOMPILE)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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