Help with a simple query - why not using an index?

Question

I have the following query:

SELECT MAX([LastModifiedTime]) FROM Workflow

The Workflow table has approximately 400M rows. The LastModifiedTime column has an index:

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 request takes 1.5 minutes. Why didn't SQL Server use the index above and just retrieve the last row in the index to get the maximum value?

BTW, the query plan for this query is displayed index scanat the index above.

Thank.

Answer 1

Mysterious ways to optimize queries ...

If possible, I would recommend that you modify the query as follows:

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

, MAX (, -, ). , , , , .

, , , -, , , SO. ASC DESC, () , scan + + .

Answer 2

( , ) 4M - 1% , "". , :

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]

.

- , ?

Answer 3

, ? , /? , , SQL- , 100 , .

Answer 4

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

?

Answer 5

- , () . , .

• ? , . ( , ?)

• ? , . , .

• . ? ( ? ? , , .)

• ? dbo.Workflow, "dbo". ( , scrabbling .)

, .

Answer 6

Here's a long snapshot: I found that Oracle sometimes requires a field to not allow null before it uses an index on it. Are there any such limitations in SQL Server?

Answer 7

First analyze your index using the command

DBCC SHOW_STATISTICS (table_name, index_name)

Check if your index spans the entire table. If not, try updating the statistics using

UPDATE STATISTICS table_name