I thought this was a partition problem (may be related, but is directly reproducable with UPDATE STATISTICS). I have a partitioned table (by day for 31 days, 32 partitions)with over 26,000,000 rows in it. The rows are relatively evenly distributed across the partitions. The table has a clustered nonunique index based on reporttime and a nonclustered nonunique index based on callsignssid and reporttime (see below, pasted from the SQL Server Management Studio).
CREATE TABLE [dbo].[APRSWx](
[CallsignSSID] [varchar](9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ReportTime] [datetime] NOT NULL,
[WindDir] [smallint] NULL,
[WindSpeed] [smallint] NULL,
[GustSpeed] [smallint] NULL,
[Temperature] [smallint] NULL,
[HourRain] [decimal](4, 2) NULL,
[DayRain] [decimal](6, 2) NULL,
[MidnightRain] [decimal](6, 2) NULL,
[Humidity] [tinyint] NULL,
[BarPressure] [decimal](5, 1) NULL
) ON [onemonth]([ReportTime])
CREATE CLUSTERED INDEX [IX_APRSWx_RT] ON [dbo].[APRSWx]
(
[ReportTime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [onemonth]([ReportTime])
CREATE NONCLUSTERED INDEX [IX_APRSWx] ON [dbo].[APRSWx]
(
[CallsignSSID] ASC,
[ReportTime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [onemonth]([ReportTime])
The following script works correctly and generates an execution plan that first seeks on IX_APRSWx and then on IX_APRSWx_RT:
SELECT TOP 1 *, DATEDIFF(s,[ReportTime],GETUTCDATE()) AS lastheard
FROM APRSWx
WHERE CallsignSSID='ANYTEXT'
ORDER BY CallsignSSID, ReportTime DESC
When an update statistics on the nonclustered index occurs, the optimizer ignores the nonclustered index even though the select is based solely on callsignssid causing the execution plan to do a scan of the IX_APRSWx_RT index (the entire database) for 'ANYTEXT' Rebuilding IX_APRSWx fixes the problem. I have turned off recomputing of statistics on the index in hopes of preventing Automatic Update Statistics from causing the problem to recur.
There is definitely something wrong when automatic/manual updates of the index statistics breaks the optimizer yet rebuilding the index (which also causes the statistics to be updated/recreated) fixes the problem. There are about 10,000 unique CallsignSSIDs in the table, each with multiple entries in across the entire month. 'ANYTEXT' is not in the table. This SELECT is basically saying 'give me the last report for 'ANYTEXT'. If none, return zero rows.'
Any ideas I have heard similar reports from other SQL 2005 users on non-partitioned tables. I am running SQL 2005 SP1
Pete Loveall
AME Corp.
UPDATE STATISTICS Breaks Optimizer
G Pearlman
This work-around (turning off Automatic Update Statistics and never doing UPDATE STATISTICS on IX_APRSWx, the nonclustered, nonunique index) works. However, it is ugly because the statistics will rapidly become out of date (about 1 million records added via INSERT and another 1 million subtracted via PARTITION SWITCH per day). This bug appears to primarily affect large tables and indexes. I don't know if it only affects tables with a clustered nonunique index and a nonclustered nonunique index, but it is definitely a bug in either the query optimizer or in UPDATE STATISTICS.
Pete Loveall
AME Corp.