Sql server: динамічне управління збором статистики роботи індексу

Відео: стовпчик індекси в SQL Server 2012 (Columnstore Indexes)

Перше, з чим нам необхідно познайомитися - функція динамічного управління (DMF). З її допомогою можна визначати оптимальний коефіцієнт заповнення і виявляти індекси, які стають причинами множинних блокувань.

Нехай термін «функція динамічного управління» не вводить вас в оману. Ці об`єкти схожі на інші функції SQL Server. Ви запитуєте результати через інструкцію SELECT, передаючи один або кілька параметрів. Результати видаються у формі набору, повертає табличні значення: у вигляді однієї або декількох рядків з кількома стовпцями в рядку. Як було показано вище, набір результатів може бути дуже широким, якщо запросити всі стовпці. У даній статті я відмовлюся від повернення всіх стовпців і зупинюся лише на важливих для даної теми. Охочі побачити повний список стовпців і вичерпне пояснення їх призначення можуть відвідати сайт Microsoft, що містить офіційну документацію по sys.dm_db_index_operational_stats (https://msdn.microsoft.com/en-us/library/ms174281.aspx).

Синтаксис виклику sys.dm_db_index_operational stats показаний в коді нижче. Концепцію параметрів шаблонів я описував в одній зі своїх статей, але ви можете просто скористатися поєднанням клавіш Ctrl + Shift + M в SQL Server Management Studio (SSMS), коли ви знайдете синтаксис виду, щоб замінити местозаполнітелі потрібними значеннями.

SELECT * FROM sys.dm_db_index_operational_stats (DB_ID () ,,,) -

Якщо залишити команду незмінною, ви отримаєте результати, які охоплюють всі об`єкти (індекси і купи) і будь-які пов`язані індекси, незалежно від обмежень конкретного розділу. Звичайно, таким чином у вас з`явиться величезний обсяг інформації, але користь від неї невелика через відсутність контексту для результатів. А шкоди може бути чимало: отримати до неї доступ без проблем зможете будь-який досвідчений хакер, або звичайний користувач, який встановив на ваш робочий смартфон програму-шпигун. Тому я завжди з`єдную об`єкти DMO індексування з іншими системними уявленнями, які дають контекст для результатів (а також фільтрують повертаються рядки поряд зі стовпцями, які потрібно побачити). Системні уявлення для контексту наступні:
• sys.indexes - надає інформацію про ваших індексах SQL Server на рівні бази даних, в тому числі ім`я, тип індексу (кластерізованний, Некла-стерізованний), унікальність і ін.
• sys.objects- можна задіяти системну функцію OBJECT_ NAME (objected), щоб повернути ім`я таблиці або подання, пов`язані з object id від sys.dm_db_index_operational_stats, але мені також доведеться фільтрувати результат, так як нас цікавлять тільки призначені для користувача об`єкти, а не системні таблиці та подання, які використовуються всередині SQL Server. Для цього необхідний доступ до колонку is_ms_shipped в sys.objects. Можна також повернути ім`я об`єкта (ім`я стовпця) і тип об`єкту (type_desc).

SELECT * FROM sys.dm_db_index_operational_stats (DB_ID () ,,,) INNER JOIN sys.indexes ION ixO.object_id = I.object_idAND ixO.index_id = I.index_idINNER JOIN sys.objects AS sOON sO.object_id = ixO.object_idWHERE sO.is_ms_shipped = 0-

Відео: Просторові індекси в SQL-сервері (частина 1)

Отримуємо наступну базову структуру, представлену в коді вище.

Саме на цьому фундаменті ми будемо будувати запити, що направляються до sys.dm_db_index_operational_ stats. У коді нижче описаний загальний підхід до отримання повного набору результатів по столбцам- потім ми розглянемо використання sys.dm_db_index_operational_stats як інструмент як для аналізу продуктивності, так і для попереджає оптимізації схем з метою її підвищення. Читаючи далі, зверніть увагу, що я вже замінив параметри шаблону.

SELECT - IDENTIFICATION: DB_NAME (ixO.database_id) AS database__name, O.name AS object__name, I.name AS index__name, I.type_desc AS index__type, ixO.index_id, ixO.partition_number, - LEAF LEVEL ACTIVITY: ixO.leaf_insert_count, ixO.leaf_delete_count, ixO.leaf_update_count, ixO.leaf_page_merge_count, ixO.leaf_ghost_count, - NON-LEAF LEVEL ACTIVITY: ixO.nonleaf_insert_count, ixO.nonleaf_delete_count, ixO.nonleaf_update_count, ixO.nonleaf_page_merge_count, - PAGE SPLIT COUNTS: ixO.leaf_allocation_count, ixO.nonleaf_allocation_count, - ACCESS ACTIVITY: ixO.range_scan_count, ixO.singleton_lookup_count, ixO.forwarded_fetch_count, - LOCKING ACTIVITY: ixO.row_lock_count, ixO.row_lock_wait_count, ixO.row_lock_wait_in_ms, ixO.page_lock_count, ixO.page_lock_wait_count, ixO.page_lock_wait_in_ms, ixO.index_lock_promotion_attempt_count, ixO.index_lock_promotion_count, - LATCHING ACTIVITY: ixO.page_latch_wait_count, ixO.page_latch_wait_in_ms, ixO.page_io_latch_wait_count, ixO.page_io_latch_wait_in_ms, ixO.tree_pag e_latch_wait_count, ixO.tree_page_latch_wait_in_ms, ixO.tree_page_io_latch_wait_count, ixO.tree_page_io_latch_wait_in_ms, - COMPRESSION ACTIVITY: ixO.page_compression_attempt_count, ixO.page_compression_success_countFROM sys.dm_db_index_operational_stats (DB_ID (), NULL, NULL, NULL) AS ixOINNER JOIN sys.indexes ION ixO.object_id = I.object_idAND ixO.index_id = I.index_idINNER JOIN sys.objects AS OON O.object_id = ixO.object_idWHERE O.is_ms_shipped = 0-

Очевидно, ми не будемо отримувати всі стовпці з цих системних об`єктів. У наступних статтях ви помітите поступовий відхід від методології SELECT * - це дозволить зосередитися тільки на шпальтах, важливих для даної теми.

У першій статті я продемонструю перехід від запитів і планів виконання до метаданих, зібраним з sys.dm_db_index_operational_stats. З огляду на, що я приділив увагу супутнього динамічному адміністративному поданням sys.dm_db_index_usage_stats, нам належить порівняти і зіставити цю дію, широко застосовується і тут. Потім ми заглибимося в можливі варіанти використання sys.dm_db index_operational_stats для діагностики очікування блокувань і короткочасних блокувань, познайомимося з випадками, коли корисно змінити коефіцієнти заповнення, вивчимо укрупнення блокувань сторінок і виберемо підходящих кандидатів для стиснення сторінок. Але перш за все важливо зрозуміти, як операції відображаються на метриках в DMF. Для цього потрібно створити невелику тестову базу даних, якщо ви хочете розібратися в проблемі, сидячи перед комп`ютером. Ми будемо звертатися до цієї бази даних у всіх статтях серії і, можливо, згодом теж.