• Articles
• admin

Nedávno jsme pracovali na problémech s výkonem SQL Serveru pro některé z našich velkých zákazníků, kteří používají SQL server jako svou databázi. V tomto článku, shrnu to, co jsem se z procesu naučil, popisem některých kroků, které můžeme podniknout k problému s výkonem.

Zkontrolujte konfiguraci serveru SQL

ujistěte se, že databázový server je nakonfigurován s dostatkem zdrojů, jako je počet jader CPU a množství paměti.

Chcete-li zkontrolovat konfiguraci serveru, můžete otevřít zobrazení“ Systémové informace“:

pro kontrolu nastavení paměti SQL Serveru,

1. spusťte SQL Server Management Studio.
2. klepněte pravým tlačítkem myši na instanci databáze a vyberte „Vlastnosti“.
3. v rozbalovacím okně „Vlastnosti serveru“ klikněte na tabulku „paměť“.
4. zkontrolujte nastavení paměti.

ujistěte se, že je zapnutý režim Snapshot

ujistěte se, že je pro databázi zapnutý režim snapshot. Aby se zabránilo zablokování serveru SQL Server, měl by být zapnut příznak režimu snapshot. Spusťte následující dotaz a zkontrolujte, zda je příznak zapnutý:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'

pokud dotaz vrátí ‚1‘, je režim snímku již zapnutý. V opačném případě spusťte následující dotaz a zapněte jej.

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

související: Definování a implementace požadavků základní linie

zkontrolujte indexy databáze

zkontrolujte indexy databáze a ujistěte se, že chybí indexy. Spusťte následující dotaz a vypište všechny indexy v databázi (kredit: tsql-seznam všech indexů & sloupce indexu v SQL Server DB-přetečení zásobníku):

SELECT t.name TableName, col.name ColumnName, ind.name IndexNameFROM sys.indexes indINNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_idINNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_idINNER JOIN sys.tables t ON ind.object_id = t.object_idWHERE (ind.is_primary_key = 0 AND t.is_ms_shipped = 0)ORDER BY t.name, col.name, ind.name

Uložte výsledky dotazu do textového souboru se sloupci oddělenými tabulátory, aby mohly být později importovány do tabulkové aplikace.

Vyhněte se fragmentaci

ujistěte se, že indexy databáze nejsou fragmentovány. Indexy v databázi pomáhají urychlit databázový dotaz. Ale když se roztříští, dotazy by mohly běžet opravdu pomalu. V rámci údržby databáze by měly být indexy pravidelně organizovány nebo znovu vytvářeny.

Spusťte následující dotaz a zkontrolujte procento fragmentace pro všechny indexy v databázi (kredit: jak zkontrolovat fragmentaci indexu na indexech v databázi):

SELECT dbschemas.Jama Software AS 'Schema', dbtables.Jama Software AS 'Table', dbindexes.Jama Software AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, dbindexes.fill_factorFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables ON dbtables. = indexstats.INNER JOIN sys.schemas dbschemas ON dbtables. = dbschemas.INNER JOIN sys.indexes AS dbindexes ON dbindexes. = indexstats. AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent DESC

zde je ukázkový výsledek dotazu pro databázi s vysoce fragmentovanými indexy:

pro indexy s počtem stránek větším než 1000 by měl být“ avg_framentation_in_percent “ udržován pod 10%. Na malých indexech to moc nezáleží. Indexy s fragmentačními procenty mezi 5% a 30% by mohly být zpravidla reorganizovány a indexy s fragmentačními procenty většími než 30% by měly být přestavěny.

Chcete-li znovu uspořádat jeden index:

ALTER INDEX REORGANIZE

Chcete-li znovu vytvořit jeden index:

ALTER INDEX REBUILD WITH (ONLINE = ON)

můžete také reorganizovat nebo znovu vytvořit index pomocí SQL Server Management Studio kliknutím pravým tlačítkem myši na index, který chcete znovu vytvořit nebo reorganizovat.

pokud je v databázi mnoho defragmentovaných indexů, můžete spustit následující dotaz a znovu je všechny Obnovit (kredit: SQL SERVER-2008 -2005-znovu sestavit každý Index všech tabulek databáze-znovu vytvořit Index pomocí Fillfactoru). Vezměte prosím na vědomí, že dotaz může chvíli běžet pro velkou databázi. Doporučuje se spustit dotaz, když je databáze nečinná nebo offline.

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME()+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'PRINT @sqlEXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO

zvažte nastavení úlohy údržby v SQL Server studio pro pravidelné spouštění reorganizace indexu databáze. Ačkoli obnovení indexů v SQL Serveru by mělo být pravděpodobně provedeno offline nebo když je systém nečinný, reorganizace indexu by mohla být provedena online. Zde je dobrý článek na toto téma: přestavět nebo reorganizovat: údržba indexu serveru SQL.

