Безсумнівно, сховище даних - один з основних компонентів, що визначають продуктивність і доступність великих і малих примірників SQL Server. В умовах зростаючих обчислювальних можливостей серверів і віртуальних серверів і підтримки об'ємної пам'яті сховища даних і підсистема вводу-виводу можуть виявитися вузькими місцями, що знижують загальну пропускну здатність
ARUBA INSTANT WI-FI: ПРОСТІ, ПОТУЖНІ, ДОСТУПНІ
Забезпечуємо доступність і продуктивність сховищ даних
Безсумнівно, сховище даних - один з основних компонентів, що визначають продуктивність і доступність великих і малих примірників SQL Server. В умовах зростаючих обчислювальних можливостей серверів і віртуальних серверів і підтримки об'ємної пам'яті сховища даних і підсистема вводу-виводу можуть виявитися вузькими місцями, що знижують загальну пропускну здатність. Неприємностей можна уникнути, якщо мати загальне уявлення про те, як SQL Server використовує сховища даних, і знати основні прийоми оптимальної організації сховищ SQL Server.
Дані і файли журналів
Базовий принцип, який лежить в основі роботи SQL Server з сховищами даних, полягає в тому, що бази даних складаються з файлів двох типів:
- Файли даних. У цих файлах зберігається інформація бази даних. Файли даних SQL Server є файли NTFS з розширенням. mdf. Найпростіша база даних зазвичай складається з одного файлу даних, але може складатися і з багатьох файлів даних, що знаходяться на одному або декількох дисках.
- Файли журналів. У цих файлах зберігаються транзакції бази даних, що дозволяє відновити базу даних на певний момент часу. Файли журналів транзакцій SQL Server є файли NTFS з розширенням. ldf. У базі даних може бути багато файлів журналів, розташованих на одному або декількох дисках.
Рекомендується помістити файли даних і журналів на різні диски. SQL Server записує всі транзакції бази даних в журнал транзакцій, тому файли журналів зручно розташовувати на дисках з високою швидкістю запису. Файли даних використовуються для обслуговування запитів і часто повинні виконувати безліч операцій читання. При створенні бази даних можна вказати місце розташування файлів даних та журналів за допомогою команди T-SQL CREATE DATABASE. Щоб змінити місцезнаходження існуючих файлів даних і журналів, можна запустити команду ALTER DATABASE з параметром MODIFY FILE. У лістингу 1 показаний приклад перенесення файлу даних бази даних в інше місце.
Не всі погодяться з рекомендацією включити режим AutoGrow для баз даних SQL Server. При включенні цієї функції для бази даних файли даних і журналів автоматично збільшуються, якщо потрібно більше місця. Цей параметр не допускає зупинки системи, якщо місця не вистачає.
І все ж AutoGrow слід розглядати як механізм останнього рубежу захисту. Його не слід використовувати в якості основного методу керування ростом бази даних. Зростанням всіх файлів даних і журналів слід керувати вручну. Активність бази даних припиняється, коли відбуваються операції AutoGrow. Часті події AutoGrow - хороший індикатор непередбаченого зростання даних. Наступна команда показує, як встановити настройку AutoGrow для файлів даних і журналів:
Майже ніколи не рекомендується активувати функцію AutoShrink для бази даних. Як і операції AutoGrow, операції AutoShrink призводять до зупинки всіх дій бази даних. Крім того, адміністратор не може контролювати час запуску AutoShrink. Використання AutoShrink може привести до спіралі операцій AutoGrow, а потім AutoShrink, а результатом буде зниження продуктивності бази даних і надмірна фрагментація файлів. Запустити AutoShrink можна за допомогою команди:
Ще один корисний прийом при роботі зі сховищами даних - негайна ініціалізація файлів Instant File Initiation. На відміну від більшості розглянутих в статті параметрів, Instant File Initialization управляється політикою Windows Server. Instant File Initialization НЕ обнуляє виділений простір для файлу, а просто виділяє потрібну кількість місця. SQL Server використовує Instant File Initialization під час створення бази даних, AutoGrow і операції відновлення бази даних. Можна включити режим Instant File Initialization на сервері через меню Administrative, щоб відкрити Local Security Policy ( «Локальна політика безпеки»). Потім розгорніть Local Policies ( «Локальні політики») і двічі клацніть на пункті Performance volume maintenance tasks, як показано на екрані.
Екран. Включення Instant File Initialization
В результаті відкривається діалогове вікно властивостей Properties для Performance volume maintenance tasks ( «Виконання завдань з обслуговування томів»), в якому можна ввести ім'я облікового запису SQL Server Service.
Зберігання даних і рівні RAID
Після того, як освоєні сховища SQL Server, можна приступати до вивчення наступної найважливішої концепції - рівнів RAID, які можна використовувати для дисків в підсистемі зберігання даних. Рівні RAID сильно впливають як на продуктивність, так і на доступність. Як і слід було очікувати, більш дорогі варіанти, як правило, забезпечують кращу продуктивність і доступність. Найбільш поширені рівні RAID наступні:
- RAID 0 (іноді іменується чергуванням дисків). На цьому рівні RAID дані розподіляються між усіма доступними дисками. Він часто використовується в різних тестах продуктивності баз даних. RAID 0 забезпечує хорошу продуктивність, але його ніколи не слід застосовувати на виробничому сервері, так як відмова одного диска призводить до втрати даних.
- RAID 1 (іноді іменується дзеркальним відображенням дисків). У конфігурації RAID 1 дані відображаються на дисках дзеркально. Швидкість операцій читання і запису хороша, але загальна ємність дисків зменшується вдвічі. RAID 1 часто використовується для файлів журналів SQL Server. У разі відмови одного диска дані не втрачаються.
- RAID 5 (іноді іменується чергуванням дисків з контролем парності). У конфігурації RAID 5 дані розподіляються по декількох дисках з метою забезпечити надмірність даних. Часто використовується для файлів даних. Цей рівень RAID забезпечує хорошу продуктивність читання і стійкий до відмови одного диска. Однак швидкість запису невелика.
- RAID 10 (іноді іменується дзеркальним відображенням дисків з чергуванням). RAID 10 поєднує в собі швидкодію варіантів з чергуванням і захист через дзеркальне відображення. RAID 10 забезпечує найвищі рівні продуктивності і доступності серед всіх рівнів RAID. Для RAID 10 потрібно вдвічі більше дисків, ніж для RAID 5, але конфігурація стійка до відмови декількох дисків. Масив RAID 10 продовжує успішно функціонувати при відмові половини дисків в наборі. RAID 10 підходить як для файлів даних, так і для журналів.
Ще один важливий компонент системи зберігання даних SQL Server - tempdb. Це системна база даних SQL Server, яка представляє собою глобальний ресурс, доступний всім користувачам. Tempdb використовується для тимчасових об'єктів користувача і внутрішніх операцій ядра системи управління базами даних, в тому числі об'єднань, статистичної обробки, курсорів, сортування, хеширования і управління версіями рядків. На відміну від даних в типовій користувальницької базі даних, дані в tempdb не зберігаються після відключення примірника SQL Server.
Як правило, tempdb - одна з найактивніших баз даних в робочому примірнику SQL Server, тому такі рекомендації допоможуть забезпечити гарну продуктивність бази даних SQL Server. Перш за все, файли даних і журналів tempdb слід розмістити на інших фізичних дисках, ніж файли журналів і даних робочої бази даних. Через дуже активного використання tempdb корисно захистити диски, організувавши масив RAID 1 або масив RAID 10 з чергуванням. Фахівці групи Microsoft SQL Server Customer Advisory Team (SQLCAT) рекомендують, щоб в tempdb був один файл даних для кожного ядра процесора. Але ця рекомендація ефективна для дуже високих робочих навантажень. Найчастіше рекомендується, щоб ставлення файлів даних до ядер процесора становило 1: 2 або 1: 4. Як і в більшості випадків, це загальні рекомендації; оптимальні підходи для конкретної системи можуть відрізнятися. Якщо ви не знаєте точно, скільки файлів використовувати для tempdb, можна почати з чотирьох файлів даних. Зазвичай для tempdb досить одного файлу журналу. Більш докладні рекомендації tempdb ви знайдете в матеріалах, перелічених в урізанні «Навчальна література».
Крім того, розмір tempdb повинен бути достатнім, щоб уникнути операцій AutoGrow. Як і призначені для користувача бази даних, tempdb буде відчувати затримки через операцій AutoGrow. За замовчуванням tempdb містить файл даних в 8 Мбайт, файл журналів в 1 Мбайт і 10% простору для AutoGrow, а це занадто мало для більшості виробничих робочих навантажень. Також важливо пам'ятати, що при перезапуску SQL Server розмір tempdb повертається до останнього заданого значення.
Розмір і переміщення файлів даних і журналів tempdb можна визначати за допомогою програмного коду, наведеного в розділі «Дані та файли журналів». Запит в лістингу 2 (з сайту MSDN) показує, як визначити розмір і відсоток зростання файлів даних і журналів tempdb.
твердотільні диски
Завдяки кільком ядер збільшилася обчислювальна потужність, і багато сучасних систем підтримують дуже великий обсяг оперативної пам'яті, через що підсистема вводу-виводу стала вузьким місцем для багатьох робочих навантажень. Традиційні жорсткі диски стали більш ємними, але швидкодія практично не збільшилася. Проблему можна вирішити за допомогою твердотільних дисків (SSD). Твердотільні диски - порівняно нова технологія зберігання даних, яка почала набирати вагу на ринку SQL Server протягом останнього року. У минулому ціна пристроїв SSD була занадто велика, а інформаційна ємність занадто мала для багатьох робочих баз даних. Одна з причин зростаючої популярності твердотільних дисків - перевага в продуктивності перед традиційними жорсткими дисками з обертовим шпинделем. Наприклад, диск Serial Attached SCSI (SAS) з частотою обертання шпинделя 15 000 об / хв може забезпечити пропускну здатність 200 Мбайт / с. Для порівняння, SSD-диск Serial ATA (SATA) з 6-Гбайт з'єднанням може забезпечити послідовну пропускну здатність близько 550 Мбайт / с. Основна причина переваги SSD-дисків у швидкодії полягає в різкому скороченні часу пошуку. Коли потрібно отримати дані з обертового жорсткого диска, головка повинна переміститися в нове місце. У SSD-диска немає рухомих частин, тому переміщення до нового місця зберігання даних визначається швидкодією осередків пам'яті.
Твердотільні і швидкодіючі флеш-сховища можна реалізувати декількома способами. Типове застосування - 2,5-дюймові диски SSD. Ці пристрої підключаються безпосередньо, як сховища типу DAS, а електронний інтерфейс - такий же, як у стандартного жорсткого диска. Інша поширена реалізація SSD - у вигляді плат PCI Express (PCIe), що підключаються безпосередньо до системної шини. У цьому підході використовуються переваги швидкодіючої шини PCIe, щоб підвищити число операцій введення-виведення в секунду (IOPS) і пропускну здатність у порівнянні зі стандартним інтерфейсом диска. Крім того, багато сховища SAN розташовують розділами SSD і функцією автоматичного розподілу даних по розділах, що дозволяє перемістити важливі робочі навантаження на високопродуктивний розділ SSD, залишаючи менш важливі робочі навантаження на повільних і менш дорогих жорстких дисках.
Існують сховища SSD різних типів. Серед них - сховище SSD на основі DRAM і сховище SSD на основі технології флеш-пам'яті, такий як однорівневі осередки (SLC) і багаторівневі комірки (MLC). У кожного типу є свої переваги і недоліки.
- DRAM. Як звичайна оперативна пам'ять для комп'ютера, DRAM відрізняється дуже високою швидкодією, але ненадійна. Для DRAM потрібен постійний елемент живлення, щоб зберегти дані на час відключення даних. Такі сховища часто випускаються у вигляді плат PCIe, що встановлюються на системній платі сервера.
- SLC. Швидкодія і життєвий цикл сховищ на SLC вище, ніж у MLC, тому SLC використовується в сховищах SSD корпоративного рівня. Однак ціна пристроїв SLC істотно вище, ніж у MLC.
- MLC. Зазвичай флеш-пам'ять типу MLC використовується в споживчих пристроях і обходиться дешевше, ніж SLC. Однак у MLC більш низька швидкість операцій запису і істотно більш високий знос, ніж у SLC.
По швидкодії пристрою SSD перевершують жорсткі диски з обертовим шпинделем, але термін їх експлуатації значно нижче. Додатки з інтенсивним введенням-висновком, такі як SQL Server, скорочують термін життя накопичувача SSD. Крім того, чим більше використовується частина диска, тим менше тривалість життя. Рекомендується переконатися, що принаймні 20% накопичувача SSD не зайняте. Швидкість читання стабільна протягом усього часу експлуатації пристрою. Однак швидкодія при записі в процесі експлуатації погіршується, тобто час, необхідний для запису, збільшується. Важливо також пам'ятати, що немає необхідності дефрагментувати диски SSD, тому що метод доступу до даних іншої, ніж у жорстких дисків. По суті, дефрагментація цього типу дисків призведе тільки до скорочення їх життєвого циклу.
Якщо потрібно використовувати диски SSD, що не застосовуйте єдиний накопичувач SSD і приготуйтеся замінювати диски SSD протягом терміну експлуатації сервера. Перерахуємо можливості застосування SSD в SQL Server.
- Переміщення індексів на диски SSD. Як правило, індекси не дуже великі і пов'язані з інтенсивними безладними операціями читання, тому ідеально підходять для розміщення на дисках SSD.
- Переміщення файлів даних на диски SSD. З файлами даних зазвичай пов'язане більше операцій читання, ніж записи, тому в більшості випадків вони підходять для дисків SSD.
- Переміщення файлів журналів на диски SSD. Файли журналів пов'язані з великим числом операцій записи. Тому якщо для файлів журналів застосовуються диски SSD, використовуйте диски SSD корпоративного рівня і конфігурації RAID 1 або RAID 10 з дзеркальним відображенням.
- Переміщення tempdb на SSD-диск. Як правило, tempdb відрізняється високим рівнем невпорядкованих операцій записи, що може привести до псування SSD. Тому якщо диски SSD використовуються для tempdb, то це повинні бути SSD корпоративного рівня в конфігурації RAID 1 або RAID 10 з дзеркальним відображенням, і потрібен план заміни дисків SSD. Крім того, зверніть увагу на варіант з PCIe DRAM для tempdb. Сховище DRAM забезпечує більш високу швидкодію при запису і має необмежений термін експлуатації. Однак ціни сховищ DRAM можуть бути високими.
Базові рівні продуктивності
Інший основний підхід - підготувати базові рівні продуктивності і періодично порівнювати системну продуктивність з цими базовими рівнями. Це може бути дуже корисним для діагностики неполадок, а також відстеження зростання бази даних і інших тенденцій. Зіставлення з базовим рівнем - один з кращих способів попереджуючого управління системами. Тема вимірювання продуктивності SQL Server виходить за рамки даної статті, але нижче наводиться огляд найважливіших вимірюваних показників сховищ даних.
Перша група лічильників продуктивності, які необхідно відстежувати, є лічильники, які стосуються пам'яті в системному моніторі Windows. Технічно це не лічильники сховища даних, але якщо пам'яті недостатньо, то інші лічильники не мають значення. Обов'язково відстежуйте лічильник Available MBytes об'єкта Memory. Цей лічильник показує обсяг фізичної пам'яті, доступної для виділення процесу або системі. Якщо показник менше 100 Мбайт, то корисно збільшити розмір пам'яті. Інший важливий лічильник -% Usage об'єкта Paging File, який показує використовуваний свап Windows. Це значення має бути менше 70%. Якщо значення вище, то, ймовірно, система вимагає більше пам'яті.
Крім лічильників, пов'язаних з пам'яттю Windows, є кілька лічильників продуктивності сховища Windows Server. Однак показання цих лічильників корисні лише в тому випадку, якщо екземпляр SQL Server працює з системою зберігання даних з прямим підключенням DAS. Якщо використовується SAN, то потрібно звертати увагу на метрики продуктивності SAN.
Якщо екземпляр SQL Server використовує DAS, то в першу чергу переконайтеся, що на кожному диску NTFS вільно принаймні 20% простору. Згодом можна перевірити лічильники сховища Windows Server за допомогою системного монітора. У таблиці 1 наведено список декількох найбільш важливих лічильників; всі вони пов'язані з об'єктом Logical Disk.
