Добрый день!
Имеется такая табличка:
USE [ViewCacheDb]
GO
/****** Object: Table [dbo].[cms_tblViewCache] Script Date: 02/11/2013 09:08:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cms_tblViewCache](
[VIEW_CACHE_ID] [uniqueidentifier] NOT NULL,
[WEBPROJECT_COMPONENT_ID] [uniqueidentifier] NOT NULL,
[CREATE_DATE] [datetime] NOT NULL,
[LAST_UPDATE] [datetime] NOT NULL,
[XML_PARAMS] [xml] NOT NULL,
[XML_CACHE] [xml] NOT NULL,
[HTML_CACHE] [nvarchar](max) NULL,
[CACHE_IS_ACTUAL] [bit] NOT NULL,
[CACHE_LIFE_TIME] [int] NOT NULL,
[XSL_TRANSFORMATION_ID] [uniqueidentifier] NULL,
[CHECK_SUM] [int] NOT NULL,
[GENERATION_TIME] [time](7) NULL,
[ASYNC_UPDATE] [bit] NULL,
[INSTANCE_STATE_ID] [uniqueidentifier] NULL,
[LOCK_DATE] [datetime] NULL,
CONSTRAINT [PK_cms_tblViewCache] PRIMARY KEY NONCLUSTERED
(
[VIEW_CACHE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
А также индексы:
USE [ViewCacheDb]
GO
/****** Object: Index [PK_cms_tblViewCache] Script Date: 02/11/2013 09:09:17 ******/
ALTER TABLE [dbo].[cms_tblViewCache] ADD CONSTRAINT [PK_cms_tblViewCache] PRIMARY KEY NONCLUSTERED
(
[VIEW_CACHE_ID] 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 = 80) ON [PRIMARY]
GO
USE [ViewCacheDb]
GO
/****** Object: Index [IX_InstanceStateId] Script Date: 02/11/2013 09:09:32 ******/
CREATE NONCLUSTERED INDEX [IX_InstanceStateId] ON [dbo].[cms_tblViewCache]
(
[INSTANCE_STATE_ID] 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 = 80) ON [PRIMARY]
GO
USE [ViewCacheDb]
GO
USE [ViewCacheDb]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_CheckSum] ON [dbo].[cms_tblViewCache]
(
[WEBPROJECT_COMPONENT_ID] ASC,
[CHECK_SUM] 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 = 80) ON [PRIMARY]
GO
Табличка cms_tblViewCache заполняется очень быстро и записи в ней часто обновляются по полям:
[LAST_UPDATE] [datetime] NOT NULL,
[XML_CACHE] [xml] NOT NULL,
[GENERATION_TIME] [time](7) NULL,
[LOCK_DATE] [datetime] NULL,
Фрагментация индексов доходит до 99% в течение 2-3 часов. Производительность падает.
Как исправить, чем лечить?