NFC Эксперт Deletes
Как мы видели вставки приводят
к внутренней и внешней фрагментации, а обновления к внешней. Удаления записи
приводит к внутренней фрагментации – делает “дырки” на страницах. При удалении
записи, SQL Server не удаляет физически эти записи, а помечает их как удаленные.
Рис. Показывает ситуацию когда
в таблице есть удаленные записи. Это приводит в внутренней фрагментации и
переиспользование места на диске приводит к дополнительным операциям I/O.
Поэтому следует внимательно следить
за фрагментацией таблиц для достижения максимального быстродействия системы.
Что делать дальше.
Теперь вы понимаете как работает
вставка, обновление и удаление данных и как это приводит к фрагментации. Для
дефрагментации в SQL Server существует три пути:
§
DBCCINDEXDEFRAG
§
DBCCDBREINDEX
§
CREATE INDEX WITH DROP_EXISTING
DBCCINDEXDEFRAGпроизводит дефрагментирование leaflevelдля
всех типов индексов и исправляет как внутреннюю так и внешнюю дефрагментацию.
Однако эта команда не создает новые страницы, а лишь перетряхивает информацию
в уже имеющихся. Таким образом сильно фрагментированные индексы не получат
реальной помощи от этой команды. Эта команда имеет одно из главных достоинств:
она накладывает блокировку на очень короткий период в отличии от иных команд.
DBCCDBREINDEX может использована для перестройки индексов
и возможно для изменения fillfactor. Если вы хотите перестроить все индексы
для таблицы, необходимо выдать команду cпустым вторым параметром:
DBCC DBREINDEX (Orders,’’)
Опция WITHDROP_EXISTINGдля команды CREATEINDEX позволяет удалить индексы и перестроить
их при помощи одно команды.
Эта команда особенно полезна для кластерного индекса
поскольку некластерный индекс может быть не перестроен. Посмотрим на пример:
вы выполняете команду CREATEINDEXWITHDROP_EXISTING без перестройки ключей.
Поскольку кластерный
индекс выполняет функцию rowlocator, то он остается не перестроенным. Следовательно
и все остальные индексы останутся нетронутыми.
Если будет удален кластерный
индекс, это потребует перестройки всех не кластерных индексов (сменился rowlocator). Таким образом для перестройки
всех индексов таблицы достаточно выдать команду для перестройки одного кластерного
индекса.
Вы можете модифицировать структуру индексов во время использования
опции WITHDROP_EXISTING но только с одним ограничением – невозможно преобразование кластерного
индекса в некластерный.
Вот пример сохраненной процедуры,
выполняющей перестройку индексов для всех таблиц базы данных:
CREATE PROCEDURE sp_reindex_all_tables
DECLARE reindex_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = ‘U’
OPEN reindex_cursor
DECLARE @tablename sysname
FETCH NEXT FROM reindex_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXECUTE (‘DBCC DBREINDEX (“” @tablename “”,”””)’)
FETCH NEXT FROM reindex_cursor INTO @tablename
END
CLOSE reindex_cursor
DEALLOCATE reindex_cursor
Автоматическое создание индексов
Перед созданием индекса необходимо решить, следует ли создать индекс для одного поля или составной индекс. Индекс для одного поля создается с помощью установки свойства Индексированное поле. В таблице ниже приведены возможные параметры свойства Индексированное поле.
Параметр свойства «Индексированное поле» | Значение |
|---|---|
Нет | Не создавать индекс для этого поля (или удалить существующий индекс) |
Да (допускаются совпадения) | Создать индекс для этого поля |
Да (совпадения не допускаются) | Создать уникальный индекс для этого поля |
При создании уникального индекса невозможно ввести новое значение в определенном поле, если такое значение уже существует в том же поле другой записи. Access автоматически создает уникальный индекс для первичных ключей, однако может потребоваться запретить создание повторяющихся значений и в других полях.
Создание индекса для одного поля
В области навигации щелкните правой кнопкой мыши имя таблицы, в которой необходимо создать индекс, и выберите в контекстном меню пункт Конструктор.
Щелкните пункт Имя поля для поля, которое следует индексировать.
В разделе Свойства поля откройте вкладку Общие.
В свойстве Индексированное выберите значение Да (допускаются совпадения), если следует разрешить повторяющиеся значения, или значение Да (совпадения не допускаются), чтобы создать уникальный индекс.
Чтобы сохранить изменения, щелкните элемент Сохранить на панели быстрого доступа или нажмите клавиши CTRL S.
Создание составного индекса
Чтобы создать составной индекс для таблицы, добавьте строку для каждого поля в индексе и укажите имя индекса только в первой строке. Все строки будут обрабатываться как часть одного индекса, пока не будет обнаружена строка с другим именем индекса. Чтобы вставить строку, щелкните правой кнопкой мыши место, куда вы хотите ее вставить, и выберите в контекстном меню команду Вставить строки.
В области навигации щелкните правой кнопкой мыши имя таблицы, в которой необходимо создать индекс, и выберите в контекстном меню пункт Конструктор.
На вкладке Конструктор в группе Показать или скрыть щелкните пункт Индексы.
Появится окно «Индексы». Измените размеры этого окна, чтобы отображались пустые строки и свойства индекса.
В первой пустой строке столбца Индекс введите имя индекса. Для индекса можно использовать либо имя одного из индексируемых полей, либо другое подходящее имя.
В столбце Имя поля щелкните стрелку, затем щелкните первое поле, которое следует использовать в индексе.
Следующую строку столбца Индекс оставьте пустой, затем в столбце Имя поля укажите второе индексируемое поле. Повторите этот шаг для всех полей, которые необходимо включить в индекс.
Чтобы изменить порядок сортировки значений полей, в столбце Порядок сортировки окна «Индексы» щелкните пункт По возрастанию или По убыванию. По умолчанию выполняется сортировка по возрастанию.
В разделе Свойства индекса окна Индексы укажите свойства индекса для строки в столбце Имя индекса, содержащем имя индекса. Задайте свойства в соответствии с таблицей ниже.
Подпись
Значение
Первичный
Если Да, то индекс является первичным ключом.
Уникальный
Если Да, то каждое индексируемое значение должно быть уникальным.
Пропуск пустых полей
Если Да, то записи с пустыми значениями в индексируемых полях будут исключены из индекса.
Чтобы сохранить изменения, нажмите кнопку Сохранить на панели быстрого доступа или нажмите клавиши CTRL S.
Закройте окно «Индексы».
В некоторых случаях индексы создаются автоматически. Например, индексы создаются для любых полей, которые определяются пользователем в качестве первичного ключа таблицы.
Для автоматического создания индекса также можно использовать параметр Автоиндекс при импорте и создании в диалоговом окне Параметры Access. Access автоматически проиндексирует все поля, имена которых начинаются с указанных в поле Автоиндекс при импорте и создании знаков или заканчиваются ими, например ID, ключ, код или число. Чтобы просмотреть или изменить текущие параметры, сделайте следующее:
Выберите Файл > Параметры.
Щелкните Конструкторы объектов, а затем в разделе Конструктор таблиц добавьте, измените или удалите значения в поле Автоиндекс при импорте и создании. Для разделения значений используйте точку с запятой (;).
Примечание: Если имя поля начинается со значения, указанного в списке, или заканчивается им, поле будет автоматически проиндексировано.
Нажмите кнопку ОК.
Так как каждый индекс требует дополнительной обработки, производительность при добавлении или обновлении данных снижается. Поэтому рекомендуется изменить значения, указанные в поле Автоиндекс при импорте и создании или уменьшить их число, чтобы сократить количество создаваемых индексов.
К началу страницы
Выбор индексов
Как мы видели в предыдущих примерах,
SQL Server достаточно точно знает как данные распределены по таблице при наличии
индекса. Каждый раз при выполнении запроса, SQL Server первым делом оценивает
статистику. Давайте рассмотрим несколько примеров для более полного понимания
данного процесса.
SELECT *
FROM Orders
WHERE OrderDate BETWEEN ‘1996-07-19’ AND ‘1996-07-25’
SQL Server сначала проверяет существования
индекса по полю OrderDate или составного индекса, начинающегося с поля OrderDate.
В таком случае SQL Server знает как много записей вернется в результате запроса.
Query Analyser дает следующую картинку:
Для того что бы посмотреть детали
каждого этапа выполнения запроса, просто выберете нужный этап и кликните правой
клавишей мыши.
Мы видим что оценочное количество
записей – 6, что и реально соответствует действительности.
Теперь выполним этот запрос:
SELECT *
FROM Orders
WHERE OrderDate BETWEEN ‘1996-07-19’ AND ‘1996-07-25’
AND CustomerID = ‘FOLKO’
SQL Server найдетодининдекс OrderDate иодин CustomerID. Он будет использовать их обоих и
результатом будет пересечение по этим двум условиям.
Если заменить условие операции
AND на OR, система может сделать объединение индексов или может предпочесть
полное сканирование таблицы, если сочтет что такой путь будет более быстрым
и имеет меньшую стоимость чем работа с индексами.
Внаемконкретномслучаеврезультатезапроса
SELECT *
FROM Orders
WHERE OrderDate BETWEEN ‘1996-07-19’ AND ‘1996-07-25’
OR CustomerID = ‘FOLKO’
Было произведено полное сканирование
таблице так как стоимость операции OR оказалась выше. Это объясняется тем
что размеры таблицы сравнительно малы.
Что же произойдет, если для таблицы
нет индексов, следовательно нет статистики? Ответ прост: SQL Server не может
работать без статистики! И он создаст статистику без каких либо индексов.
Если выполнить запрос:
SELECT *
FROM Orders
WHERE ShipCity=’Grass’
SQL Server автоматически создаст
статистику для этого поля, поскольку нет индекса по нему. Для того что бы
просмотреть все индексы, созданные для определенной таблицы, выполним запрос:
SELECT name, first, root
FROM sysindexes
WHERE id=OBJECT_ID(‘Orders’)
Мы получили результат:
name first root
PK_Orders 0xCD0000000100 0xCB0000000100
CustomerID 0x690100000100 0x6C0100000100
CustomersOrders 0x6F0100000100 0x720210000100
EmployeeID 0xDA0000000100 0xDA0000000100
EmployeesOrders 0xDC0000000100 0xDC0000000100
OrderDate 0xDE0000000100 0xDE0000000100
ShippedDate 0xE00000000100 0xE00000000100
ShippersOrders 0xE20000000100 0xE20000000100
ShipPostalCode 0x740100000100 0x770100000100
_WA_Sys_ShipCity_014935CB 0x000000000000
0x000000000000
Заметьте имя “индекса” _WA_Sys_ShipCity_014935CBс rootадресам 0х0 – потому что это не индекс.
Этостатистикадляполя ShipCity. Вам не стоит беспокоится о засорении вашей базы данных ненужной информацией
– автоматически созданная статистика будет уничтожена так же автоматически
когда SQL Server посчитает что она более не нужна. Просто доверьтесь SQL Server.
(Хорош оборот, неправда ли!?)
Вы можете так же изучить наличие
статистики при помощи команды
sp_helpstats ‘Orders’
statistics_name statistics_keys
_WA_Sys_ShipCity_014935CB ShipCity
Атакжепривыполнениикоманды Tools à Manage Statistics в Query Analyser.
Эта статистика была создана автоматически
потому что параметр базы данных AUTO_CREATE_STATISTICS установлен в “On”.
Автоматическое обновление статистики
является огромной помощью для DBA. Порой крайне сложно определить какая же
статистика должна быть создана и эту функцию на себя взял SQL Server. Существует
только одна проблема – поддерживать корректную на каждый момент времени статистику.
Обслуживание статистики.
Что произойдет если статистика
устареет и не будет отражать реальную картину с данными в таблице. Ответ очень
прост – выбор индекса может быть неверен. Представим что статистика была собрана
когда в таблице было только 1 000 записей, а теперь ее размер 100 000. Что
бы быть реально полезной, статистика должна содержать текущие реальные данные.
SQL Server позволяет обновлять
статистику автоматически без привлечения дополнительных усилий со стороны
DBA. Проверить, установлен ли флаг автоматического обновления статистики,
можно командой:
SELECT DATABASEPROPERTYEX(‘dbname’,’IsAutoUpdateStatistics’)
Если результат равен 1, это значит
что опция автоматического обновления статистики включена. Установить опцию
в этот режим можно командой
ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS ON
авыключить
ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF
Так же эту операцию можно установить
используя сохраненную процедуру sp_dboption, но в SQL Server 2000 она оставлена
только для обратной совместимости и может исчезнуть в будущих версиях.
Рекомендуется оставлять эту функцию
включенной. В этом случает SQL Server будет сам обновлять статистику когда
посчитает ее устаревшей. Алгоритм обновления полностью определяется SQL Server
и зависит от количества обновлений, удалений и добавлений записей в таблицу.
Кроме того если размер таблицы
более 8МБ (1 000 страниц), SQL Server не будет использовать все данные для
вычисления статистики. Все эти ограничения разработаны для того что бы работа
со обновлением статистики наносила как можно меньший удар на быстродействие
сервера.
Так же управлять автоматическим
обновлением статистики можно при помощи сохраненных процедур типа sp_autostats:
Команда
sp_autostats ‘Orders’
показывает включена ли опция автоматического
обновления статистики для конкретных индексов и когда обновление было сделано
в последний раз. Так же эта команда позволяет включить или отключить опцию
обновление для всех индексов таблицы или для какого-то конкретного индекса.
Итого что бы отключить автоматическое обновление статистики у вас есть несколько
вариантов:
§
ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS
§
sp_autostats
§
используйте STATISTICS_NORECOMPUTE
вкоманде CREATE INDEX
§
используйте NORECOMPUTE
в STATISTICS UPDATES или CREATE STATISTICS
Как только автоматическое обновление
будет отключено, вы будете вынуждены обновлять статистику вручную. Эта операция
может быть выполнена при сопровождении индексов или операцией UPDATE STATISTICS.
Полное описание UPDATE STATISTICS
стоит изучить по BOL.
Кластерные и не кластерные индексы
По умолчанию, индекс всегда создается
не кластерным, если не указано иное. Таблица может иметь только один кластерный
индекс.
Процедура sp_helpindex возвращает
полный список всех индексов для таблицы.
Например
sp_helpindex Orders
index_name index_description
index_keys
CustomerID nonclustered located on PRIMARY
CustomerID
CustomersOrders nonclustered located on PRIMARY CustomerID
EmployeeID nonclustered located on PRIMARY
EmployeeID
EmployeesOrders nonclustered located on PRIMARY EmployeeID
OrderDate nonclustered located on PRIMARY OrderDate
PK_Orders clustered, unique, primary key
located on PRIMARY OrderID
https://www.youtube.com/watch?v=cNEirK2lUi4
ShippedDate nonclustered located on PRIMARY ShippedDate
ShippersOrders nonclustered located on PRIMARY ShipVia
ShipPostalCode nonclustered located on PRIMARY ShipPostalCode
Вы так же можете получить более
удобный результат при выборе команды Tools à Manage indexes в Query Analyser.
Заметим что все индексы находятся
в PRIMARY file group. В случае необходимости повышения быстродействия, можно
поместить индексы в другую file group, отличную от местонахождения таблицы,
для баланса I/O операций между разными физическими дисками.
Команда CREATE INDEX имеет ряд
параметров. Давайте рассмотрим некоторые из них.
1) ASC|DESC
ASC значит что индекс будет построен
по возрастанию ключей. DESC соответственно – по убыванию. Эта опция не дает
никакой разницы на поиск данных, но оказывает существенное влияние на скорость
выполнения ORDER BY опции в запросах.
2) SORT_IN_TEMPDB
Полезна при создании индексов Эта опция
может увеличить производительность системы во время создания индексов. Индексы
создаются в два этапа. На первом создается временный набор данных с отсортированными
ключами для не кластерного индекса.
На втором – окончательный результат переносится
на место его хранения в базе данных. Без указания данной опции временный результат
создается в той же file group, где и будет создан индекс. При указанной опции,
временный результат будет находиться в базе данных Tempdb.
Преимущества очевидны,
особенно если поместить базу данных Tempdb на другой физический диск. Выигрыш
будет получен на обеих стадиях так как чтение и запись данных будут вестись
параллельно на двух разных дисках. Вторая положительная сторона использования
указанного параметра – созданный индекс будет менее фрагментирован.
3) IGNORE_DUP_KEY
Очень хитрая опция. Она рассматривает
поведение в случае вставки данных в уникальный столбец (или группу столбцов).
Без этой опции если идет попытка вставки дублирующего значения, то вся вставка
будет откачена (rolled back).
Server: Msg 2601, Level 14, State 3, Line
1
Cannot insert duplicate key row in object
‘TestTable’ with unique index ‘id1’.
The statement has been terminated.
С этой опцией вставка записей будет продолжена, а дублирующие
записи отброшены с сообщением warning:
Server: Msg 3604, Level 16, State 1, Line
2
Duplicate key was ignored.
Примердляпроверки:
create table TestTemp (c1 int, c2 varchar(10))
create table TestTable (c1 int, c2 varchar(10))
create table TestTable_IGNORE_DUP_KEY (c1 int, c2
varchar(10))
create unique index id1 on TestTable(c1)
create unique index id2 on TestTable_IGNORE_DUP_KEY(c1)
with IGNORE_DUP_KEY
insert TestTemp values(1,’test’)
insert TestTemp values(1,’test’)
insert TestTable select * from TestTemp
insert TestTable_IGNORE_DUP_KEY select * from TestTemp
Распределение статистики
Индексы не выбираются на основании
анализа распределенной статистики. Немного истории – в SQL Server 6.5 статистика
хранилась на странице в 2 Кб, независимо от размера индекса. Таким образом
при увеличении размера индекса, уменьшалась точность описания распределения
данных по индексу.
Что бы понять что из себя представляет
статистика, рассмотрим следующий пример. Возьмем таблицу Orders из базы данных
Northwind. Если мы выполним следующий запрос:
SELECT TOP 24 OrderID, convert(char(11), OrderDate)
FROM Orders
ORDER BY OrderDate
Вы получите следующий результат:
OrderID OrderDate
10248 Jul 4 1996
10249 Jul 5 1996
10250 Jul 8 1996
10251 Jul 8 1996
10252 Jul 9 1996
10253 Jul 10 1996
10254 Jul 11 1996
10255 Jul 12 1996
10256 Jul 15 1996
10257 Jul 16 1996
10258 Jul 17 1996
10259 Jul 18 1996
10260 Jul 19 1996
10261 Jul 19 1996
10262 Jul 22 1996
10263 Jul 23 1996
10264 Jul 24 1996
10265 Jul 25 1996
10266 Jul 26 1996
10267 Jul 29 1996
10268 Jul 30 1996
10269 Jul 31 1996
10270 Aug 1 1996
10271 Aug 1 1996
В этом примере выбраны записи с
4 июля 1996 по 1 августа 1996. Теперь посчитаем сколько раз встречаются те
или иные значения:
SELECT convert(char(11), A.orderdate) as OrderDate
, count(*) as ‘# of OrderDate’
FROM (SELECT TOP 24 OrderID, OrderDate
FROM Orders
ORDER BY OrderDate) as A
GROUP BY A.orderdate
OrderDate
# of OrderDate’
Jul 4 1996 1
Jul 5 1996 1
Jul 8 1996 2
Jul 9 1996 1
Jul 10 1996 1
Jul 11 1996 1
Jul 12 1996 1
Jul 15 1996 1
Jul 16 1996 1
Jul 17 1996 1
Jul 18 1996 1
Jul 19 1996 2
Jul 22 1996 1
Jul 23 1996 1
Jul 24 1996
1
Jul 25 1996
1
Jul 26 1996
1
Jul 29 1996
1
Jul 30 1996
1
Jul 31 1996
1
Aug1 1996 2
Когда SQLServerсчитает или сортирует данные, он заранее знает
как много тех или иных значений он найдет в указанном запросе.
SELECT *
FROM Orders
WHERE OrderDate BETWEEN ‘1996-07-15’ AND ‘1996-07-20’
SQL Server знает что запрос вернет
только 6 записей еще до того как будет произведен доступ к таблице. Назначение
статистики базируется на простом алгоритме: для выбора стратегии доступа к
данным, SQL Server должен знать как много записей вернет запрос.
В SQL Server статистика не просто
учитывает количество записей для того или иного значения. Первое, статистика
более точно описывает распределение данных по значениям. Второе, зона описания
статистики может не содержать всех значений индексируемого поля.
Выполнимкоманду
dbcc show_statistics (Orders,OrderDate)
Statistics for INDEX ‘OrderDate’.
Updated Rows Rows Sampled Steps Density Average key length
—————————————————————————–
Dec 23 2002 9:30AM 830 830 187
1.6842021E-3 12.0
Этот результат показывает следующее
§
статистика была просчитана в последний раз 23
декабря 2002
§
таблица содержит 830 записей
§
все записи были проанализированы для получения
статистики
§
в статистике информация сохранена в дискретности
на 187 записей
§
средняя плотность распределения примерно 0.17%
Пожалуй самым интересным здесь
будет значение плотности распределения. Если каждое значение в таблице уникальное,
то плотность будет 1/830, то есть 0.12%. Но в нашем примере мы имеем 0.17%
показывает что некоторые значения встречаются 2 и более раз.
Например если колонка содержит
только 3 значения, плотность распределения будет равна 33.3%, что показывает
бесполезность построения индекса по данному полю. Индексы занимают место на
диске и в оперативной памяти и отнимает быстродействие.
В идеале самый лучший
индекс имеет плотность распределения равную единице, деленной на количество
записей в таблице – все записи уникальны. При построении индексов, обращайте
внимание на плотность распределения – если она превышает 10%, то индекс можно
считать бесполезным. Сканирование по таблице в таком случае будет более эффективным.
Второй результат, возвращаемый
командой dbcc show_statistics
All density Average Length Columns
—————————————————————-
2.0833334E-3 8.0 OrderDate
1.2048193E-3 12.0 OrderDate, OrderID
Это дает очень интересный результат
потому что плотность для одного поля OrderDate 0.2%, а для пары OrderDate,
OrderID уже 0.12. Поскольку OrderID является primary key для таблице, то понижение
плотности вполне очевидно.
Последний результат может быть
наиболее интересен для полного понимания важности статистики.
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS
1996-07-04
0.0 1.0
0
1996-07-15 7.0 1.0
6
1996-07-19 3.0 2.0 3
1996-07-25 3.0 1.0 3
1996-08-01 4.0 2.0 4
Во-первых, заметим что только пять
значений в зоне распределения вместо 24 в самой таблице. Тем не менее учитывая
значения по всем колонками, система знает сколько записей будет в выборке.
Колонка RANGE_HI_KEY дает высшее значение для значения, сохраненного в статистике.
Мы знаем что 1996-07-04 является первым значением и следующим за ним идет
1996-07-15. Между этими двумя значениями находится 7 записей. Колонка RANGE_ROWS
дает нам эту информацию. Только три значения есть между 1996-07-15 и 1996-07-19,
и так далее.
Колонка DISTINCT_RANGE_ROWS содержит информацию о том сколько
определенных (неповторяющихся) значений в интервале. Например в интервале
с 1996-07-04 по 1996-07-15 есть 6 определенных значений из 7 записей в интервале.
Это говорит что одно значение в указанном интервале повторяется дважды.
Анализируя эту информацию, можно
определить как распределены данные в индексе, а так же как много записей вернет
запрос.
Сканирование таблицы
Когда запрос выполняется к таблице
не имеющей кластерного индекса и без использования не кластерных индексов,
выполняется простое сканирование таблицы. Для того что бы найти данные в куче
(heap), SQL Server использует Index Allocation Map (IAM).
IAM представляет
страницу которая содержит карту всех экстентов, которые содержат данные указанной
таблицы. SQL Server использует IAM что бы найти все страницы с данными. Рис.7
иллюстрирует методологию используемую SQL Server. 1 означает что экстент используется
объектом, а 0 – что не используется.
Любой запрос, который не может
использовать индексы, работает по следующей методологии:
1. SQL Server делает запрос к системной таблице Sysindexes для нахождения
FirstIAM страницы.
2. SQL Server берет IAM страницу и находит экстенты где хранится таблица.
3. SQL Server берет данные из страниц и экстентов, найденных по IAM
Как вы видите, последовательность записей, получаемых конечным
пользователем зависит от того в каком порядке данные хранятся в IAM а не от
порядка вставки записей в таблицу. Это объясняет что нет никакой логики в
порядке полученных данных, поскольку порядок их получения зависит от их порядка
в IAM.
Выполним следующий запрос:
SELECT id, indid, fistIAM
FROM sysindexes
WHERE id=OBJECT_ID(‘CustomerCustomerDemo’)
Получим результат:
Рис.7 Доступ к таблице без индекса
Заметьте что indid имеет значение
0, что означает что данная таблица хранится как heap.
В случае когда производится поиск
по таблице без индексов, производится простое сканирование всех записей.
Эта операция показывается в query
execution plan следующей иконкой:
Доступ к данным с использованием кластерного
индекса
Как только в таблице имеется кластерный
индекс, IAM более не используется для доступа к данным. IAM не исчезает вообще,
но используется только для сопровождения таблицы как объекта в базе данных.
Страницы данных взаимосвязаны и данные в них находятся в соответствии с кластерным
индексом.
Если выполнить запрос SELECT *
FROM Customers без каких-либо условий WHERE, то система выполнить сканирование
таблицы с использованием кластерного индекса. Эта операция очень похожа на
простое сканирование таблицы. Главное различие между сканированием по кластерному
индексу то, что результат сканирования вернется в порядке сортировки по кластерному
индексу.
Эта операция показывается в query
execution plan следующей иконкой:
Теперь если выполнить поиск по
кластерному индексу, SQL Server будет выполнять индексный поиск. Например:
SELECT *
FROM Customers
WHERE customerid = ‘ALFKI’
Эта операция показывается в query
execution plan следующей иконкой:
Заметьте что стрелочка изогнута
что индицирует что SQL Server использует индекс для нахождения соответствующего
значения. При выполнении этой операции SQL Server находит root page из таблицы
Sysindexes и ищет совпадение значений по индексу. Рис.8 Показывает как выполняется
указанный поиск.
Рис. 8
Сначала SQL Server находит root
page используя запрос
SELECTid, indid, root
FROMsysindexes
WHEREid = OBJECT_ID(‘Customers’)
Рассмотрим результат:
Indid равно 1, что указывает на
то что это кластерный индекс. В случае heap это значение будет равно 0.
Значение root – входная дверь для
индекса. Как только отправная точка будет найдена, SQL Server найдет все значения
удовлетворяющей условию WHERE. Это проиллюстрировано на Рис.8 Мы ищем записи,
значение которых равно ‘London’.
С root страницы мы находим что запись ‘London’
находится между ‘Johannesburg’ и ‘London 2’. Таким образом записи, удовлетворяющие
условию начинаются на странице 11, где находится ‘Johannesburg’. Мы помним
что записи отсортированы по этому полю.
В этом конкретном примере, SQL
Server сканирует три физические страницы вместо пяти при полном сканировании
таблицы.
Типы фрагментаций
Когда запись удаляется, в файле
БД высвобождается место. Когда вставляется новая запись, это может привести
к расщеплению страниц, что приводит к появлению пустого пространства на страницах
данных. Когда данный обновляются, это может привести к изменению размера записи
и к возникновению двух ранее упоминавшихся случаев. Все это приводит к фрагментации.
В SQL Server рассматриваются два типа фрагментации: внутренняя и внешняя.
Внутренняя подразумевает пустоты
внутри страницы. Внешняя – непоследовательность связей страниц.
Пример внутренней фрагментации
показан на Рис.
В этом примере страницы не полностью
заполнены данными, что приводит к дополнительным операциям I/O и переиспользованью
оперативной памяти. Помните что страницы в оперативной памяти есть зеркальное
отражение страниц на диске.
Пример внешней фрагментации показан
на Рис.
В идеале страницы должны быть подлинкованы
слева направо в порядке хранения данных. Вследствие расщепления страниц этот
порядок может быть нарушен. Это приводит как к неполному заполнению страниц,
так и к увеличению операций I/O вследствие непоследовательного положения цепочек
страниц на диске – это вызывает дополнительные перемещения головок с цилиндра
на цилиндр диска. А это одна из наиболее медленных дисковых операций.
Команда DBCC SHOWCONTIG помогает
определить как внутреннюю так и внешнюю фрагментацию.
Выполним команду
DBCC SHOWCONTIG(‘Orders’)
Изучимрезультат:
DBCC SHOWCONTIG scanning ‘Orders’ table…
Table: ‘Orders’ (21575115); index ID: 1, database ID: 6
TABLE level scan performed.
– Pages Scanned…………………………..: 20
– Extents Scanned…………………………: 5
– Extent Switches…………………………: 4
– Avg. Pages per Extent……………………: 4.0
– Scan Density [Best Count:Actual Count]…….: 60.00% [3:5]
– Logical Scan Fragmentation ………………: 0.00%
– Extent Scan Fragmentation ……………….: 40.00%
– Avg. Bytes Free per Page…………………: 146.5
– Avg. Page
Density (full)…………………: 98.19%
Команда DBCC SHOWCONTIG работает на leaf level поэтому дает
ответ только о положении страниц.
Расшифруем результат:
— Pages Scanned указывает количество страниц в таблице. В
нашем примере их 20.
— Extents Scanned показывает количество экстентов занимаемых
таблицей. Это сразу указывает на фрагментированность данных – для сохранения
20 страниц хватает 3х экстентов.
— Extent Switches говорит о количестве раз переключения с
экстента на экстент при последовательном чтении данных. В идеальной ситуации
это число равно Extents Scanned – 1
— Avg. Pages per Extent говорит о среднем количестве страниц
на экстент при перемещении по цепочке страниц. Это значение должно быть как
можно ближе к 8
— Scan Density представляет собой значение для внешней фрагментации.
Этот результат получается от соотношения идеальной смены экстентов к фактической.
Вполне очевидно, это что должно быть близко к 100%
— Logical Scan Fragmentation дает процент страниц не в логическом
порядке. Если страницы находятся в строгой последовательности слева направо,
то данный параметр будет иметь значение 0
— Extent Scan Fragmentation дает процент экстентов не в логическом
порядке. Имеет то же логическое значение что и Logical Scan Fragmentation
— Avg. Bytes Free per Page – должно быть как можно ближе к
0 если fill factor 100. Иное значение требует незначительных расчетов. Если
fill factor 80, это обеспечивает примерно 1600 свободных байтов на страницу.
— Avg. Page Density должно быть как можно ближе к 100%. Avg.
Bytes Free per Page и Avg. Page
Density дают хорошее представление о внутренней фрагментации.
В нашем примере мы имеем Avg. Page Density 98.19%, что означает
что нет внутренней фрагментации(длина записей не всегда совпадает с размером
страницы). С другой стороны Scan Density 60% и Extent Scan Fragmentation 40%
говорит о внешней фрагментации. Если мы дефрагментируем таблицу а выполним
эту команду еще раз, мы получим следующий результат:
DBCC SHOWCONTIG scanning ‘Orders’ table…
Table: ‘Orders’ (21575115); index ID: 1, database ID: 6
TABLE level scan performed.
– Pages Scanned…………………………..: 20
– Extents Scanned…………………………: 3
– Extent Switches…………………………: 2
– Avg. Pages per Extent……………………: 6.7
– Scan Density [Best Count:Actual Count]…….: 100.00% [3:5]
– Logical Scan Fragmentation ………………: 0.00%
– Extent Scan Fragmentation ……………….: 0.00%
– Avg. Bytes Free per Page…………………: 146.5
https://www.youtube.com/watch?v=7seaBQkmJTs
– Avg. Page
Density (full)…………………: 98.19%