Cum să reglați Microsoft SQL Server pentru performanță

Pentru a-și păstra utilizatorii, orice aplicație sau site web trebuie să ruleze rapid. Pentru mediile critice de misiune, o întârziere de câteva milisecunde în obținerea informațiilor poate crea probleme mari. Pe măsură ce dimensiunile bazei de date cresc pe zi ce trece, trebuie să obținem datele cât mai repede posibil și să le scriem înapoi în baza de date cât mai repede posibil. Pentru a ne asigura că toate operațiunile se execută fără probleme, trebuie să ne reglam serverul de baze de date pentru performanță.

În acest articol voi descrie o procedură pas cu pas pentru reglarea performanței de bază pe unul dintre serverele de baze de date de top de pe piață: Microsoft SQL Server (SQL Server, pe scurt).

# 1 Găsirea vinovaților

Ca și în cazul oricărui alt software, trebuie să înțelegem că SQL Server este un program de calculator complex. Dacă avem o problemă cu el, trebuie să descoperim de ce nu funcționează așa cum ne așteptăm.

Din SQL Server trebuie să extragem și să împingem datele cât mai rapid și cât mai precis posibil. Dacă există probleme, câteva motive de bază și primele două lucruri de verificat sunt:

• Setările hardware și de instalare, care ar putea necesita corectare, deoarece nevoile SQL Server sunt specifice
• Dacă am furnizat codul T-SQL corect pentru implementarea SQL Server

Chiar dacă SQL Server este un software proprietar, Microsoft a oferit o mulțime de modalități de a-l înțelege și de a-l utiliza eficient.

Dacă hardware-ul este în regulă și instalarea a fost făcută corect, dar SQL Server încă rulează lent, atunci mai întâi trebuie să aflăm dacă există erori legate de software. Pentru a verifica ce se întâmplă, trebuie să observăm cum funcționează diferite fire. Acest lucru se realizează prin calcularea statisticilor de așteptare ale diferitelor fire. Serverul SQL folosește fire pentru fiecare solicitare a utilizatorului, iar firul nu este altceva decât un alt program din programul nostru complex numit SQL Server. Este important de remarcat faptul că acest thread nu este un fir de sistem de operare pe care este instalat serverul SQL; este legat de firul SQLOS, care este un pseudo sistem de operare pentru SQL Server.

Statisticile de așteptare pot fi calculate folosind sys.dm_os_wait_stats Dynamic Management View (DMV), care oferă informații suplimentare despre starea sa actuală. Există multe scripturi online pentru a interoga această vizualizare, dar preferatul meu este scriptul lui Paul Randal, deoarece este ușor de înțeles și are toți parametrii importanți pentru a observa statisticile de așteptare:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Când executăm acest script, trebuie să ne concentrăm pe rândurile de sus ale rezultatului, deoarece acestea sunt setate mai întâi și reprezintă tipul maxim de așteptare.

Trebuie să înțelegem tipurile de așteptare pentru a putea lua deciziile corecte. Pentru a afla despre diferitele tipuri de așteptare, putem accesa documentația Microsoft excelentă.

Să luăm un exemplu în care avem prea mult PAGEIOLATCH_XX . Aceasta înseamnă că un fir așteaptă citirile paginii de date de pe disc în buffer, care nu este altceva decât un bloc de memorie. Trebuie să fim siguri că înțelegem ce se întâmplă. Acest lucru nu înseamnă neapărat un subsistem I/O slab sau memorie insuficientă, iar creșterea subsistemului I/O și a memoriei va rezolva problema, dar doar temporar. Pentru a găsi o soluție permanentă, trebuie să vedem de ce se citesc atât de multe date de pe disc: Ce tipuri de comenzi SQL cauzează acest lucru? Citim prea multe date în loc să citim mai puține date folosind filtre, cum ar fi clauzele where ? Au loc prea multe citiri de date din cauza scanărilor de tabel sau scanări de index? Le putem converti în căutări de indici prin implementarea sau modificarea indecșilor existenți? Scriem interogări SQL care sunt înțelese greșit de SQL Optimizer (un alt program din programul nostru de server SQL)?

Trebuie să gândim din unghiuri diferite și să folosim diferite cazuri de testare pentru a găsi soluții. Fiecare dintre tipurile de așteptare de mai sus necesită o soluție diferită. Un administrator al bazei de date trebuie să le cerceteze temeinic înainte de a lua orice măsură. Dar de cele mai multe ori, găsirea de interogări T-SQL problematice și reglarea acestora va rezolva 60 până la 70 la sută din probleme.

# 2 Găsirea interogărilor problematice

