Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Добрый день!
Имеется такая табличка:
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 часов. Производительность падает.
Как исправить, чем лечить?
11 фев 13, 09:16    [13906729]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Размер таблицы доходит до 12 gb количество записей 1 млн
11 фев 13, 09:19    [13906740]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
khourshed,

А есть какая-нибудь причина, почему на таблице отсутствует кластерный индекс? Дефрагментация куч - довольно специфическое действо...
11 фев 13, 09:22    [13906749]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Ennor Tiegael,

Причин нет. Однако делать кластерный индекс на uniqueidentifier не рискнул. Хотя может быть заблуждение.

очень активно юзается IX_TaskCheckSum
11 фев 13, 09:26    [13906774]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
khourshed
Ennor Tiegael,

Причин нет. Однако делать кластерный индекс на uniqueidentifier не рискнул. Хотя может быть заблуждение.

очень активно юзается IX_TaskCheckSum


IX_CheckSum, пардон
11 фев 13, 09:31    [13906791]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
khourshed,

А и не надо делать его по uniqueidentifier. Особенно, если он генерится не в самой БД. Если система ваша, добавьте столбец с identity и навесьте на него уникальный кластерный индекс. При ваших объемах вполне сойдет int, но если боитесь переполнения, то можно и bigint.

Если система стороннего разработчика, то лучше переадресовать вопрос по поводу отсутствия кластерника им. Ну а потом уже добавлять int identity :)
11 фев 13, 09:49    [13906857]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34703
khourshed
Фрагментация индексов доходит до 99% в течение 2-3 часов. Производительность падает.
Как исправить, чем лечить?


А расскажи, что же такое эта фрагментация индекса и как ты её мериишь ?
И как, по твоему, оно влияет на производительность ?

К сказанному по поводу отсутствия кластерного индекса присоединяюсь -- странно выглядит в современном MSSQL, но только у тебя вроде бы индексные поля не меняются, ROW ID будет меняться только при миграции записей со страницы на страницу,
что по идее не должно быть часто, все изменяемые поля -- постоянной длины, кроме поля XML, которое по идее должно хранится off the data page, хотя не знаю по MSSQL-ю точно.

В общем, мне кажется, тут не фрагментация виновата, а просто ты не понимаешь, что тормозит.
Какие конкретно операции вызывают подозрения на плохую производительность ?

По фрагментации вот есть статья, вполне вменяемая,

http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx
11 фев 13, 11:15    [13907294]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
MasterZiv,
Под фрагментацией я понимаю то что SQL Server Management Studio показывает под заголовком total fragmentation, в терминологии статьи, которую вы мне прислали это скорее всего external fragmentation.
Если сделать Rebuild индекса, производительность увеличивается ощутимо. Но потом опять затухает по мере нарастания фрагментации.

Попробую добавить identity и посмотреть что будет.
11 фев 13, 14:39    [13908985]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а зачем вы их с ФФ = 80 то держите? он у вас и так фрагментируется, так вы его еще и изначально полупустым держите
11 фев 13, 18:18    [13910446]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
khourshed,

А как вы себе представляете существование индекса по полю типа [uniqueidentifier] без мгновенной дефрагметации?
Оно же не монотонно прирастающее, такие поля в индексах всегда были злом, особенно в PK.
11 фев 13, 18:46    [13910595]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
WarAnt,

Там NewSequentialId() генерится.
11 фев 13, 19:25    [13910717]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Crimean,

Данные часто меняются я уже писал об этом.

Ситуация примерно описана здесь http://www.mssqltips.com/sqlservertip/1940/understanding-sql-server-index-fill-factor-setting/
11 фев 13, 19:30    [13910731]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Crimean
Member

Откуда:
Сообщений: 13147
khourshed
Данные часто меняются я уже писал об этом.


и чего, работа со стартовым ФФ 80% через условный час работы дает реальную фрагментацию меньше, чем старт от ФФ 100%? а то указанная статья просто рассказывает про ФФ, не более того
на самом деле через "достаточно долгое" время работы у вас фактический ФФ всегда (!) будет определяться только потоком данных/модификаций. и вам важно, чтобы в промежутках между перестроениями индекса реальный ФФ был как можно выше
ИНОГДА (но далеко не всегда) действительно указание ФФ при перестроении индекса отличным от 100% позволяет получать реальный высокий ФФ до следующего перестроения. но я таких ситуаций на практике не видел
хотя, если у вас эта табличка почти полностью чистится и заливается по-новой (многократно между перестроениями индексов) - возможно это как раз ваш случай, но в этом случае вряд ли можно ожидать стабильного результата в смысле итогового заполнения страниц
вполне вероятно, что у вас как раз тот самый случай когда НАДО указывать индекс хинтами в запросе. но, конечно, падения производительности от понижения % заполнения данных это не уберет, просто сократит число кривых планов выполнения
p.s.
а статейка про NEWSEQUENTIALID вам никак не поможет?
11 фев 13, 20:24    [13910896]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Crimean,

