Практичні поради щодо SQL Server, частина 1 конфігурації

Головна »Найкращі практики роботи з SQL Server, Частина 1: Налаштування

поради

Читання посібників з найкращих практик для Microsoft SQL Server може бути справжньою справою. Пробившись через технічні посібники, посібники з найкращих практик, статті TechNet та тексти блогів від експертів SQL, ми дійшли висновку, що простий для розуміння текст на цю тему був би корисним.

Наша мета полягає не в тому, щоб заглибитися в усі налаштування, а в поясненні кількох речей, які допоможуть вам налаштувати та вирішити проблеми з продуктивністю.

Спільні та спеціальні екземпляри

Якщо програма використовує велику кількість схем/збережених процедур, це може потенційно погіршити продуктивність програм, використовуючи той самий екземпляр. Доступні ресурси можуть бути розділені або заблоковані. Це може призвести до уповільнення роботи інших програм на базі даних на загальному екземплярі SQL Server. Виправлення проблем із продуктивністю може бути клопотом, оскільки вам потрібно з’ясувати, який екземпляр спричиняє проблему. І це часто буває не так просто.

Те, як компанії відповідають на це питання, зазвичай зважується з вартістю операційних систем та ліцензій SQL. Якщо продуктивність програми є головним пріоритетом, настійно рекомендується виділений екземпляр.

Корпорація Майкрософт продає ліцензії SQL Server не для кожного екземпляра, а для кожного ядра. З міркувань витрат адміністратори, як правило, встановлюють стільки екземплярів SQL Server, скільки сервер може просто обробити, що може призвести до значних проблем із продуктивністю в довгостроковій перспективі.

Тому, коли це можливо, вам слід обирати виділені екземпляри SQL.

Розбиття файлів SQL на різні носії даних

SQL Server використовує дуже різні шаблони вводу-виводу для читання даних та файлів журналів. Доступ до файлів даних, як правило, випадковий, до файлів журналів транзакцій послідовно. На обертових жорстких дисках головка зчитування повинна бути розміщена для випадкового доступу до вводу-виводу. Таким чином, послідовний доступ до даних є більш ефективним, ніж випадковий. Якщо ви розділяєте файли з різними шаблонами доступу, ви також мінімізуєте кількість зчитуваних рухів голови і тим самим оптимізуєте продуктивність зберігання.

Використовуйте системи RAID-10 для двійкових файлів, даних, файлів журналів та баз даних tempdb для найкращої продуктивності та доступності.

Розмір баз даних tempdb

Встановіть максимальний розмір файлу баз даних tempdb, щоб уникнути фрагментації диска.

Конфлікти можуть виникати на сторінках GAM, SGAM та PFS, коли SQL повинен писати на певні системні сторінки для виділення нових об'єктів. Засувки захищають (блокують) ці сторінки в пам'яті. На перевантаженому SQL-сервері може зайняти багато часу, щоб системний файл зафіксувався в базі даних tempdb, що призведе до збільшення часу запитів. Це явище називається "суперечка".

Емпіричне правило для створення файлів даних tempdb:

  • На 8 ядер
    • 8 файлів даних tempdb

Починаючи з SQL Server 2016, кількість ядер процесора, видимих ​​для операційної системи, визначається автоматично під час інсталяції. На основі цього SQL Server обчислює та налаштовує кількість файлів tempdb, необхідних для оптимальної роботи. Це суттєве покращення порівняно з попередніми версіями - похвала для Microsoft!

Конфігурація сховища

  • Мінімальна пам’ять сервера
  • Макс. Пам’ять сервера
  • Макс. Кількість робочих потоків
  • Місце для створення індексу
  • Мінімальна робоча пам’ять на запит

Мінімальна пам’ять сервера

З опцією "Мін. Серверна пам’ять »ви можете визначити мінімальний обсяг пам’яті для екземпляра SQL Server. Оскільки SQL Server є справжньою свинею пам'яті, використовуючи кожну доступну пам'ять RAM, цей параметр зазвичай використовується, коли операційна система вимагає від SQL Server занадто багато пам'яті. Однак технології віртуалізації роблять цей параметр більш важливим.

