Основні концепції SQL Server для адміністраторів Windows, Windows IT Pro
Для тих, хто управляє SQL-сервером, але не розрізняє таблицю та індекс
Початок початків
Першим кроком у встановленні системи з SQL Server є виділення необхідної кількості пам'яті. Системи баз даних вимогливі до обсягу оперативної пам'яті, і SQL Server перестав бути винятком. Для систем, що обслуговують підрозділи всередині організації, абсолютний мінімум пам'яті має становити 512 Мбайт. Більшим системам потрібно відповідно більше пам'яті. Враховуючи сьогоднішні ціни на оперативну пам'ять можна вільно додати 1 або 2 Гбайт в систему з SQL Server за порівняно невеликі гроші. Інвестиції в додаткову пам'ять вирішать проблеми продуктивності, яка може бути оцінена в термінах скорочення часу вирішення проблем та продуктивності кінцевих користувачів.
Розробники Microsoft зробили SQL Server 2000 легким у встановленні та запуску. Однак я не рекомендую вибирати найпростіший шлях - за замовчуванням. Наприклад, якщо під час встановлення окремого екземпляра (instance) SQL Server вибрати установки за замовчуванням, в результаті отримаємо систему, продуктивність якої нижче оптимальної. За промовчанням програма установки SQL Server створює файли бази даних і файли журналу на тому самому диску. Щоб досягти підвищення продуктивності SQL Server, краще розташувати ці файли на різних дисках. Таким чином, перше, що необхідно зробити при встановленні, це переконатися, що SQL Server має достатню кількість дисків. Як мінімум, потрібно мати три диски. Один для операційної системи, інший для файлів даних і третій для файлу журналів. На рис. 1 показана типова конфігурація дисків для малих та середньомасштабних установок SQLServer. У цьому прикладі наведено систему, що складається з восьми дисків. Тут операційна система, файли даних та файли журналів розміщуються на різних дисках. Для забезпечення відмовостійкості операційна система та файли журналу використовують дзеркаловання. Файли даних використовують чергування даних RAID 5 для забезпечення ефективності роботи сховища. Для максимального захисту даних замість RAID 5 можна використовувати RAID 1 для дисків із даними. Однак це рішення обходиться суттєво дорожче порівняно з RAID 5, оскільки дзеркалювання вимагає вдвічі більшого дискового простору порівняно з обсягом даних, що зберігаються. Дискові приводи SCSI перевищують продуктивність і швидкість приводів IDE, тому використання таких дисків позитивно позначається на продуктивності всієї системи.
Для кращої відновлюваності системи необхідно віддзеркалювати файли журналу. Популярна конфігурація для встановлення SQL сервера така: RAID 1 для дискових томів, що містять файли журналів, та RAID 5 для томів, які містять файли даних. Можливо, ви захочете мати ще один додатковий диск, на якому виконуватимете трасування та інші діагностичні операції. Нарешті, з міркувань безпеки та прийнятної продуктивності краще встановити SQL Server на виділений сервер, а не на контролер домену.
Основи SQL Server
Можливо, процес керування SQL Server 2000 здасться вам не таким складним, коли ви зрозумієте, як працює продукт. SQL Server після установки містить чотири системні бази даних (master, model, msdb і tempdb) і дві бази користувача. База master, напевно, є найважливішою із системних баз. Вона включає таблиці, які описують всі інші бази в системі та містять інформацію прооблікові записи та параметри безпеки. База model містить шаблони для всіх нових баз. Всі бази, що створюються на сервері, успадковують всі установки бази model. База msdb використовується SQL Server Agent для зберігання інформації про розклад завдань. У ній також зберігається інформація про резервні копіювання та реплікації. База tempdb зберігає тимчасові робочі таблиці. Об'єкти в базі tempdb присутні доти, доки користувач, який створив їх, зареєстрований у системі.
Якщо в обов'язки адміністратора входить лише керування SQL Server, а не створення нових баз, то в такому разі немає особливої необхідності вникати в деталі створення об'єктів баз даних. Зазвичай їх формують розробники у IT-департаменті чи постачальники додатків. Однак основні поняття про об'єкти ядра бази даних, бази даних, таблиці, індекси, уявлення, процедури, що зберігаються, і тригери можуть допомогти при вирішенні виникаючих проблем.
Бази даних.Бази даних містять інформацію, яку використовують програми. База SQL Server включає набір таблиць, уявлень, індексів та процедур, що зберігаються. Кожен із додатків зазвичай розроблено так, що він з'єднується зі своєю базою. Виділений SQL Server здатний підтримувати безліч баз даних (32767 баз на сервер). База даних SQL Server може розростатися до великих розмірів. Максимальний розмір бази становить 1,048,516 Tбайт. Кожна база повинна складатися щонайменше з двох файлів — файлу даних та файлу журналу. Файл даних містить таблиці, рядки та стовпці інформації. Файл журналу містить усі транзакційні операції (INSERT, UPDATE, DELETE), які виконували користувачі або програми під час роботи з базою. Як уже згадувалося, щоб гарантувати оптимальну продуктивність, ніколи не слід мати в своєму розпорядженніфайли даних та журналу на тому самому диску працюючої системи. Аналогічно не рекомендується розміщувати файли даних та журналу на стислих чи шифрованих дисках.
Під час створення бази даних SQL Server використовує копію бази model як шаблон для нової бази. Можна встановити максимальний обсяг, до якого може розростатися база в мегабайтах або відсотках від її розміру. Для більшості установок краще вибрати режим auto-grow (автоматичне розширення), який дозволяє базі за потреби розширюватися автоматично. Якщо адміністратор добре розуміється на особливостях наявних додатків, то приблизно повинен представляти очікуване зростання та розміри бази. Тим самим можна уникнути автоматичного розширення бази під час її активного використання, що може негативно зашкодити продуктивності всієї системи.
Індекси.Основна причина використання індексів - прискорення виконання запитів. Індекси – це один із вибраних стовпців таблиці. SQL Server використовує два типи індексів: кластерні та некластерні. Кластерний індекс визначає порядок даних у таблиці. Коли використовується кластерний індекс, SQL Server упорядковує рядки в базовій таблиці відповідно до порядку, визначеного кластерними індексами. Кожна таблиця може мати лише один кластерний індекс. Некластерні індекси не змінюють порядок рядків базової таблиці, вони надають альтернативний оптимізований шлях доступу до даних.
Видалити чи додати індекси можна без втручання у загальну архітектуру бази даних. Хоча першочерговою метою використання індексу є збільшення швидкості доступу до даних, використання надмірної кількості індексів може дати протилежний результат. Коли індекс створено, SQL Server повинен його обслуговувати (тобто.підтримувати в актуальному стані), оскільки дані додаються до таблиці бази. Процес, що забезпечує роботу SQL Server, повинен не тільки додавати дані до таблиці, а й оновлювати існуючі індекси одночасно з новими даними. Звичайно, чим більше індексів ви створите, тим довше проходитиме процес їх оновлення. Надмірна кількість індексів може негативно вплинути на продуктивність усієї системи. Одним із загальних методів, які можуть покращити виконання пакетних завдань, є наступний. Спочатку пишеться сценарій, який видаляє перед виконанням завдання всі індекси. Потім пишеться інший сценарій, який відновлюватиме індекси після закінчення процедури. SQL Server 2000 має майстер налаштування індексів (Index Tuning Wizard), який аналізує запити і пропонує індекси, здатні поліпшити продуктивність цих запитів.
Уявлення.Подання подібне до віртуальної таблиці або збереженого запиту. Дані, доступні через це подання, не зберігаються у вигляді окремого об'єкта бази даних. Натомість користувач створює уявлення за допомогою команди SQL SELECT. Коли запускається відповідне уявлення, користувач бачить результати роботи функції SELECT. Подання зазвичай застосовується для обмеження доступу користувачів до певних стовпців або рядків в одній або кількох таблицях.
Збережені процедури та тригери.Зберігаються процедури створюються з використанням коду мовою T-SQL. Збережені процедури є основою більшості програм баз даних. Тригер - це спеціальний тип процедури, що зберігається, яка може бути підключена до таблиці. Оскільки процедури, що зберігаються, скомпільовані, вони забезпечують найкращу продуктивність у порівнянні з динамічно виконуванимипроцедур SQL (код SQL, який програма генерує перед виконанням команди SQL). Коли динамічний код SQL виконується на сервері, процес, що управляє роботою SQL Server, спочатку аналізує та перевіряє команду, гарантуючи тим самим відсутність помилок у синтаксисі. Потім створюється план, яким буде здійснюватися доступом до даних. Збережені процедури дозволяють виконати цю роботу на етапі створення даної процедури, що зберігається, а не динамічно, в процесі роботи програми. Це дає збереженим процедурам значну перевагу у продуктивності перед динамічним виконанням коду SQL під час роботи програми. SQL Server має функцію кешування запитів. Він може кешувати окремі фрагменти динамічного коду SQL певний час. Однак кешування не дає жодних гарантій того, що необхідний динамічний код все ще буде присутній у кеші при наступному його використанні.
Подібно до процедур, що зберігаються, код T-SQL використовується і для створення тригерів. На відміну від процедур, що зберігаються, які можуть виконуватися будь-якими користувачами, які мають необхідні права на цю процедуру, тригери запускаються виключно самою базою даних. Вони зв'язуються з таблицями за допомогою процедури CREATE TRIGGER. Крім того, вони запускаються в той момент, коли над таблицею здійснюються певні дії, такі як INSERT, UPDATE або DELETE. Адміністратори баз даних зазвичай використовують тригери для забезпечення цілісності посилань. У вас є можливість виконати послідовні операції видалення DELETE в таблицях, на які посилаються рядки таблиці, що посилається. Код тригера запускається лише після перевірки SQL Server цілісності, включаючи перевірки цілісності посилань.
Тепер саме час зупинитися та підбити деякі підсумки. Зараз виповинні знати критерії, за якими вибирають необхідний обсяг оперативної пам'яті, та як краще створити дискову підсистему SQL Server. Також необхідно розуміти призначення основних компонентів SQL Server. У другій частині статті я докладніше зупинюся на внутрішньому пристрої SQL Server для того, щоб полегшити вашу роботу за сумісництвом як адміністратор бази даних.
Поділіться матеріалом з колегами та друзями