După cum am menționat mai sus, primul lucru pe care îl putem face este să căutăm interogări problematice. Următorul cod T-SQL va găsi cele 20 de interogări cu cele mai proaste performanțe:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Trebuie să fim atenți cu rezultatele; chiar dacă o interogare poate avea un timp de rulare mediu maxim, dacă rulează o singură dată, efectul total asupra serverului este scăzut în comparație cu o interogare care are o durată medie de rulare medie și rulează de multe ori într-o zi.

# 3 Interogări de reglare fină

Reglarea fină a unei interogări T-SQL este un concept important. Lucrul fundamental de înțeles este cât de bine putem scrie interogări T-SQL și implementa indecși, astfel încât optimizatorul SQL să găsească un plan optimizat pentru a face ceea ce ne-am dorit să facă. Cu fiecare nouă lansare a SQL Server, obținem un optimizator mai sofisticat care ne va acoperi greșelile de scriere a interogărilor SQL neoptimizate și, de asemenea, va remedia orice erori legate de optimizatorul anterior. Dar, indiferent cât de inteligent ar fi optimizatorul, dacă nu îi putem spune ce dorim (prin scrierea unei interogări T-SQL adecvate), optimizatorul SQL nu își va putea face treaba.

SQL Server folosește algoritmi avansati de căutare și sortare. Dacă suntem buni la algoritmi de căutare și sortare, atunci de cele mai multe ori putem ghici de ce SQL Server ia anumite măsuri. Cea mai bună carte pentru a învăța mai multe și a înțelege astfel de algoritmi este Arta programarii pe computer de Donald Knuth.

Când examinăm interogările care trebuie ajustate, trebuie să folosim planul de execuție al acelor interogări, astfel încât să putem afla cum le interpretează serverul SQL.

Nu pot acoperi aici toate aspectele planului de execuție, dar la nivel de bază pot explica lucrurile pe care trebuie să le luăm în considerare.

• Mai întâi trebuie să aflăm care operatori preiau cea mai mare parte din costul interogării.
• Dacă operatorul preia multe costuri, trebuie să aflăm motivul. De cele mai multe ori, scanările vor costa mai mult decât caută. Trebuie să examinăm de ce are loc o anumită scanare (scanare tabelă sau scanare index) în loc de căutarea indexului. Putem rezolva această problemă prin implementarea indecșilor corespunzători pe coloanele tabelului, dar ca în cazul oricărui program complex, nu există o soluție fixă. De exemplu, dacă tabelul este mic, scanările sunt mai rapide decât cele căutate.
• Există aproximativ 78 de operatori, care reprezintă diferitele acțiuni și decizii ale planului de execuție SQL Server. Trebuie să le studiem în profunzime consultând documentația Microsoft, astfel încât să le putem înțelege mai bine și să luăm măsurile adecvate.

#4 Reutilizarea planului de execuție

Chiar dacă implementăm indecși corespunzători pe tabele și scriem cod T-SQL bun, dacă planul de execuție nu este reutilizat, vom avea probleme de performanță. După reglarea fină a interogărilor, trebuie să ne asigurăm că planul de execuție poate fi reutilizat atunci când este necesar. Majoritatea timpului CPU va fi cheltuit pentru calcularea planului de execuție care poate fi eliminat, dacă reutilizam planul.

Putem folosi interogarea de mai jos pentru a afla de câte ori este reutilizat planul de execuție, unde usecounts reprezintă de câte ori este reutilizat planul:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

Cea mai bună modalitate de a reutiliza planul de execuție este prin implementarea procedurilor memorate parametrizate. Când nu suntem în măsură să implementăm proceduri stocate, putem folosi sp_executesql , care poate fi folosit în schimb pentru a executa instrucțiuni T-SQL când singura modificare a instrucțiunilor SQL sunt valorile parametrilor. Cel mai probabil SQL Server va reutiliza planul de execuție pe care l-a generat în prima execuție.

Din nou, ca și în cazul oricărui program complex de calculator, nu există o soluție fixă. Uneori este mai bine să compilați planul din nou.

Să examinăm următoarele două exemple de interogări:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

Să presupunem că avem un index non-cluster pe coloana de name și jumătate din tabel are valoarea sri și câteva rânduri au pal în coloana de name . Pentru prima interogare, SQL Server va folosi scanarea tabelului deoarece jumătate din tabel are aceleași valori. Dar pentru a doua interogare, este mai bine să utilizați scanarea indexului, deoarece doar câteva rânduri au valoare pal .

Chiar dacă interogările sunt similare, același plan de execuție poate să nu fie o soluție bună. De cele mai multe ori va fi un caz diferit, așa că trebuie să analizăm totul cu atenție înainte de a decide. Dacă nu dorim să reutilizam planul de execuție, putem folosi întotdeauna opțiunea „recompilare” în procedurile stocate.

Rețineți că, chiar și după utilizarea procedurilor stocate sau sp_executesql , există momente în care planul de execuție nu va fi reutilizat. Sunt:

• Când indecșii utilizați de interogare se modifică sau sunt abandonați
• Când se modifică statisticile, structura sau schema unui tabel utilizat de interogare
• Când folosim opțiunea „recompilare”.
• Când există un număr mare de inserări, actualizări sau ștergeri
• Când amestecăm DDL și DML într-o singură interogare

# 5 Eliminarea indecșilor inutile

După reglarea fină a interogărilor, trebuie să verificăm modul în care sunt utilizați indecșii. Întreținerea indexului necesită o mulțime de CPU și I/O. De fiecare dată când inserăm date într-o bază de date, SQL Server trebuie să actualizeze indecșii, așa că este mai bine să-i eliminăm dacă nu sunt folosiți.

Serverul SQL ne oferă dm_db_index_usage_stats DMV pentru a găsi statistici de index. Când rulăm codul T-SQL de mai jos, obținem statistici de utilizare pentru diferiți indici. Dacă găsim indici care nu sunt folosiți deloc, sau folosiți rar, îi putem renunța pentru a câștiga performanță.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 Instalarea SQL Server și Configurarea bazei de date

La configurarea unei baze de date, trebuie să păstrăm datele și fișierele jurnal separat. Motivul principal pentru aceasta este că scrierea și accesarea fișierelor de date nu este secvențială, în timp ce scrierea și accesarea fișierelor jurnal este secvențială. Dacă le punem pe aceeași unitate, nu le putem folosi într-un mod optimizat.

Când achiziționăm Storage Area Network (SAN), un furnizor ne poate oferi câteva recomandări cu privire la modul de configurare, dar aceste informații nu sunt întotdeauna utile. Trebuie să avem o discuție detaliată cu oamenii noștri din hardware și rețele despre cum să păstrăm datele și fișierele jurnal separat și într-un mod optimizat.

#7 Nu supraîncărcați SQL Server

Sarcina principală a oricărui administrator de baze de date este să se asigure că serverul de producție funcționează fără probleme și deservește cât mai bine clienții. Pentru a face acest lucru, trebuie să menținem baze de date separate (dacă este posibil, pe mașini separate) pentru următoarele medii:

• Productie
• Dezvoltare
• Testare
• Analitic

Pentru o bază de date de producție avem nevoie de o bază de date cu modul de recuperare completă, iar pentru alte baze de date este suficient un mod simplu de recuperare.

Testarea pe o bază de date de producție va pune multă sarcină pe jurnalul de tranzacții, indexuri, CPU și I/O. De aceea trebuie să folosim baze de date separate pentru producție, dezvoltare, testare și analiză. Dacă este posibil, utilizați mașini separate pentru fiecare bază de date, deoarece va reduce sarcina CPU și I/O.

#8 Jurnalul tranzacțiilor, tempdb și memorie

Fișierul jurnal trebuie să aibă suficient spațiu liber pentru operațiuni normale, deoarece o operațiune de creștere automată a unui fișier jurnal necesită timp și ar putea forța alte operațiuni să aștepte până la finalizare. Pentru a afla dimensiunea fișierului jurnal pentru fiecare bază de date și cât de mult este utilizată, putem folosi DBCC SQLPERF(logspace) .

Cel mai bun mod de a configura tempdb este să îl puneți pe un disc separat. Trebuie să păstrăm dimensiunea inițială cât ne putem permite, deoarece atunci când ajunge la o situație de autogrow, performanța va scădea.

După cum am menționat anterior, trebuie să ne asigurăm că serverul SQL rulează pe o mașină separată, de preferință una fără nicio altă aplicație pe ea. Trebuie să păstrăm puțină memorie pentru sistemul de operare, plus ceva mai mult dacă face parte dintr-un cluster, așa că în cele mai multe cazuri ar trebui să fie în jur de 2 GB.

Concluzie:

Procedurile și sugestiile discutate aici sunt doar pentru reglarea de bază a performanței. Dacă urmăm acești pași, este posibil să obținem, în medie, o îmbunătățire a performanței cu aproximativ 40 până la 50 la sută. Pentru a face reglarea avansată a performanței SQL Server, ar trebui să analizăm mult mai profund fiecare dintre pașii tratați aici.

Citiți suplimentare pe blogul Toptal Engineering:

• Rezolvarea blocajelor cu indici și partiții SQL
• Ghid de migrare Oracle la SQL Server și SQL Server la Oracle