Макс. Пам’ять сервера

З опцією “Макс. Пам'ять сервера ”, встановлено максимальну пам’ять для екземпляра SQL Server. Це особливо важливо, коли крім SQL Server працюють інші програми, і ви хочете переконатися, що для них достатньо пам'яті.

Деякі програми просто використовують пам'ять, доступну під час запуску, і не потребують більше оперативної пам'яті. Навіть якби це було необхідно. Ось варіант «Макс. Серверна пам’ять ”вступає в дію.

У кластері або фермі SQL Server може бути кілька екземплярів SQL Server, які змагаються за ресурси. Встановивши обмеження пам’яті для кожного екземпляра SQL Server, ви уникаєте цієї конкуренції за оперативну пам’ять та забезпечуєте оптимальну роботу.

Не забувайте залишати принаймні 4-6 ГБ оперативної пам'яті для операційної системи, щоб запобігти проблемам із продуктивністю.

Макс. Кількість робочих потоків

Цей параметр використовується для оптимізації продуктивності, коли до SQL-сервера підключена велика кількість клієнтів. Зазвичай для кожного запиту запиту створюється окремий потік операційної системи. Однак використання одного потоку на запит запиту для сотень підключень до сервера може споживати великі системні ресурси. Опція “Макс. Кількість робочих потоків »сприяє підвищенню продуктивності, оскільки SQL Server дозволяє створювати пули робочих потоків, які можуть обробляти більшу кількість запитів запитів.

Значення за замовчуванням - 0, що дозволяє SQL Server автоматично налаштовувати кількість робочих потоків під час запуску. Цей параметр підходить для більшості систем. "Макс. Кількість робочих потоків »- це вдосконалений варіант, який слід змінювати лише у співпраці з досвідченим адміністратором бази даних.

Коли слід налаштувати SQL Server на використання більше робочих потоків? Якщо середня довжина черги на модуль планування більше 1, може мати сенс збільшити кількість потоків - але лише в тому випадку, якщо навантаження не пов'язане з процесором або в іншому випадку тривалі очікування. Якщо будь-який з них відповідає дійсності, немає сенсу додавати подальші потоки, оскільки вони також опиняться в черзі.

Місце для створення індексу

Це також вдосконалений варіант, який зазвичай не слід змінювати. Це контролює максимальну кількість оперативної пам'яті, виділеної для створення індексів. Значенням за замовчуванням для цього параметра є 0, що означає, що SQL Server автоматично налаштує цей параметр. Однак, якщо у вас виникають труднощі з побудовою індексу, ви можете збільшити це значення.

Мінімальна робоча пам’ять на запит

Коли запит виконується, SQL Server намагається виділити для нього оптимальний обсяг пам'яті. За замовчуванням мінімум 1024 КБ для кожного запиту. Рекомендуємо залишити за замовчуванням значення 0, щоб SQL Server міг динамічно керувати пам'яттю, виділеною для створення індексу. Однак якщо SQL Server має більше оперативної пам'яті, ніж це необхідно для ефективного виконання програми, продуктивність деяких запитів може бути збільшена за рахунок збільшення кількості. Поки на сервері є вільна пам’ять, яка не використовується SQL Server, іншими програмами або операційною системою, збільшення значення може покращити загальну продуктивність SQL Server. Однак, якщо вільної оперативної пам'яті немає, дія впливає досить негативно на загальну продуктивність.

Конфігурація процесора

Гіперпоточність

Гіперпотоковість - це спеціальна реалізація одночасної багатопоточності (SMT) в процесорах Intel для поліпшення розпаралелювання обчислень (багатозадачність) у мікропроцесорах x86. Апаратне забезпечення, яке використовує гіперпотоки, змушує логічні процесори гіперпотоків здаватися операційній системі як фізичні процесори. Потім SQL Server виявляє фізичні процесори, які вказує операційна система. Таким чином ви можете скористатися перевагами процесорів Hypherthreading.

Єдина принада полягає в тому, що кожна версія SQL Server має власний обмежувальний обсяг обчислювальної техніки.

