Clustered Index/PK fragmentation

B0rG
Дата: 18.02.2010 00:32:27
запустил
DBCC SHOWCONTIG ( 't_data_dat' ) WITH ALL_INDEXES

получил:


DBCC SHOWCONTIG scanning 'T_DATA_DAT' table...
Table: 'T_DATA_DAT' (1013578649); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 802337
- Extents Scanned..............................: 100425
- Extent Switches..............................: 118800
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 84.42% [100293:118801]
- Logical Scan Fragmentation ..................: 59.07%
- Extent Scan Fragmentation ...................: 0.31%
- Avg. Bytes Free per Page.....................: 744.2
- Avg. Page Density (full).....................: 90.81%

DBCC SHOWCONTIG scanning 'T_DATA_DAT' table...
Table: 'T_DATA_DAT' (1013578649); index ID: 3, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 456423
- Extents Scanned..............................: 57220
- Extent Switches..............................: 87170
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 65.45% [57053:87171]
- Logical Scan Fragmentation ..................: 8.05%
- Extent Scan Fragmentation ...................: 0.48%
- Avg. Bytes Free per Page.....................: 90.4
- Avg. Page Density (full).....................: 98.88%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


запустил
alter table drop constraint primary key
и
alter table ADD CONSTRAINT [PK_T_DATA_DAT] PRIMARY KEY CLUSTERED

запустил showcontig еще раз, получил


DBCC SHOWCONTIG scanning 'T_DATA_DAT' table...
Table: 'T_DATA_DAT' (1013578649); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 789204
- Extents Scanned..............................: 98694
- Extent Switches..............................: 100633
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 98.03% [98651:100634]
- Logical Scan Fragmentation ..................: 41.81%
- Extent Scan Fragmentation ...................: 0.23%
- Avg. Bytes Free per Page.....................: 795.0
- Avg. Page Density (full).....................: 90.18%

DBCC SHOWCONTIG scanning 'T_DATA_DAT' table...
Table: 'T_DATA_DAT' (1013578649); index ID: 3, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 483008
- Extents Scanned..............................: 60377
- Extent Switches..............................: 60376
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [60376:60377]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 0.35%
- Avg. Bytes Free per Page.....................: 795.1
- Avg. Page Density (full).....................: 90.18%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Хотел увидеть Logical Scan Fragmentation ближе к 0.0% как для второго индекса, но получил 41% вместо 59%. Почему?

Cheers
Pete
tpg
Дата: 18.02.2010 06:24:44
Приведите результат select @@version.
Какой филфактор использовался, какой/ие типы столбцов, используемых в индексе?
Приведите DDL таблицы и её индексов.
Glory
Дата: 18.02.2010 10:41:37
- Avg. Bytes Free per Page.....................: 90.4
- Avg. Page Density (full).....................: 98.88%

- Avg. Bytes Free per Page.....................: 795.1
- Avg. Page Density (full).....................: 90.18%

Fillfactor наверное по-умолчанию 10 стоит ?
B0rG
Дата: 18.02.2010 13:55:15
Glory, tpg,

спасибо за ответ.
Значит без скриптов все таки не обойтись:

Оба индеска создавались с филлфактор 90.


Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)


автор

select count(1) = 159698710


CREATE TABLE [dbo].[T_DATA_DAT](
	[DAT_TRV_ID] [decimal](6, 0) NOT NULL,
	[DAT_DEF_ID] [varchar](20) NOT NULL,
	[DAT_DATE] [datetime] NOT NULL,
	[DAT_VALUE] [float] NULL,
 CONSTRAINT [PK_T_DATA_DAT] PRIMARY KEY CLUSTERED 
(
	[DAT_DATE] ASC,
	[DAT_TRV_ID] ASC,
	[DAT_DEF_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = ON,
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [I_DAT_TRV_ID_DEF_ID] ON [dbo].[T_DATA_DAT] 
(
	[DAT_TRV_ID] ASC,
	[DAT_DEF_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 = 90) ON [PRIMARY]
B0rG
Дата: 22.02.2010 16:10:08
anyone?

Cheers
Pete
Glory
Дата: 22.02.2010 16:27:22
B0rG
anyone?

А что то еще неясно/непонятно ?
B0rG
Дата: 23.02.2010 16:38:03
Glory
А что то еще неясно/непонятно ?


Вообще ничего не понятно.

drop recreate index для обычных индексов серьезно уменьшает значение параметра Logical Scan Fragmentation. Тогда как для pk индекса он практически ничего не делает. Если в таком поведении великий смысл, и имеет ли этот параметр Logical Scan Fragmentation какое-нибудь значение для pk индексов?

Ну и что бы два раза не вставать, была книжка от одного из создателей Sql Server Engine, не могли бы подсказать как она называется?

Regards
P.
Glory
Дата: 23.02.2010 19:43:06
B0rG
drop recreate index для обычных индексов серьезно уменьшает значение параметра Logical Scan Fragmentation. Тогда как для pk индекса он практически ничего не делает. Если в таком поведении великий смысл, и имеет ли этот параметр Logical Scan Fragmentation какое-нибудь значение для pk индексов?

Разве создание индекса вообще должно как то менять фрагментацию ?? Тем более логическую.
B0rG
Дата: 25.02.2010 20:19:44
Glory,

BOL говорит, что PRIMARY KEY CLUSTERED INDEX определяет физический порядок данных на диске.

Допустим Logical Scan Fragmentation параметер, показывает насколько фрагментированы физические страницы индекса. Тогда если я полностью перестраиваю не кластерный индекс, то меняется физическое расположение страниц индекса - LSF сильно уменьшается.

Если же я перестраиваю clustered index primary key то этого не происходит.

Почему?

Есть подозрение, что я не правильно трактую священное писание, но хотелось бы узнать в каком именно месте.
Glory
Дата: 26.02.2010 10:37:51
B0rG
Glory,

BOL говорит, что PRIMARY KEY CLUSTERED INDEX определяет физический порядок данных на диске.

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


B0rG

Допустим Logical Scan Fragmentation параметер, показывает насколько фрагментированы физические страницы индекса. Тогда если я полностью перестраиваю не кластерный индекс, то меняется физическое расположение страниц индекса - LSF сильно уменьшается.

Если же я перестраиваю clustered index primary key то этого не происходит.

Я вижу, что вы удаляете и создаете индекс. Никаких "перестраиваю индекс" я не вижу.