- 1.Защита SQL-сервера
- 2.Балансировка нагрузкі дыскавай падсістэмы
- 3.Управление памяццю
- 4.Ожидания ў сэрвэры
- 5.Построение індэксаў
- 6.Оптимизация цяжкіх запытаў.
- 7. Агульнае зніжэнне нагрузкі на SQL
( SQL ) SQL (2010 год)
Гэтая нататка не прэтэндуе на паўнату даследаванні тэмы прадукцыйнасці SQL-сервера, у любым выпадку добра б прачытаць Лічыльнікі прадукцыйнасці SQL Server і Windows , Сем найбольш карысных лічыльнікаў эфектыўнасці , Маніторынг эфектыўнасці MS SQL Server. практычныя рэкамендацыі , Выкарыстанне табліцы sysperfinfo для вырашэньня праблемаў SQL Server . Вы лёгка знойдзеце дзесяткі падобных артыкулаў на рускай мове і тысячы - на англійскай. Ёсць мноства карысных артыкулаў у BOL - Аптымізацыя прадукцыйнасці базы дадзеных tempdb
Я проста распавяду як я ужыў ўсю гэтую карысную інфармацыю на практыцы для аптымізацыі кампутера з SQL-серверам, якія абслугоўваюць партал, на які ходзіць чвэрць мільёна посетилей у суткі (верагодна ў наступны сезон будзе яшчэ разы ў два больш). гэты кампутер мае ў аснове сваёй пякельны SAS-кантролер, які абслугоўвае больш за 10 скрынь, набітых дыскамі. Кожны бачыцца як адзін SCSI-дыск. Акрамя SAS-кантролера кампутер мае 16ГБ памяці, два четырехядерных ксеона і некалькі звычайных дыскаў.
1.Защита SQL-сервера
Першая задача, якую я вырашыў - гэта абарона гэтага кампутера ад несанкцыянаванага доступу з сеткі. Зрабіў я гэта звычайным для сябе спосабам - запхнуў ўнутр гэтага кампутера апаратны фаервол.
2.Балансировка нагрузкі дыскавай падсістэмы
Балансаванне нагрузкі SAS-кантролера я забяспечыў сплитированием ўсіх асноўных баз - гэтую тэхналогію я апісваў на сваім Хамяк - Cекционирование графікі пры SQL-захоўванні. . У выніку кожная сур'ёзная база ў мяне ляжыць на мноства дыскаў:
Акрамя таго, усе базы разнесеныя па розных дысках, бэкапу, індэксы і гэтак далей. Базы з запісам працуюць нават не ў Simple, а ў BulkLogged. У выніку асноўны дыск, які працуе пры працы SQL-сервера - гэта дыск, на якім ляжыць TempDB. Менавіта ён тузаецца на гэтым відэа-роліку - калі рэзка ўзрастае памер дыскавай чарзе.
Зразумела, што на такім загружаным дыску наогул больш нічога не павінна быць размешчана. У прынцыпе MS рэкамендуе ствараць адзін файл TempDB для кожнага ЦП на сервер. І паколькі дыск з tempdb - гэта адзіны рэальна загружаны дыск у мяне, я ствару tempdb на 8 дысках.
3.Управление памяццю
SQL-cерверы я паставіў у рэжым AWE, нягледзячы на тое, што ў мяне ўсё 64-х разраднае - і виндузня і сервер. Першае пытанне, у якім я хацеў пераканаецца, што SQL-серверу досыць аператыўнай памяці. На жаль, глюкавый микрасофт ня здолеў нават добится каб у яго нармальна працаваў Windows Task Manager, хто заклікаў па Ctrl-Alt-Del. На жаль, ён паказвае зусім неадэкватныя лічбы.
Ёсць яшчэ і другі варыянт тых жа лічбаў, якія паказвае люхта select * from master.dbo.sysperfinfo.
Ёсць і трэці варыянт тых жа лічбаў - лічыльнікі Perfomance. Усе лічбы розныя. Якім лічбам верыць - незразумела, але быццам бы микрософтовцы сцвярджаюць, што лічыльнікі оснаски Pefomance ў іх атрымаліся менш глюкавые, чым астатнія:
Калі паверыць лічыльніках Performance, то можна ўбачыць што серверу ў мяне даступна 14GB памяці (што лагічна ў-агульным), але пажадаў ён зь іх заняць 5,2GB. Гэта яўная неадэкватнасць микрософтовского алгарытму - мець магчымасць заняць АЗП кэшам з дадзенымі, але не пользоватся АЗП. А пастаянна падпампоўваць дадзеныя з дыскаў. У Оракла ёсць версія SQL-сервера, якая наогул умее не тузаць дыскі - проста счытвае ўсе дадзеныя ў АЗП. Зразумела ніякія індэксы, фактары запаўнення, сплитирование дадзеных і іншыя хітрыкі проста не патрабуюцца ў такім выпадку - доступ да дадзеных у АЗП адбываецца імгненна, не менш чым у мільён разоў хутчэй доступу да дыскаў (нават прыкручанага да ацкому SAS-кантролер).
Акрамя гэтага незразумелага трафіку з дыскамі мяне таксама бянтэжыць у микрософтовском алгарытме наяўнасць велізарных значэнняў PageFault - гэты лічыльнік азначае, што патрабаванай старонкі з дадзенымі не аказалася ў АЗП. Але ў цэлым, паколькі я пераканаўся, што памяці ў мяне істотна больш, чым SQL-сервер пажадаў заняць - на гэтым я палічыў і гэты этап аптымізацыі завершаным.
4.Ожидания ў сэрвэры
Акрамя памяці, звычайна аналізуецца, што чакае сервер. У маім выпадку відаць, што ў сервера няма ніякіх асаблівых вузкіх месцаў. Але ў гэтым трэба было пераканаецца, хоць бы для таго, каб перайсці да наступнага этапу аптымізацыі - пабудове індэксаў . Бо трасу трэба пісаць у сетку (на іншай SQL) - каб не паўплываць на оптимизируемый SQL. Для гэтага, як мінімум трэба пераканаецца, што сетка не з'яўляецца самым вузкім месцам сервера.
5.Построение індэксаў
Ключом да эфектыўнай працы SQL з'яўляецца нармальныя індэксы. Яны дазваляюць адразу звярнуцца да патрэбнай запісу, а не шляхам перабору запісаў на дыску. SQL 2005. Экзамен 70-431. Стар 121. "Каб знайсці радок у табліцы, якая змяшчае 2,5 мільёна радкоў, SQL Server павінен паглядзець ўсяго тры старонкі дадзеных. І толькі калі ў табліцы стане больш за 300 мільёнаў радкоў, SQL Server прыйдзецца праглядаць чатыры старонкі для пошуку патрэбнай радка." Таму трэба для пачатку выканаць усе рэкамендацыі Tuning Advisor'a.
Каб добится эфектыўнай работы індэксаў - для пачатку трэба правільна спраектаваць базу. Груба кажучы - максімум спасылкавых адносін, што дазваляе аптымізатар адразу чапляць патрэбныя звязаныя дадзеныя. Для сайта, які круціцца на гэтым сэрвэры - я зрабіў дзясятак такіх дыяграм.
Пра індэксы трэба памятаць і на этапе SqL-праграмавання. Напрыклад, пры запісе індэксы запавольваюць працу (іх трэба перабудоўваць). Таму на момант масавых запісаў у кодзе працэдур іх трэба адключаць. А таблы куды вядзецца ўсякая статыстыка партала - там індэксы не патрэбныя наогул, бо запіс туды ідзе заўсёды, а запыт зробіць адзін раз у месяц адміністратар. Зрэшты пытанняў эфектыўнага SQL-праграмавання шмат, не толькі індэксы - напрыклад хинты WITH (NOLOCK) - чым іх больш, тым усё шустрэй працуе.
Індэксы маюць патрэбу і ў правільнай эксплуатацыі. Пра абнаўленне статыстыкі індэксаў - можна пачытаць тут Выкарыстанне статыстыкі аптымізатарам запытаў Microsoft SQL Server 2005
6.Оптимизация цяжкіх запытаў.
Для аптымізацыі асобных цяжкіх запытаў можна ўжыць убудаваныя сродкі MS. Але ёсць і асобныя ацкие прогі для гэтага, напрыклад SQL Shot . Але запыты, якія нельга аптымізаваць, скажам да некалькіх секунд - ад іх трэба адмовіцца наогул. Як гэта зрабіць?
Я раблю кэшаванне такіх цяжкіх запытаў на самым верхнім прыкладным узроўні. Для гэтага ўсе цяжкія адборы проста запускаюцца ў заданні, а юзэр з сайта проста атрымлівае не цалкам актуальны, але мгновернный вынік. Такім чынам у маіх праектах усе цяжкія запыты выведзеныя ў джоб і на рэактыўнасць партала не ўплываюць.
Вось прыклад. на зборцы Зборка для працы з дадзенымі стандартных ASP.NET-профіляў на ўзроўні SQL зробленая вось такая працэдура, якая выклікаецца ў заданні . Такім чынам карыстальнік сайта імгненна атрымлівае вынік запыту, які лічыцца гадзінамі.
7. Агульнае зніжэнне нагрузкі на SQL
Вось некалькі маіх любімых метадаў:
1. Вынас ўсіх што можна запытаў у Application_start і Session_start. Напрыклад, вось фрагмент global.asax . Як бачыце, патрэбныя шмат разоў рекордсеты мне стала вядома яшчэ пры старце сесіі. А на старонках я проста дастаю рекордсеты з памяці без звароту да SQL.
2. Калі вельмі шмат курсорных запытаў RS.Movenext ёсць сэнс атрымаць увесь рекорсет адразу з FOR XML. Прадукцыйнасць тут павялічыцца не ў кожнай сітуацыі, але гэта таксама можа быць выхадам па зніжэнні нагрузкі на SQL.
3. Яшчэ метад зніжэння нагрузкі - сгенерить заданнем SQL гатовыя html-фрагменты з комбобоксами і ўключыць гэты гатовы фрагмент асповской дырэктывай <! - # include (або литералом).
4. Перанос усіх тыпавых заданняў сайта ( Выкананне перыядычных задач у ASP.NET ) На начны час.
Пасля налады прадукцыйнасці SQL-сервера ну забудзьцеся наладзіць бекапирование дадзеных .