NUMA (неоднорідний доступ до пам'яті)

NUMA - це метод оптимізації доступу до пам'яті. І з їх допомогою можна збільшити швидкість процесора, не збільшуючи коефіцієнт використання шини процесора. SQL Server підтримує NUMA і добре працює на апаратному забезпеченні NUMA, не вимагаючи жодної спеціальної конфігурації.

Приналежність до процесора

Якщо у вас немає проблем із продуктивністю, навряд чи вам коли-небудь доведеться змінювати налаштування асоціації процесора за замовчуванням. І все-таки варто знати про це більше.

SQL Server підтримує спорідненість процесора за допомогою двох варіантів маски:

  • Маска спорідненості (також відома як маска спорідненості процесора)
  • Спорідненість до введення/виводу

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

Параметр схожості вводу-виводу прив'язує введення-виведення диска SQL Server до певної підмножини процесорів. В середовищах обробки онлайн-транзакцій (OLTP) це вдосконалення може покращити продуктивність потоків SQL Server, які викликають операції вводу-виводу.

Примітка: Спорідненість обладнання до окремих дисків або контролерів дисків не підтримується.

Макс. Ступінь паралельності (MAXDOP)

За замовчуванням SQL Server використовує всі доступні процесори для виконання запитів. Хоча це вигідно для великих запитів, воно може вплинути на продуктивність та паралельність. Кращий підхід - обмежити паралельність кількістю фізичних ядер у сокеті процесора. Наприклад, для MAXDOP слід встановити значення 4 на SQL-сервері з двома сокетами фізичного процесора з чотирма ядрами, незалежно від функції гіперпотоків. MAXDOP не може визначити, який процесор використовується. Швидше, це обмежує максимальну кількість процесорів, які можна використовувати для одного запиту.

Поріг витрат на паралельність

Значення за замовчуванням для цього параметра - 5. Оптимізатор запитів використовує поріг витрат, щоб визначити, чи є сенс створювати паралельні плани запитів. 5 - це дуже низьке значення, яке підходить лише для чистих OLTP-додатків (сюди, до речі, відноситься і DatAdvantage).

Для систем, що не належать до OLTP, ми рекомендуємо спочатку встановити значення приблизно 50 і відрегулювати його за необхідності. Вам обов’язково слід коригувати значення для критичних запитів у програмі.

Інші важливі налаштування

Миттєва ініціалізація файлу

Якщо ви надасте SQL Server привілей Windows «Виконувати завдання з обслуговування томів», ви отримаєте кращу продуктивність при зростанні файлів даних.

Як правило, Windows пише багато нулів, як тільки користувачеві потрібен простір. Коли ви створюєте файл розміром 1 МБ, Windows записує на диск 1 МБ нулів для ініціалізації файлу. Якщо SQL Server має привілеї «Виконання завдань обслуговування обсягу», він пропонує Windows позначити необхідний простір як зайнятий і негайно повернути його до SQL Server. Це дозволяє швидше збільшувати файли.

Стиснення резервної копії

Починаючи з SQL Server 2008r2, стиснення резервної копії можна активувати за допомогою прапорця.

Це означає, що резервні копії вимагають менше місця для зберігання, займають менше часу і навіть швидше відновлюються. Вам обов’язково слід використовувати цей параметр.

Виділене підключення віддаленого адміністратора

Цей параметр потрібен лише в тому випадку, якщо з SQL Server щось не так.

Коли ви отримуєте доступ через спеціальне підключення адміністратора (ЦАП), SQL Server забезпечує виділене підключення, планувальник процесора та пам’ять. Віддалене усунення несправностей екземпляра SQL Server, який постійно використовує 100% процесора, набагато простіший, якщо у вас є виділені ресурси! Ви повинні бути підключені до SQL Server фізично через консоль або віддалено через RDP, щоб використовувати віддалений ЦАП. Вам обов’язково слід також скористатися цим налаштуванням. Після активації ви можете забути про це знову!

Висновок

SQL Server забезпечує продуктивність та масштабованість, необхідні для підтримки додатків виробничих баз даних, за умови дотримання найкращих практик.
У нашому наступному дописі в блозі ми розглянемо найкращі методи роботи з SQL Server у віртуалізованих середовищах.