Help with simple query - why isn't an index being used?

Question

I have the following query:

SELECT MAX([LastModifiedTime]) FROM Workflow

There are approximately 400M rows in the Workflow table. There is an index on the LastModifiedTime column as follows:

CREATE NONCLUSTERED INDEX [IX_Workflow_LastModifiedTime] ON [dbo].[Workflow] 
(
 [LastModifiedTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100)

The above query takes 1.5 minutes to execute. Why wouldn't SQL Server use the above index and simply retrieve the last row in the index to get the maximum value?

BTW, the query plan for this query shows an index scan being done on the above index.

Thanks.

Answer 1

Can you tell from the execution plan if it is using the index or not? If not, when was the table/index last analyzed? If it's never been analyzed, maybe SQL server still thinks there's only 100 rows in it and it would just be faster to do a table scan.

Answer 2

I have a table with a similar date-time field (assuming that is your datatype) and 4M rows - 1% of yours, but the same query came back almost as soon as I clicked 'execute'. My index is almost the same as yours:

CREATE NONCLUSTERED INDEX [IX_PartViewTrack_SearchDate] ON [dbo].[PartViewTrack] 

(
[SearchDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,     
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

The execution plan shows an index scan.

Just for grins - drop the index and see how long it takes?

Answer 3

SELECT MAX([LastModifiedTime]) FROM Workflow with(nolock)

Is this any faster?

Answer 4

A black-and-white problem, without (to me) an obvious answer. Here are some pretty dumb ideas, just to clear the air.

• Is it a table? If a view, underlying structure might get in the way. (I forget, can you build a nonclustered index on a materialized view?)

• Is there contention? If other users have long-standing locks on the table, that might slow it down. Also, extremely frequent updates might throw it off.

• The query plan shows an index scan being run. Is the index reference this index? (Are there other indexes? A clustered indexes? Probably not an issue, but scrabbling for ideas here.)

• Do you use schemas? The index is on dbo.Workflow, but the query does not specify "dbo". (Like I said, scrabbling for ideas here.)

All of those sound pretty lame, but without hands-on access these kinds of problems can be pretty tough to figure out.

Answer 5

Here is a longshot: I have found that Oracle sometimes requires a field to not allow nulls before it will use an index on it. Are there any similar restrictions in SQL Server?

Answer 6

Mysterious are the ways of the query optimizer...

If is possible, I'd recommend you change the query like this:

SELECT TOP (1) [LastModifiedTime]
FROM Workflow 
ORDER BY [LastModifiedTime] DESC;

This is semantically identical and the optimizer will no longer consider using the MAX aggregate and scan (which apparently it does right now). It may consider doing a SORT in a worktable, but hopefully the estimated cost of such a plan would be much bigger than the cost of the reverse order seek.

As to why does the optimizer choose what apparently is an obviously bad plan, there are usually many many factors involved and is hard to diagnose just from a SO post. In general, having an ASC index does not always substitute for the lack of a DESC index and your particular column statistics (distribution) may had hit some tipping point inside the query optimizer where it decided to choose the scan+aggregate instead of the reverse scan+top.

Answer 7

Analyse your index first using the command

DBCC SHOW_STATISTICS (table_name, index_name)

Check if your index is covering the whole table. If not, try updating your statistics using

UPDATE STATISTICS table_name