1. У меня везде генерится NewSequentialId, но он как default value не прописан. Как это реализовано обсуждать не вижу смысла, так как хп на добавление записи уже получает id от вызывающей процедуры.
вот 10 последних записей, например:
3A8F5B5F-7074-E211-86F5-000F20D0F73F
288F5B5F-7074-E211-86F5-000F20D0F73F
474C6359-7074-E211-86F5-000F20D0F73F
3C4C6359-7074-E211-86F5-000F20D0F73F
3B4C6359-7074-E211-86F5-000F20D0F73F
2F4C6359-7074-E211-86F5-000F20D0F73F
204C6359-7074-E211-86F5-000F20D0F73F
144C6359-7074-E211-86F5-000F20D0F73F
ED4B6359-7074-E211-86F5-000F20D0F73F
CEBA5C53-7074-E211-86F5-000F20D0F73F 

Как видите значения последовательны.
2. Я не пытаюсь вступить с вами в теоретическую или практическую дискуссию, я сюда вообще -то за советом пришел. Если я пришел за советом, значит я предполагаю, что здесь есть люди, которые разбираются в этих вопросах значительно лучше меня и смогут сократить для меня поиск подходящего решения.
3. Система которую я эксплуатирую самописная и наверняка кишит разного рода ошибками, которые я и пытаюсь выловить и понять. Или вы не делали ни разу ошибок в своей практике? Если вам есть что сказать и дать дельный практический совет то я вам буду премного благодарен. Сидеть и перекладывать какашки (даже мои) из одной кучки в другую и тем самым самоутверждаться, думаю не самый лучший способ ведения дискуссии (как минимум менее эффективный) [извините если я не прав в своем восприятии ситуации по отношению к вам].

Кластерный индекс сначала решил создать на Primary Key (все значения этого поля получены функцией NewSequentialId()). Ситуацию не поменяло. Основной рабочий индекс IX_CheckSum через 2 часа опять 98% фрагментации. Попробую с полем типа int как советовали. может изменит ситуацию.
11 фев 13, 21:31    [13911064]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
LenaV
Member

Откуда: USA
Сообщений: 6796
khourshed

Кластерный индекс сначала решил создать на Primary Key (все значения этого поля получены функцией NewSequentialId()). Ситуацию не поменяло. Основной рабочий индекс IX_CheckSum через 2 часа опять 98% фрагментации. Попробую с полем типа int как советовали. может изменит ситуацию.

что значит "Основной рабочий индекс IX_CheckSum" ?
он сканируется или по нему выполняется операция seek?
посмотрите планы выполнения при разной степени фрагментации.
они могут меняться
если seek то ФФ можно безболезненно уменьшить, попробуйте 70%

посмотрите sys.dm_db_index_operational_stats этого индекса вот как нибудь так.
SELECT b.fill_factor, b.name, OBJECT_NAME (b.object_id) as tablename
  ,leaf_insert_count, leaf_delete_count,leaf_update_count
  ,a.leaf_allocation_count, a.nonleaf_allocation_count
  ,range_scan_count, singleton_lookup_count
  ,row_lock_count, row_lock_wait_count, row_lock_wait_in_ms 
  FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) a
    JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
    where name in ('IX_CheckSum')
    order by a.page_io_latch_wait_in_ms desc


если большое колличество leaf_allocation_count
то ФФ 70% уменьшит количество split pages,
и соответственно улучшит работу I/O .

что показывает dbcc showcontig до и после фрагментации?
особенно интересен Avg. Page Density после фрагментации
11 фев 13, 23:50    [13911379]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
khourshed
Кластерный индекс сначала решил создать на Primary Key (все значения этого поля получены функцией NewSequentialId()). Ситуацию не поменяло. Основной рабочий индекс IX_CheckSum через 2 часа опять 98% фрагментации. Попробую с полем типа int как советовали. может изменит ситуацию.


Сомневаюсь что это изменит картину. Вы уже делали кластер на NewSequentialId() - будет тоже самое, только некластерные индексы будут немного меньшего размера, но картина останется той-же.

Насчет фрагментации не кластерных индексов, если значения ключей меняются, добавляются новые, удаляются старые - то фрагментация неизбежна, ее нельзя никак предотвратить. Филфактор да вариант, но он вас не спасает, видимо данные очень агресивно меняются.

Может быть лушче посмотрим на планы запросов когда вы дефрагметировали все индексы и система работает нормально.
И второй план когда индексы фрагментированы, через 2 часа, когда "Производительность падает."