Chcete-li nastavit plán reorganizace indexu v SQL Serveru, klepněte pravým tlačítkem myši na „Správa“, poté na „plány údržby“ a vyberte „nový plán údržby“ nebo „průvodce plánem údržby“. Postupujte podle pokynů a vytvořte plán.

související: možnosti správy vydání v JAMA Connect

spustit chybějící zprávu indexu

SQL Server poskytuje nástroj, který analyzuje činnosti databáze a doporučuje další indexy, které mohou pomoci s výkonem dotazu. Zpráva by nám mohla poskytnout nějaké představy o tom, proč jsou určité dotazy pomalé.

Chcete-li vygenerovat sestavu, spusťte následující dotaz poté, co byla instance databáze chvíli použita (kredit: Nepoužívejte jen slepě vytvářet ty“ chybějící “ indexy!):

SELECT d., s = OBJECT_SCHEMA_NAME(d.), o = OBJECT_NAME(d.), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, s.avg_total_user_cost, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dINNER JOIN sys.dm_db_missing_index_groups AS gON d.index_handle = g.index_handleINNER JOIN sys.dm_db_missing_index_group_stats AS sON g.index_group_handle = s.group_handleWHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d., 'IsMsShipped') = 0

Přečtěte si článek nalezení chybějících indexů, abyste porozuměli výstupu z tohoto dotazu.

monitorujte relace databáze

monitorujte relace databáze pomocí nástroje Activity Monitor. Microsoft SQL Server Management Studio přichází s Activity Monitor, který lze použít ke sledování relací databáze.

hledejte relace, které jsou blokovány jinými relacemi po dlouhou dobu. Klepněte pravým tlačítkem myši na relaci a vyberte „podrobnosti“, abyste zobrazili podrobnosti dotazu, který je s procesem spojen.

použijte Windows Resource Monitor

Windows Resource Monitor lze použít ke sledování paměti a využití CPU procesu SQL Server, abyste se ujistili, že v systému je dostatek paměti a CPU není nasycený. Vezměte prosím na vědomí, že je známý problém pro SQL Server 2008, kde obrázek paměti zobrazený pro proces SQL Server v Resource Monitor není správný.

Identifikujte pomalé dotazy

pokud jsme zjistili, že výkon nesouvisí se zdroji, dalším krokem je identifikace pomalých dotazů, které vedou ke špatnému výkonu.

následující dotaz vrací 100 nejlepších pomalých dotazů, které běží déle než 300 ms (kredit: dlouhé běžící dotazy):

SELECT st.text, qp.query_plan, qs.*FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300

zde je snímek obrazovky některých výsledků vzorku:

jakmile jsme shromáždili seznam pomalých dotazů, mohli bychom je spustit přímo v databázi a podívat se na plány provádění, abychom těmto dotazům porozuměli.

další nástroje

zde je seznam dalších nástrojů, které lze použít k řešení problémů s SQL Serverem a Java aplikacemi:

• SQL Server Profiler: lze použít k identifikaci pomalých dotazů. Můžete jej spustit z SQL Server Management Studio
• VisualVM: může být použit ke sledování využití prostředků Java a dělat podproces
• Java Mission Control / Flight recording: monitor a záznam událostí systému
• JProfiler: identifikovat pomalé služby nebo databázové hovory (vývoj, není podporován 8.x nasazení)
• New Relic: může sledovat výkon databáze a zaznamenávat pomalé dotazy

kontrolní seznamy

zde je seznam informací, které lze shromáždit k řešení problémů se serverem SQL:

1. konfigurace databázového serveru (počet jader CPU, fyzická paměť, místo na disku, verze serveru Windows, verze serveru SQL, nastavení paměti serveru SQL)
2. konfigurace aplikačního serveru (počet jader CPU, fyzická paměť, místo na disku, nastavení paměti jádra Java)
3. Statistika indexu databáze
4. statistika fragmentace databáze
5. SQL Server chybí zpráva indexu
6. paměť databázového serveru a statistiky CPU, když je server pomalý
7. operace, které způsobují, že systém je pomalý
8. SQL Server slow query report
9. statistiky databázové tabulky
10. pár výpisů podprocesů z Java aplikací pořízených během pomalých operací, pokud je to možné

závěr

ladění problémů s výkonem SQL Serveru není vždy přímočaré. Ale se správnými nástroji a trpělivostí bychom měli být schopni pochopit příčinu těchto problémů. Doufám, že informace v tomto článku s tím pomohou.