Організація індексів в SQL Server 2005, Інші СУБД, Бази даних, статті

SQL Server 2005 представляє нову парадигму для мови DDL. Тут всі об’єкти створюються з використанням команди CREATE, видаляються за допомогою команди DROP і модифікуються за допомогою ALTER. SQL Server 2005 не має окремих збережених процедур для зміни одного аспекту об’єкта, подібно процедурі p_defaultdb у версіях SQL Server 2000 і 7.0, яка змінює визначену за замовчуванням базу даних користувача, або спеціальних одноцільових команд для створення атрибутів, начебто sp_addtype. SQL Server 2000 почав рух в цьому напрямку з розширення функціональних можливостей команди ALTER DATABASE, що дозволяє виконувати всі зміни у властивостях бази даних, і з обмеження використання команди sp_dboption.

Деякі дії, які в попередніх версіях вимагали використання ключів модуля DBCC, будуть виконуватися в SQL Server 2005 командами ALTER. Перш, в SQL Server 2000, DBCC був акронімом модуля перевірки несуперечності бази даних DataBase Consistency Checker, і ключі DBCC, доступні в перших версіях SQL Server, CHECKDB і CHECKTABLE, дійсно виконували набір послідовних перевірок. Але оскільки продукт розростався, а з ним і DBCC, розробники з компанії Microsoft почали давати бідному перевантаженому DBCC та інші завдання, так що врешті-решт вийшло так, що менше за все він якраз займається перевіркою цілісності бази даних. В оперативній документації SQL Server 2000 Books Online (BOL) вже дали нову розшифровку DBCC: DataBase Console Command.


Одні ключі DBCC в SQL Server 2000 вимагають введення інформації, інші – роблять зміни. Дві з команд DBCC, до версії 2005, мали справу з індексами: DBCC SHOWCONTIG виводила звіт про фрагментації індексу, а DBCC INDEXDEFRAG зменшувала фрагментацію, змінюючи сторінки, які використовує індекс. У SQL Server 2005 команда ALTER INDEX замінює DBCC INDEXDEFRAG. Заміною для DBCC SHOWCONTIG став новий динамічно керований об’єкт, названий sys.dm_db_index_physical_stats ().


Розробники Microsoft протягом довгого часу відчували потребу в заміні DBCC SHOWCONTIG. Одним із приводів було те, що DBCC як команда не пропонувала простого способу фільтрації повертається інформації або тільки отримання інформації, що цікавить. Для збереження виводу DBCC SHOWCONTIG в таблицю доводилося задіяти INSERT EXEC, але попередньо потрібно створити таблицю окремою операцією і лише після збереження даних в нову структуру можна фільтрувати результат запиту. У SQL Server 2005 для користування sys.dm_db index_physical_stats () повертається фрагментована інформація. Запит по всіх стовпцях, які ця функція може повернути, дозволяє отримати набагато більше інформації, ніж дає DBCC SHOWCONTIG. Однак через те, що дані повертаються через функцію типу table-valued function (TVF), кількість повернутих стовпців і рядків можна обмежити.


Функція sys.dm_db_index_physical_stats () приймає п’ять параметрів, і всі вони мають значення за замовчуванням. Можна виставити всі параметри за замовчуванням і не накладати фільтр на стовпці і рядки, наприклад:

SELECT * FROM sys.dm_db_index_physical_stats
(NULL, NULL, NULL, NULL, NULL)

Тоді функція повертає 20 стовпців з даними для (майже) кожного рівня для кожного індексу по кожній таблиці кожного розділу в кожній базі даних поточного екземпляра SQL Server. Коли я запустила цю команду на своєму невеликому примірнику, який включає тільки бази AdventureWorks, Pubs і Northwind (плюс системні бази даних), вона повернула понад 390 рядків. Не буду тут представляти весь висновок в 390 рядків і 20 стовпців, а пропоную читачам виконати команду самостійно.


Розглянемо ці п’ять параметрів, які можна передавати за допомогою даної функції. Заодно звертаю увагу читачів на потенційні помилки при використанні параметрів. Наступного разу я поясню деякі стовпці виводу, значення яких можуть бути не так очевидні.


Управління повертаються даними


Перший параметр – це ідентифікатор ID бази даних, який повинен бути вказаний як число. Якщо вказати NULL, що є значенням за замовчуванням, функція поверне інформацію про всі базах даних. Якщо ID не визначено, SQL Server буде ігнорувати такі три параметри і припускає, що вони всі – NULL.


Другий параметр – це object_id, який також повинен бути заданий числом, а не ім’ям. Тут те ж саме: якщо виставити NULL, то функція надасть інформацію про всі об’єкти, і SQL Server призначить такі два параметри, index _id і partition_id, також за замовчуванням, що означає включення всіх індексів і всіх розділів. Розділ – це нова особливість SQL Server 2005. Щоб спростити отримання ID бази даних і ID об’єкта, можна задіяти функції db_id () і object_id () і передавати ім’я бази даних або об’єкта як параметр.


При використанні цих вбудованих функцій слід дотримуватися обережності. Якщо передати неправильне ім’я або просто зробити в імені орфографічну помилку, ніякого повідомлення про помилку не з’явиться, а повертається значенням буде NULL. Оскільки NULL – це допустимий параметр, SQL Server вирішить, що це якраз те, що було потрібно. Припустимо, наприклад, що я хочу бачити всю інформацію, про яку говорилося вище, на цей раз для бази даних, і я наберу з помилкою:

SELECT * FROM sys.dm_db_index_physical_stats
(db_id
“AdventureWords”, NULL, NULL, NULL, NULL)

Такий бази даних, як AdventureWords немає, тому функція db_id () поверне NULL і результат буде той же самий, як ніби я викликаю функцію зі значеннями всіх параметрів як NULL. І система не видасть ніякого повідомлення про помилку або попередження.


За кількістю повертаються рядків можна здогадатися, що допущена помилка, але якщо немає ніяких припущень про те, чого слід очікувати, це не може бути очевидно одразу. У документації BOL пропонується, що користувач може уникнути цієї проблеми, поміщаючи ID в змінні і роблячи перевірку значень змінних, як показано в лістингу , Перед запуском функції sys.dm_db_index_physical_stats ().


Більш підступна проблема, яку я виявила, проводячи тестування з цього стовпцю, полягає в тому, що SQL Server викликає функцію object_id () в контексті поточної бази даних до того, як викликається будь-який запит до динамічно керованої функції (dynamic management function, DMF). Я зверталася до бази даних AdventureWorks, а хотіла отримати інформацію з таблиці бази даних Pubs. Тому я зробила це таким чином:

SELECT *
FROM sys.dm_db_index_physical_stats
(DB_ID(N”pubs”),
OBJECT_ID(N”dbo.authors”), null, null, null);

Оскільки таблиці dbo.authors в моїй поточній базі даних не існує, SQL Server буде повертати в object_id значення NULL, і я буду отримувати інформацію про всі об’єкти в базі даних Pubs. Але якщо таблиця dbo.authors в базі AdventureWorks є, SQL Server буде використовувати ID цієї таблиці і намагатися отримати дані з бази даних Pubs. Тому я отримаю або повідомлення про те, що об’єкта з цим ID в базі даних немає, або є дані з іншої таблиці, а не з тієї, яка мені потрібна. Це завдання може бути важким в плані пошуку помилок, якщо, звичайно, користувач зрозумів, що проблема існує.


Єдине рішення – повністю кваліфікувати ім’я таблиці в запиті до TVF або, як в представленому вище коді, задіяти змінні для отримання ID і цілком певної назви таблиці. Мені здається дещо дивним, що потрібно повністю кваліфікувати об’єкт з ім’ям бази даних, в той час як параметр вже визначає ім’я бази даних. Але що є, то є. Якщо буде створюватися пакувальник процедур для виклику функції sys.dm_db_index_physical_stats (), можна зчепити ім’я бази даних з ім’ям об’єкта перед пошуком ID об’єкта і уникнути проблеми. Результат роботи такої функції приховано, так що виникає бажання написати процедуру, яка звертається до цієї функції і повертає інформацію трохи більш дружнім способом.


Третій параметр дозволяє встановлювати ID індексу для зазначеної таблиці, і знову значення за замовчуванням NULL говорить про те, що користувач отримає на виході всі індекси. Четвертий параметр вказує на номер розділу, і NULL знову означає, що потрібна інформація по всіх розділах. П’ятий і останній параметр – це єдиний параметр, для якого заданий за замовчуванням значення NULL не припускає повернення максимуму інформації. Останній параметр указує режим вибірки, який SQL Server буде використовувати при отриманні даних. Прийняті параметри наступні: DEFAULT, NULL, LIMITED, SAMPLED або DETAILED. Установка значення NULL за замовчуванням відповідає режиму LIMITED.


Заданий режим визначає рівень сканування, яке виконує SQL Server для отримання інформації, необхідної даної функції. При виконанні функції SQL Server переглядає ланцюжок сторінок для попередньо заданого розділу таблиці або індексу. На відміну від DBCC SHOWCONTIG, якою зазвичай необхідна колективна (типу S) блокування таблиці, процедурі sys.dm _db_index_physical_stats () потрібно колективна блокування наміри (IS), яка сумісна з більшістю інших блокувань.


Режим LIMITED – найшвидший і сканує найменше число сторінок. Він переглядає всі сторінки купи, але тільки на рівні батьківських сторінок, які за індексом розташовуються вище рівня листя. Режим SAMPLED повертає статистику, засновану на одному відсотку всіх сторінок індексу або купи. Однак якщо таблиця невелика, SQL Server перетворює режим SAMPLED в режим DETAILED, і для завдань цієї функції “Невелика” означає таблиця менш ніж в 10 000 сторінок. Режим DETAILED сканує всі сторінки і повертає всю статистику. Так що можна помітити, що режими сповільнюються по мірі того, як SQL Server виконує все більшу кількість роботи.


Встановимо собі межа


Як я вже згадувала, функція sys.dm_db_index_physical_stats () може повертати більшу кількість рядків, якщо користувач вводить всі задані за замовчуванням параметри. Але навіть обережне використання доступних параметрів для підмножини таблиць або індексів може повернути більше, ніж потрібно, кількість даних. Оскільки sys.dm_db_index_physical_stats () – це функція типу TVF, можна накладати на повертаються дані власні фільтри. Наприклад, користувач може бачити результати тільки для некластеризованих індексів. Користуючись доступними параметрами, користувач вибирає, потрібно йому бачити всі індекси або тільки один. Якщо в третьому параметрі використовується NULL для зазначення всіх індексів, можна додати фільтр в операторі WHERE для вибору тільки тих рядків, де значення index_id знаходиться між значеннями 2 і 250. Або можна переглядати тільки ті рядки, які вказують на високу ступінь фрагментації.


Лістинг. Перевірка ідентифікаторів бази даних і об’єкта до виклику функції
DECLARE @db_id smallint;
DECLARE @object_id int;

SET @db_id = DB_ID(N”AdventureWorks”);
SET @object_id = OBJECT_ID(N”AdventureWorks
.Person.Address”);

IF @db_id IS NULL
BEGIN;
PRINT N”Invalid database”;
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N”Invalid object”;
END;

SELECT * FROM sys.dm_db_index_physical_stats
(@db_id, @object_id, NULL, NULL, NULL)

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*