I have a table where I cache data to improve performance.
Over the years, it has ballooned to 10M+ rows.
Below is the structure
CREATE TABLE [dbo].[SystemCache]
(
[CacheId] [UNIQUEIDENTIFIER] NOT NULL,
[CacheKey] [VARCHAR](900) NULL,
[CacheText] [TEXT] NULL,
[TotalCount] [INT] NULL,
[CacheDate] [SMALLDATETIME] NULL,
[Url2Use] [VARCHAR](1000) NULL,
[CacheNumber] [BIGINT] NULL,
[PageSize] [TINYINT] NULL,
CONSTRAINT [PK_SystemCache] PRIMARY KEY NONCLUSTERED ( [CacheId] ASC )WITH
(pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 90) ON [PRIMARY]
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SystemCache] ADD CONSTRAINT [PK_SystemCache] PRIMARY KEY
NONCLUSTERED ( [CacheId] ASC )WITH (pad_index = OFF, statistics_norecompute =
OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF, online = OFF, allow_row_locks =
on, allow_page_locks = on, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_SystemCache_CacheNumber]
ON [dbo].[SystemCache] ( [CacheNumber] 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]
GO
CREATE NONCLUSTERED INDEX [IX_SystemCache_CacheKey]
ON [dbo].[SystemCache] ( [CacheKey] 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 = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SystemCache_CacheDate]
ON [dbo].[SystemCache] ( [CacheDate] ASC )
INCLUDE ( [PageSize]) 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 = 90) ON [PRIMARY]
GO
An attempt to delete older data is significantly slow
DELETE FROM systemcache
WHERE cachedate < '7/15/2011'
Any ideas suggestions?