Давайте начнем с одного запроса наиболее важного для вашей системы.
11 фев 13, 23:50    [13911381]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Создал поле ID bigint c Identity Icrement. Создал по нему кластерный индекс. Прошел 60 мин. Вроде фрагментация перестала расти. Производительность системы нормальная.

результат select-а который просил(а) LenaV:

fill_factornametablenameleaf_insert_countleaf_delete_countleaf_update_countleaf_allocation_countnonleaf_allocation_countrange_scan_countsingleton_lookup_countrow_lock_countrow_lock_wait_countrow_lock_wait_in_ms
80IX_CheckSumcms_tblViewCache1936000601207038802781


Если планы запросов актуальный могу скинуть.
12 фев 13, 00:49    [13911546]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Блин почему нельзя редактировать записи. кучу опечаток наделал
12 фев 13, 00:50    [13911549]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
LenaV
Member

Откуда: USA
Сообщений: 6796
khourshed,
поздравляю с нормальной производительностью :)

у вас нет split pages при ФФ 80%
по крайней мере сейчас не видно.
понаблюдайте за ней еще и потом увеличивайте до 100%
и скенов не видно.
похоже фрагментация этого индекса не имеет значения для вашей проблемы.
12 фев 13, 01:07    [13911581]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
khourshed
Фрагментация индексов доходит до 99% в течение 2-3 часов. Производительность падает.
Как исправить, чем лечить?

А производительность чего падает-то?
12 фев 13, 01:10    [13911586]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
Ray D,

падает производительность работы системы. Если еще конкретней - время отклика страниц на сайте. стандартно 0,5 сек. При упавшей производительности от 3 до 20 сек. Хотя операции только на чтение из вышеуказанной таблицы в режиме NOLOCK
12 фев 13, 01:41    [13911606]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Ну можете все-таки показать запрос и приложить план в xml?
12 фев 13, 01:51    [13911613]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
LenaV
Member

Откуда: USA
Сообщений: 6796
а CPU у вас поднимается, когда производительность падает?
и сколько?
12 фев 13, 01:51    [13911614]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
khourshed
Member

Откуда:
Сообщений: 93
LenaV,

Два часа полет нормальный.
План запроса:
declare
			@pViewCacheId uniqueidentifier,
			@pXmlCache xml,
			@pHtmlCache nvarchar(max),
			@pInstanceStateid uniqueidentifier,
			@pWebProjectId uniqueidentifier='084608C8-0655-E211-A762-000F20D0F73F',
			@pCheckSum int = -2112594593
		 select 
			@pViewCacheId=VIEW_CACHE_ID,
			@pXmlCache =XML_CACHE,
			@pHtmlCache=HTML_CACHE,
			@pInstanceStateId=ISNULL(INSTANCE_STATE_ID,'B01ADD9F-F2DA-4ED8-AAE5-373A8B3EB4B7') --default value =created
		 from
			ViewCacheDb.dbo.cms_tblViewCache with (NOLOCK)
		 where
			WEBPROJECT_COMPONENT_ID=@pWebProjectId
			and 
			CHECK_SUM=@pCheckSum


|--Compute Scalar(DEFINE:([Expr1004]=isnull([ViewCacheDb].[dbo].[cms_tblViewCache].[INSTANCE_STATE_ID],{guid'B01ADD9F-F2DA-4ED8-AAE5-373A8B3EB4B7'})))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [ViewCacheDb].[dbo].[cms_tblViewCache].[ID]))
|--Index Seek(OBJECT:([ViewCacheDb].[dbo].[cms_tblViewCache].[IX_CheckSum]), SEEK:([ViewCacheDb].[dbo].[cms_tblViewCache].[WEBPROJECT_COMPONENT_ID]=[@pWebProjectId] AND [ViewCacheDb].[dbo].[cms_tblViewCache].[CHECK_SUM]=[@pCheckSum]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([ViewCacheDb].[dbo].[cms_tblViewCache].[IX_cms_tblViewCache_Id]), SEEK:([ViewCacheDb].[dbo].[cms_tblViewCache].[ID]=[ViewCacheDb].[dbo].[cms_tblViewCache].[ID] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

По индекс IX_CheckSum вообще Total Fragmentation не поднимается выше 1%

Теперь хочется понять в чем состоит чудо и почему его не произошло когда я сделал clustered PK с NewSequentialId()
12 фев 13, 01:52    [13911616]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментация индекса  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Что-то видится мне, у вас вместо seek+lookup (как сейчас), получался скан из-за уехавшей статистики. Можно хинтами текущий план зажать.
12 фев 13, 01:57    [13911620]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить