Най-добри практики за SQL Server, част 1 Конфигурация
Начало »Най-добри практики за SQL Server, Част 1: Конфигурация
Четенето на ръководства за най-добри практики за Microsoft SQL Server може да бъде доста скучна работа. След като се преборихме с технически ръководства, ръководства за най-добри практики, статии на TechNet и текстове в блогове от SQL експерти, стигнахме до заключението, че лесен за разбиране текст по тази тема би бил полезен.
Нашата цел не е да навлизаме във всяка настройка, а да обясним няколко неща, които ще ви помогнат да настроите и да изпитате проблеми с производителността.
Споделени спрямо специални екземпляри
Ако приложението използва голям брой схеми/съхранени процедури, това потенциално може да влоши производителността на приложенията, използвайки същия екземпляр. Наличните ресурси могат да бъдат разделени или блокирани. Това може да доведе до забавяне на други базирани на база данни приложения на споделения екземпляр на SQL Server. Поправянето на проблеми с производителността може да бъде скучна работа, защото трябва да разберете кой екземпляр причинява проблема. И това често не е толкова лесно.
Начинът, по който компаниите отговарят на този въпрос, обикновено се претегля спрямо разходите за операционни системи и SQL лицензи. Ако производителността на приложението е основен приоритет, силно се препоръчва специален екземпляр.
Microsoft не продава лицензи за SQL Server за сървъри по екземпляр, а по ядро. Поради съображения за разходи администраторите са склонни да инсталират толкова екземпляри на SQL Server, колкото сървърът може да обработи, което може да доведе до огромни проблеми с производителността в дългосрочен план.
Следователно, когато е възможно, трябва да изберете специални SQL екземпляри.
Разделяне на SQL файлове на различни носители на данни
SQL Server използва много различни I/O модели за четене на данни и регистрационни файлове. Достъпът до файловете с данни обикновено е случаен, последователно до файловете на регистрите на транзакциите. При въртящи се твърди дискове четящата глава трябва да бъде позиционирана за произволен вход/изход. Следователно последователният достъп до данни е по-ефективен от случаен. Ако разделяте файлове с различни модели на достъп, вие също така минимизирате броя на прочетените движения на главата и по този начин оптимизирате производителността на съхранението.
Използвайте 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 и искате да се уверите, че за тях има достатъчно памет.
Някои приложения просто използват паметта, налична при стартиране, и не изискват повече RAM. Дори и да е необходимо. Тук идва опцията „Макс. Сървърна памет ”влиза в игра.
В клъстер или ферма на SQL Server може да има няколко екземпляра на SQL Server, които се конкурират за ресурси. Чрез задаване на ограничение на паметта за всеки екземпляр на SQL Server, вие избягвате тази конкуренция за RAM и гарантирате оптимална производителност.
Не забравяйте да оставите поне 4-6 GB RAM за операционната система, за да предотвратите проблеми с производителността.
Максимален брой работни нишки
Тази опция се използва за оптимизиране на производителността, когато голям брой клиенти са свързани към SQL сървъра. Обикновено за всяка заявка за заявка се създава отделна нишка на операционната система. Използването на една нишка на заявка за заявка за стотици връзки към сървъра може да консумира големи системни ресурси. Опцията „Макс. Брой работни нишки “спомага за подобряване на производителността, защото SQL Server ви позволява да създавате пулове от работни нишки, които могат да обработват по-голям брой заявки за заявки.
Стойността по подразбиране е 0, което позволява на SQL Server автоматично да конфигурира броя работни нишки при стартиране. Тази настройка е подходяща за повечето системи. "Макс. Брой работни нишки ”е усъвършенствана опция и трябва да се променя само в сътрудничество с опитен администратор на база данни.
Кога трябва да конфигурирате SQL Server да използва повече работни нишки? Ако средната дължина на опашката за модул за планиране е повече от 1, може да има смисъл да се увеличи броят на нишките, но само ако натоварването не е обвързано с процесора или има други времена за изчакване. Ако някое от двете е вярно, няма смисъл да добавяте допълнителни нишки, защото те също биха попаднали в опашката.
Хранилище за създаване на индекс
Това също е усъвършенствана опция, която обикновено не трябва да се променя. Това контролира максималното количество RAM, разпределено за създаване на индекси. Стойността по подразбиране за тази опция е 0, което означава, че SQL Server автоматично ще конфигурира тази настройка. Ако обаче имате затруднения с изграждането на индекса, можете да увеличите тази стойност.
Минимална работна памет на заявка
Когато се изпълни заявка, SQL Server се опитва да разпредели оптималното количество памет за нея. По подразбиране има минимум 1024 KB за всяка заявка. Препоръчваме да оставите настройката по подразбиране на 0, така че SQL Server да може динамично да управлява паметта, разпределена за създаване на индекс. Ако обаче SQL Server има повече RAM, отколкото е необходимо за ефективно изпълнение на програмата, производителността на някои заявки може да бъде увеличена чрез увеличаване на броя. Докато на сървъра има свободна памет, която не се използва от SQL Server, други приложения или операционната система, увеличаването на стойността може да подобри общата производителност на SQL Server. Ако обаче няма налична безплатна RAM, действието има доста негативен ефект върху цялостната производителност.
Конфигурация на процесора
Hyperthreading
Hyperthreading е специална реализация на едновременна многопоточност (SMT) в процесори на Intel за подобряване на паралелизацията на изчисленията (многозадачност) в x86 микропроцесори. Хардуерът, който използва хиперпотоци, кара логическите процесори за хиперпотоци да изглеждат като физически процесори за операционната система. След това SQL Server открива физическите процесори, които операционната система посочва. По този начин можете да се възползвате от процесорите Hypherthreading.
Единствената уловка е, че всяка версия на SQL Server има свой собствен лимит на изчислителен капацитет.
NUMA (неравномерен достъп до паметта)
NUMA е метод за оптимизиране на достъпа до паметта. И с тяхна помощ може да се увеличи скоростта на процесора, без да се увеличава използването на процесорната шина. SQL Server поддържа NUMA и работи добре на NUMA хардуер, без да изисква специална конфигурация.
Принадлежност към процесора
Освен ако нямате проблеми с производителността, е малко вероятно да се наложи някога да промените настройката за асоцииране на процесора по подразбиране. И все пак си струва да знаете повече за това.
SQL Server поддържа афинитет на процесора чрез две опции за маска:
- Маска за афинитет (известна също като маска за афинитет на процесора)
- I/O афинитет
SQL Server използва всички CPU, налични в операционната система. Модулите за планиране са създадени за всички процесори, за да се използват оптимално ресурсите. При многозадачност операционната система или други приложения на SQL сървъра могат да преместват нишки на процеса между процесорите. Тъй като SQL Server използва големи количества ресурси, това може да повлияе на производителността. За да се сведе до минимум този ефект, процесорите могат да бъдат конфигурирани така, че натоварването на SQL Server да се присвоява на предварително избрана група процесори. Това е възможно чрез маската за афинитет на процесора.
Опцията за афинитет на I/O обвързва I/O дисковете на SQL Server към конкретна подгрупа от процесори. В среди за онлайн обработка на транзакции (OLTP) това подобрение може да подобри производителността на нишките на SQL Server, които причиняват I/O операции.
Забележка: Хардуерният афинитет към отделни дискове или дискови контролери не се поддържа.
Максимална степен на паралелизъм (MAXDOP)
По подразбиране SQL Server използва всички налични процесори за изпълнение на заявки. Въпреки че това е полезно за големи заявки, това може да повлияе на производителността и паралелността. По-добрият подход е да се ограничи паралелизмът до броя на физическите ядра в процесорен сокет. Например, MAXDOP трябва да бъде зададен на 4 на SQL сървър с два физически процесорни гнезда с по четири ядра, независимо от функцията за хипернизоване. MAXDOP не може да определи кой процесор се използва. По-скоро ограничава максималния брой процесори, които могат да се използват за една заявка.
Праг на разходите за едновременност
Стойността по подразбиране за тази опция е 5. Оптимизаторът на заявки използва прага на разходите, за да определи дали има смисъл да се създават паралелни планове за заявки. 5 е много ниска стойност, която е подходяща само за чисти OLTP приложения (между другото включва DatAdvantage).
За системи, които не са OLTP, препоръчваме първо да зададете стойността на около 50 и да я коригирате според изискванията. Определено трябва да коригирате стойността за критични заявки в дадено приложение.
Други важни настройки
Незабавна инициализация на файлове
Ако дадете на SQL Server привилегията „Извършване на задачи за поддръжка на тома“ на Windows, ще имате по-добра производителност при отглеждане на файлове с данни.
Обикновено Windows записва много нули веднага щом потребителят има нужда от място. Когато създавате 1MB файл, Windows записва 1MB нули на диска, за да инициализира файла. Ако SQL Server има привилегията „Perform Volume Maintenance Tasks“, той подканва Windows да маркира необходимото място като заето и да го върне незабавно на SQL Server. Това позволява по-бързо увеличаване на файловете.
Резервно компресиране
От SQL Server 2008r2, резервното компресиране може да бъде активирано с помощта на квадратче за отметка.
В резултат на това архивните копия изискват по-малко място за съхранение, отнемат по-малко време и се възстановяват още по-бързо. Определено трябва да използвате тази настройка.
Специална връзка за отдалечен администратор
Тази настройка ви е необходима само ако нещо не е наред със SQL Server.
Когато осъществявате достъп чрез специална административна връзка (DAC), SQL Server предоставя специална връзка, CPU планировщик и памет. Отдалеченото отстраняване на неизправности на екземпляр на SQL Server, който постоянно използва 100% процесор, е много по-лесно, когато имате специални ресурси! Трябва да сте свързани към SQL Server физически чрез конзолата или дистанционно чрез RDP, за да използвате отдалечен ЦАП. Определено трябва да използвате и тази настройка. Веднъж активиран, можете да забравите за него отново!
Заключение
SQL Server осигурява производителността и мащабируемостта, необходими за поддръжка на приложения на производствени бази данни, при условие че се спазват най-добрите практики.
В следващата ни публикация в блога ще разгледаме най-добрите практики за SQL Server във виртуализирани среди.