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?
Thanks.
