Smit Shah

Blog » SQL Delete Performance Issue

SQL Delete Performance Issue

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?
Back to all posts
                                                                                         
This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet/ BOL /Other sites that are authorities in their field. Me or employer do not endorse any tools, applications, books, or concepts mentioned on the site. I have documented my personal experience on this site. The information on this site may not be up to date or accurate at times, if you are not sure or have a question, please contact me to verify information.