Online-курс "Асновы APM": Аптымізацыя прадукцыйнасці SQL сервера

Аптымізацыя буфернага кэша

Чаму так важна аптымізаваць кэш? Ды таму, што пры выкананні любых запытаў SQL сервер чытае дадзеныя не з дыскавай падсістэмы, а з буфернага кэша. Нават калі неабходных дадзеных там няма, спачатку яны будуць загружаныя ў кэш, а ўжо потым SQL іх адтуль возьме, а ўвесь гэты час запыт будзе чакаць.

Абнаўлення і выдалення таксама вырабляюцца ў кэшы, а нейкі час праз іх выгрузяць на дыск. Такі механізм дазваляе SQL сервера аптымізаваць аперацыі ўводу / высновы:

• Некалькі старонак могуць быць прачытаныя або запісаныя за адзін зварот да дыска.
• Сервер можа заўважыць, што для пэўных аперацый запытваюцца паслядоўныя старонкі, і выказаць здагадку, што пасля запыту адной з іх неўзабаве рушыць услед зварот да суседніх, і загадзя загрузіць іх у буфер.

Ёсць два індыкатара працы буфернага кэша:

MSSQL $ Instance: Buffer Manager \ Buffer cache hit ratio - суадносіны запытаных старонак, якія былі знойдзены ў кэшы, да старонак, якіх там не было (і якія давялося загрузіць з дыска). Чым больш гэтыя суадносіны, тым больш эфектыўны кэш. Але варта ўлічваць, што нават пры вялікім суадносінах можа прысутнічаць эфект перагрузкі кэша (cache thrashing).

MSSQL $ Instance: Buffer Manager \ Page Life Expectancy - час жыцця старонак у кэшы. Лічыцца, што калі гэты паказчык больш пяці хвілін, то гэта нармальна. Калі значэнне ніжэй, то альбо не хапае памяці, альбо прысутнічае перагрузка кэша.

Перагрузка кэша часта адбываецца пры сканаванні вялікі табліцы або азначніка. Прычына простая: кожная старонка скана павінна прайсці праз кэш, і шмат карысных старонак можа быць выгружаны, каб змясціліся старонкі, да якіх больш за адзін раз ніхто не абярнуўся. Гэта выклікае сур'ёзную нагрузку на аперацыі чытання і запісы, бо карысныя старонкі зноў будуць загружацца з дыска ў кэш пры чарговым запыце.

Калі пасля аналізу лічыльнікаў з'явілася падазрэнне на перагрузку кэша, можна паспрабаваць аптымізаваць працу з кэшам. Каб зразумець, якія табліцы і індэксы займаюць шмат месца ў кэшы, можна звярнуцца да sys.dm_os_buffer_descriptors.

SELECT o.name, i.name, bd. *
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.allocation_units a
ON bd.allocation_unit_id = a.allocation_unit_id
INNER JOIN
sys.partitions p
ON (a.container_id = p.hobt_id AND a.type IN (1, 3))
OR (a.container_id = p.partition_id AND a.type = 2)
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.indexes i
ON p.object_id = i.object_id AND p.index_id = i.index_id

Пасля гэтага прааналізуйце, якія з іх выклікаюць максімальную нагрузку на абмен старонкамі з дыскавай падсістэмай, і што ў базе і запытах можна памяняць для паляпшэння сітуацыі.

Пошук «дрэнных» індэксаў

Для вызначэння карыснасці індэксаў трэба перш зразумець, як сервер выкарыстоўвае існуючыя індэксы. sys.dm_db_index_operational_stats змяшчае інфармацыю аб нізкаўзроўневых аперацыях уводу / высновы, блакаваннях, зваротах для кожнага азначніка. Гэтую інфармацыю можна выкарыстоўваць для адказу на пытанні:

• Ці маюцца папулярныя індэксы? Ці ёсць барацьба за іх? Ці ёсць чаканне блакаванняў?
• Ці маецца індэкс, які выкарыстоўваецца неэфектыўна? Якія індэксы прыводзяць да вузкага месцы ў працы з уводам / высновай? page_io_latch_wait_ms пакажа, ці былі чакання загрузкі старонак індэкса ў буферны кэш - добры індыкатар сканавання індэксаў.
• Як прымяняюцца індэксы, якія шаблоны доступу выкарыстоўваюцца?

А з дапамогай sys.dm_db_index_usage_stats можна даведацца, колькі і якіх аперацый ўжывалася да індэксу і калі апошні раз была ужыты пэўны тып аперацыі. Гэта дапаможа адказаць на пытанні:

• Як карыстальнікі працуюць з індэксам? Якія аперацыі найбольш часта выкарыстоўваюцца?
• Як падтрымліваецца і наколькі часта абнаўляецца індэкс?
• Калі індэкс выкарыстоўваўся ў апошні раз і для чаго?

З дапамогай розных запытаў можна атрымліваць і ўдакладняць інфармацыю пра індэксы і ў канчатковым выніку атрымаць кандыдатаў на аптымізацыю. Прычым аптымізаваць можна і запыты, якія выкарыстоўваюць індэкс - пачынаючы з SQL сервера 2005 г., гэтая інфармацыя даступная для любога азначніка.

Ёсць розныя стратэгіі аптымізацыі - правільная архітэктура, аб'яднанне індэксаў, змена падтрымання. Гэта адзін з самых складаных, але і самых прадуктыўных аспектаў аптымізацыі, і значна больш падрабязна асветлены на адпаведных сайтах і ў web-кастах.

Нельга расказаць усё і адразу, але прадстаўленая інфармацыя павінна даць добры старт любым ініцыятывам у пытаннях аптымізацыі прадукцыйнасці SQL сервера.

Каманда ALG DevOps Team будзе ўдзячная за Вашы адказы на наступныя пытанні: