Jak řešit výkon dotazů Ad Hoc v serveru SQL Server

Vlastnosti výkonnostní problémy

Problém výkonu lze charakterizovat následujícími vlastnostmi:

• Krátké ad-hoc dotazy, které mají obvykle velmi krátkou dobu za následek snížení celkového výkonu systému při vysoký počet souběžných uživatelů spuštění dotazů.

• Velmi vysoké nebo 100 procent využití procesoru.

• Žádné související blokování během období snížení výkonu.
  
  Můžete rychle vyhledávat blokování kontrolou sloupci BLK ve výstupu sp_who systémovou uloženou proceduru. Pokud sloupec BLK je nenulový počet systémový proces ID (identifikátory SPID), tam je blokuje.

• V některých situacích paměti serveru je třeba zdůraznit, a může dojít k chybám, které jsou podobné následující chyby:

  Chyba: 701, závažnosti: 17, stav: 1
  Není dostatek paměti ke spuštění tohoto dotazu.

  - nebo -

  Msg 8645, úroveň 17 stav 1, procedura, řádek 1
  Došlo k vypršení časového limitu při čekání na paměťové prostředky k provedení dotazu. Spusťte dotaz znovu.

Vylepšení v dotazu kompilace

Z důvodu vylepšení v architektuře systému spuštění v SQL Server 7.0, konkrétně Optimalizátor dotazů můžete zaznamenat rozdíl ve využití systémových prostředků aplikacemi v porovnání s předchozími verzemi serveru SQL Server. Konkrétně SQL Server 7.0 může zobrazit zvýšení využití procesoru nebo paměti, ale obvykle jsou starší verze serveru SQL Server disku IO vázán. Tyto změny lze sledovat na dvou faktorech:

• Algoritmus hash a sloučení spojení.

• Čas kompilace dotazu

Starší verze serveru SQL Server spolehlivé zcela iterací smyčky vnořené k provedení spojení. Vnořené smyčky spojení ze své podstaty použít vstupně-výstupní operace disku. Počínaje SQL Server 7.0, byly zavedeny algoritmu hash a sloučení spojení. Algoritmus hash a sloučení spojení udělat mnohem více v paměti než spojení vnořené smyčky zpracování. Je to logický výsledek tohoto procesoru a využití paměti je vyšší, pokud jsou použity tyto techniky spojení. Další informace o algoritmus hash a sloučení spojení naleznete v tématu "Principy Hash spojení" a "Principy korespondence spojení" témata v SQL Server 7.0 Books Online.

Čas kompilace dotazu jsou ovlivněny, protože optimalizace dotazů má více možností a informací, které jsou k dispozici než v předchozích verzích serveru SQL Server, včetně nové hodnoty hash a sloučení spojení techniky, vylepšené vyhledávání algoritmy a statistiky sloupce. Tyto dodatečné informace umožňuje Optimalizátor dotazu vybrat nejúčinnější metodu k načtení dat dotazu. Analýza a posouzení těchto nových technik a informace však vyžaduje čas zpracování. Toto zvýšení využití procesoru může způsobit dotazu kompilace časy, které jsou delší než v předchozích verzích serveru SQL Server.

U většiny dotazů je tento nárůst v čase kompilace posunu snížení v době spuštění. Celkový efekt je dotaz spustí rychleji než v předchozích verzích serveru SQL Server. Jedinou výjimkou však dochází s velmi malé, jednoduché, OLTP typu dotazy, které mají velmi nízké časy spuštění. Pro tyto dotazy proces generování plánu dotazu může být stejné nebo větší výdaje než spuštění dotazu. V důsledku toho může provést dotaz poněkud pomalejší než v předchozích verzích serveru SQL Server. Vzhledem k tomu, že rozdíl je obvykle v milisekundách, tyto efekty nejsou všimli pro konkrétní dotaz, pokud provést samostatně. Však můžete všimnout, že systém celkové využití procesoru je vyšší než v předchozích verzích serveru SQL Server pokud velký počet dotazů ad hoc jsou spouštěny současně vysoký počet uživatelů.

Rozvíjet parametrizované dotazy

SQL Server 7.0 používá několik nových postupů, například ukládání do mezipaměti dotazů ad hoc a automatické parametrizace. Dotazy, které SQL Server 7.0 automaticky parameterizes jsou však omezeny. Ujistěte se, že plány dotazů jsou rozděleny do parametrů a lze efektivně znovu použít, použijte následující metody:

• Značek parametrů Jak technologie OLE DB a ODBC API povolit parametry pro zadaný s otazníkem, když uživatelé odesílat dotazy. To může být velmi užitečné v libovolné aplikaci, zejména pro střední vrstvy aplikace, které obsahují moduly generování dotazu kde není k dispozici pomocí uložené procedury. Plán dotazů, který je generován pro dotazy, které mají značek parametrů lze opakovaně využít všech klientů, kteří spuštění téhož dotazu, i když nejsou určeny hodnoty různých parametrů. Další informace naleznete v tématu "Parametr značky" v SQL Server 7.0 Books Online.

• sp_executesql Sp_executesql uložená procedura je volána zprostředkovatele OLE DB nebo ovladač ODBC při použití značek parametrů v aplikaci. Však ji může také volat přímo aplikaci nebo v jiné uložené procedury k explicitně parametrizaci dotazů ad hoc. To může být velmi užitečné v aplikacích nebo dávkové soubory, kde se používá příkaz EXECUTE k provedení dynamických příkazů SQL. Na rozdíl od sp_executesqlneumožňuje příkaz EXECUTE parametrizace. Toto nastavení omezuje možnost opětovného použití plánu dotazu. Další informace naleznete v tématu "sp_executesql (T-SQL)" a "Použití sp_executesql" témata v SQL Server 7.0 Books Online.

• Uložené procedury Uložené procedury mají mnoho výhod, včetně schopnosti parametrizaci dotazů a opětovné použití spuštění plánů. Další informace naleznete v tématech "Uložené procedury" a "Programming uložené procedury" v SQL Server 7.0 Books Online.

Zobrazení dat nástroje Sledování výkonu

Chcete-li zjistit, jaké systémové prostředky způsobují kritické místo pomocí protokolu sledování výkonu. Sledování výkonu protokolu můžete umožňují celkový obrázek systému a pomáhají při zobrazení dat SQL Profiler zaměřuje pozornost uživatelů. Zkontrolujte data sledování výkonu z doby, kdy bylo přes čas, který snížil výkon dobrý výkon. Určit čítače, která byla ovlivněna nejprve a pak určit, které z následujících problémů je nejdůležitějších pro vaši situaci:

• Objekt: proces
  Čítač: procesor
  Instance: SQL Server

• Objekt: procesor
  Čítač: % času procesoru
  Instance: Každá instance procesoru zkontrolujte

• Objekt: SQL Server: vyrovnávací paměť Manager
  Čítač: Bez vyrovnávací paměti

• Objekt: SQL Server: vyrovnávací paměť Manager
  Čítač: Odcizení počet stránek

• Objekt: SQL Server: paměti Manager
  Čítač: Granty paměti až do

• Objekt: SQL Server: SQL Statistika
  Čítač: SQL kompilace za sekundu

Pokud jsou vysoké využití procesoru, SQL kompilace za sekundu a volného vyrovnávacích pamětí čítače a čítače čekající na granty paměti a odcizení počet stránek jsou nízké, znamená to, že procesor je kritické místo. Zaměřit se na postup účinně parametrizaci a opakované použití plánů dotazů, aby se zabránilo náklady generování plánu dotazu a naleznete v části "Skupina trasování SQL Profiler třídou událostí" v tomto článku. Je málo volného vyrovnávacích pamětí a kompilace SQL za sekundu čítače a čítače odcizení počet stránek a čeká na granty paměti jsou vysoké, SQL Server je omezen paměti. Zaměřit se na hledání dotazů, kde spojení hash jsou používány a mohou být změněny do smyčky spojení a naleznete v části "Skupina trasování SQL Profiler stopáž" tohoto článku. Další informace o těchto čítačů pomocí názvu čítače SQL Server 7.0 Books Online hledání.

Zobrazení dat SQL Profiler

Při řešení potíží s výkonem je velmi cenné pro zobrazení dat SQL Profiler. Není nutné zkontrolovat všechna data, která je zachycena; být selektivní. SQL Profiler umožňuje efektivně zobrazit sebraná data. Na kartě Vlastnosti (na
Nabídka soubor , klepněte na příkaz Vlastnosti), SQL Profiler umožňuje omezit data zobrazená odebráním sloupce dat nebo události, seskupení nebo řazení podle sloupce dat a používání filtrů. Můžete hledat celý trasování nebo konkrétního sloupce pro specifické hodnoty (na
Upravit nabídky, klepněte na příkaz Najít ). Můžete také uložit SQL Profiler dat tabulky serveru SQL Server (v nabídce soubor přejděte na příkaz Uložit jakoa potom klepněte na tlačítko Sledování tabulky) a potom spustit dotazy SQL proti němu.

Poznámka: Ujistěte se, filtrovat pouze uložené trasovacího souboru. Pokud budete postupovat podle těchto kroků na aktivní sledování rizika ztráty dat, která byla zachycena od spuštění trasování. Uložit aktivní trasování do souboru nebo tabulky nejprve (v
Nabídka soubor , klepněte na příkaz Uložit jako ) a poté jej znovu otevřete (v nabídce soubor klepněte na tlačítko Otevřít) před pokračováním. Při práci s soubor uložený trasování, filtrování neodebere trvale dat; data je pouze skryté, nebudou odstraněny. Můžete přidat a odebrat události a data sloupce, které chcete zaměřit hledání.

Také je třeba zaměřit na oblasti, kde se zobrazí všechny výhody. Tyto faktory mohou pomoci zvýšit výkon aplikace, ale ne nutně stejný stupeň. Před implementací jakékoli změny zjistěte, jak účinné změny mohou být v závislosti na následujících faktorech:

• Jak často se spustí dotaz

• Jaká zlepšení dotazu lze zvýšit.

Například zkrácení doby spuštění jediného dotazu z 1,5 sekund na 1,2 sekund nemusí být užitečné, pokud není spuštěn dotaz často celý den. Nicméně pokud bude dotaz proveden vysoký počet souběžných uživatelů velmi často, zlepšení výkonu může být velmi účinným. Zlepšení jediném dotazu z 6 minut na 3 sekundy a naopak, nemůže vzniknout znatelné zvýšení celkového výkonu Pokud se používá zřídka. Před implementací jakékoli změny odhadnout účinky konkrétní dotaz nebo procedura pomocí seskupení a filtrování technik v SQL Profiler a vaše znalosti aplikace. Zaměřit na změny nejefektivnější nejprve a potom pokračujte iterací prostřednictvím další dotazy a postupy, dokud se nedostanete na úroveň, kde má dostatečně lepší výkon.

Po uložení SQL Profiler trasování do souboru nebo tabulky, znovu otevřete trasování v SQL Profiler a prohlédněte si obsah. Chcete-li seskupit trasování SQL Profiler, postupujte takto:

• Trasování SQL Profiler seskupte podle doby trvání:

  1. V nabídce soubor klepněte na tlačítko
     Vlastnosti.

  2. Klepněte na kartu Datové sloupce a ve skupinovém rámečku skupinyklepněte na tlačítko nahoru
     Doba trvání. Klepněte na dolů k odebrání všech ostatních sloupců.

  3. Klepněte na kartu události a pak odeberte všechny události kromě TSQL SQL:StmtCompleted a TSQL RPC: dokončení. To umožňuje zaměřit se na pouze dotazy, které jsou právě prováděny.

  4. Klepněte na tlačítko OK

  Můžete snadno zobrazit příkazy SQL, listy a postupy, které jsou spuštěny slowest tím umožňuje seskupování podle doby trvání. Zkontrolujte trasování, pokud dochází k problému a vytvořit směrný plán pro dobrý výkon. Můžete filtrovat podle počátečního času ukončit trasování do oddílů, v případě výkonu je dobré a samostatné oddíly výkon je nízký. Dotazy s nejdelší dobou trvání, kdy výkon je dobré hledejte. Jedná se s největší pravděpodobností Podstatou problému. Při snižuje celkový výkon systému, i dobré dotazy můžete zobrazit dlouhé doby trvání, protože čekání na systémové prostředky.
  
  Kontrola provádění plánů pro dotazy, většina má často dlouhé trvání. Pokud zjistíte, že je používán hash spojení, zvažte použití dotazu nápovědy LOOP JOIN vynutit spojení vnořené smyčky pro dotaz. Pokud doba provádění dotazu pomocí smyčky spojení je menší než, rovná nebo mírně vyšší než čas spuštění pomocí algoritmu hash spojení, smyčky spojení může být lepší možnost, pokud v počítači dochází k vysoké paměti a zatížení procesoru. Tím, že snižuje zátěž na kritická místa prostředků (procesor a paměť), můžete zlepšit celkový výkon systému. Další informace o dotazu nápovědy LOOP JOIN naleznete v tématu "SELECT (T-SQL)" v SQL Server 7.0 Books Online.

• Skupina trasování SQL Profiler třídou událostí:

  1. V nabídce soubor klepněte na tlačítko
     Vlastnosti.

  2. Klepněte na kartu Datové sloupce a pod záhlavím skupiny klepněte na tlačítko nahoru
     Třídy událostí a TextTřída událostí v horní. Klepněte na tlačítko dolů odebrat všechny sloupce pod nadpisem skupiny .

  3. Klepněte na kartu události a ujistěte se, že jsou zahrnuty všechny události.

  4. Klepněte na tlačítko OK

Typy událostí

Chcete-li zobrazit typy událostí dochází v počítači se systémem SQL Server a jak často dojde k události, Seskupit podle sloupce Třídy událostí . Hledat v tomto sloupci pro následující události:

• – Různé: Příprava SQL a Exec připraveny SQL; KURZORY: Cursorprepare Připravit SQL události označuje, že příkaz SQL byl připraven pro použití s výchozí sadu výsledků (kurzor na straně klienta) SQLPrepare/SQLExecute (pro ODBC) nebo ICommandText::Prepare/ICommandText::Execute (pro OLE DB) pomocí výchozích možností kurzoru: dopředu, pouze jen pro čtení, velikost řádků = 1. Události Cursorprepare označuje, že kurzor straně serveru byl připraven v příkazu SQL pomocí funkce SQLPrepare/SQLExecute (pro ODBC) nebo ICommandText::Prepare/ICommandText::Execute (pro OLE DB) s jedním z předchozí možnosti kurzoru nastavte jiné než výchozí hodnoty. Událost Exec připraveny SQL označuje, že byl spouštěn buď předchozí typy existující připravených příkazů. Pokud vidíte častých výskytů těchto událostí, aplikace používá model/spuštění přípravy při otevření sady výsledků. Pokud ano, musíte určit, pokud použijete model přípravy/provést správně.
  
  V ideálním případě by aplikace sestavuje příkaz SQL jednou a spustí jej mnohokrát, takže není nutné kompilovat nový plán pokaždé, když je proveden příkaz okně Optimalizace. Pokaždé, když spustíte připravený příkaz Uložit náklady kompilace dotazu. Pokud plánujete spuštění dotazu jednou, společnost Microsoft doporučuje, že není připravíte jej. Příprava a potom spuštěním příkazu SQL vyžaduje tři sítě výměn dat: jeden k přípravě příkazu, jeden pro spuštění příkazu a jeden k unprepare příkazu. Příprava kurzory na straně serveru vyžaduje nejméně pěti výměnami: jeden pro přípravu kurzoru, chcete-li spustit nebo otevřít, jeden jeden nebo více vyvolání, zavřete jej a jeden k unprepare jej. Spuštění dotazu vyžaduje pouze jeden zpětný převod.
  
  Chcete-li zjistit, jak efektivně vaše aplikace používá model/spuštění přípravy, porovnejte počet těchto dvou událostí (Příprava a spouštění) dojít. Počet událostí Exec připraveny SQL by měly být mnohem větší než celková Příprava SQL a události CursorPrepare (tři až pět krát větší je dobrým odhadem). To znamená, že připravených příkazů jsou nebránili opětovnému použití dostatečně často překonat zvýšené nároky na jejich vytváření. Pokud je počet událostí Příprava SQL a CursorPrepare zhruba ekvivalentní počet událostí SQL připraveny Exec , může to znamenat, že aplikace není účinně používá model přípravy/spuštění. Zkuste připravit výpis jednou a znovu použít co nejvíce. Aplikace pro přípravu výkazů jednou a znovu použít tyto příkazy můžete také změnit.
  
  Aplikace musí být napsány specificky efektivně používat model přípravy/spuštění. Dobu existence popisovač připraveného příkazu řídí jak dlouho ponechat HSTMT otevřené v ODBC nebo ICommandText objektu v technologii OLE DB. Jednu běžnou praxí je získat HSTMT připravit příkaz SQL, připravený příkaz Spustit a potom uvolněte HSTMT, tedy ztrátě popisovač připraveného plánu. Pokud to učiníte, neobdržíte žádnou výhodu z modelu přípravy/spuštění. Ve skutečnosti setkat snížení výkonu z důvodu další režii zbytečné síťové komunikaci. Aplikace musí mít metodu pro ukládání do mezipaměti HSTMT nebo objektu s popisovačem připraveného příkazu a přístup k nim pro opakované použití. Ovladače nebo zprostředkovatele toto neprovede automaticky. aplikace je zodpovědný za provádění, správu a použití těchto informací. Pokud aplikace nemůže provést, zvažte použití značek parametrů namísto metody přípravy/spuštění.

• Použití značek parametrů Aplikace mohou pomocí značek parametrů pro optimalizaci použití příkazu Transact-SQL několikrát s různými vstupní a výstupní hodnoty. Při prvním spuštění dotazu je připraven jako parametrizované dotazu a SQL Server generuje a ukládá do mezipaměti parametrizované plán pro dotaz. Pro následné volání na stejný dotaz pomocí buď stejné nebo odlišné parametry SQL Server není nutné generovat nový plán dotazů; SQL Server znovu použít existující plán dotazů nahrazením aktuální parametry.
  
  Pokud aplikace používá parametr značky s volání funkce SQLExecDirect (pro ODBC) nebo ICommandText::Execute (pro OLE DB), ovladače nebo zprostředkovatele automaticky příkaz SQL balíčky a provede jako volání sp_executesql . Příkaz nemá připraven a provedeny samostatně. Pokud SQL Server obdrží volání sp_executesql, jej automaticky kontroluje mezipaměť postup pro odpovídající plán a znovu použije plán nebo generuje nový plán.
  
  Chcete-li zjistit, pokud vaše aplikace používá aktuálně značek parametrů, můžete hledat
  Textový sloupec v SQL Profiler trasování pro "sp_executesql." Však protože sp_executesql může být volán přímo, ne všechny instance označuje použití značek parametrů.
  
  Další informace o modelu přípravy/spuštění naleznete v tématu "Spuštění plánu ukládání do mezipaměti a znovu použít" v SQL Server 7.0 Books Online. Další informace o značek parametrů naleznete v tématu "Parametr značky" v SQL Server 7.0 Books Online.

• SP: dokončeno Dynamických příkazů SQL spuštěn pomocí příkazu EXECUTE zobrazí jako SP: dokončeno události s textem "Dynamic SQL." Rozbalte SP: dokončeno událostí a vyhledejte všechny výskyty, které mají "dynamické" SQL jako text. Pokud existuje mnoho z těchto událostí, bude pravděpodobně možné zvýšit výkon aplikací pomocí sp_executesql namísto příkazu EXECUTE. Sp_executesql uložený postup umožňuje serveru SQL Server k opětovnému použití spuštění plánů, pokud stejný dotaz se spustí znovu pomocí různých parametrů. Při použití příkazu EXECUTE není parametrizované plán a nebude znovu použit, pokud dotaz se spustí znovu pomocí stejných parametrů.
  
  K určení dotazy nebo postupy, které pomocí dynamické události SQL příkaz EXECUTE, Poznámka: ID připojení a spuštění čas pro každou událost. Oddělit trasování (odebrat Třídy událostí a Text v záhlaví skupiny ). Po oddělíte trasování, je řazen v chronologickém pořadí. Můžete filtrovat trasování podle ID připojení (na kartě filtry ) a pak odeberte všechny třídy událostí, s výjimkou SP: počáteční a SP: dokončeno události pro lepší čitelnost. Potom můžete vyhledat začátek události (v nabídce Úpravy klepněte na tlačítko
  Najít). Výsledky zobrazit po spuštění události dynamické SQL. Pokud došlo k události v uložené proceduře, se zobrazí události mezi SP: počáteční a SP: dokončeno události pro tento postup. Pokud k události v uložené proceduře nedošlo, byl proveden jako dotaz ad hoc a můžete použít jiné datové sloupce (Název aplikace, NT uživatelské jménoa další) k určení, kde byl proveden příkaz. Pokud chcete určit text příkazu a kontext, kde byl proveden, můžete také přidat třídy událostí, například SQL:BatchCompleted a SQL:RPCCompleted.
  
  Po určení, kde je používána příkazu EXECUTE, zvažte nahrazení volání sp_executesql. Například zvažte následující scénář, kde se používá příkaz spustit pomocí dynamického příkazu SQL. Postup trvá název tabulky, ID a idValue jako vstupní parametry a potom spustí příkaz SELECT z tabulky na základě hodnoty ID. Použití příkazu EXECUTE postup vypadá podobně jako následující kód:

  drop proc dynamicUsingEXECUTE
    go create proc dynamicUsingEXECUTE @table sysname, @idName varchar(10),
    @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string
    with parameter. -- Notice the use of escape quotes. select @query = 'select *
    from ' + @table + ' where ' + @idName + ' = ''' + @idValue + '''' exec (@query)
    go

  Za předpokladu, že dotaz není Parametrizovaná automaticky, je-li provést tento postup proti názvy tabulky ukázkové databáze pubs dvakrát s různými hodnotami parametru @idValue , SQL Server musí generovat plán samostatného dotazu pro každé spuštění. Například:

  exec dynamicUsingEXECUTE
    'titles', 'title_id', 'MC2222' go exec dynamicUsingEXECUTE 'titles',
    'title_id', 'BU7832'

  Poznámka: V tomto příkladu dotaz je jednoduché dostatečně, můžete ji automaticky parametrizaci a skutečně znovu použít plán vykonání SQL Server. Však pokud to byl složitý dotaz SQL Server může automaticky parametrizaci, SQL Server nemusí znovu použít plán pro druhé spuštění Pokud je parametr @idValue byl změněn. Následující jednoduchý dotaz omezuje složitost v příkladu.
  
  Můžete přepsat pomocí tohoto postupu můžete použít namísto příkazu EXECUTE sp_executesql . Podpora pro nahrazení parametru umožňuje sp_executesql zefektivnit protože generuje provádění plánů, které jsou pravděpodobnější, které budou opětovně použity serverem SQL. Například:

  drop proc dynamicUsingSP_EXECUTESQL go create proc
    dynamicUsingSP_EXECUTESQL @table sysname, @idName varchar(10), @idValue
    varchar(10) as declare @query nvarchar(4000) -- Build query string with
    parameter select @query = 'select * from ' + @table + ' where ' + @idName + ' =
    @idValue' -- Now execute with parameter exec sp_executesql @query, N'@idValue
    varchar(10)', @idValue go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    'MC2222' go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    'BU7832'

  V tomto příkladu při prvním provedení příkazu sp_executesql , SQL Server generuje parametrizované plán pro příkaz SELECT z hlav s title_id jako parametr. Druhé provedení SQL Server znovu použije plán s novou hodnotu parametru. Další informace o sp_executesqlnaleznete v tématu "sp_executesql (T-SQL)" a "Použití sp_executesql" témata v SQL Server 7.0 Books Online.

• SP:RECOMPILES Tato událost označuje, že uložená procedura byla překompilovány během spuštění. Mnoho událostí překompilujte označuje, že SQL Server využívá prostředky pro sestavování dotazu místo spuštění dotazu.
  

Pokud se nezobrazí některé z těchto událostí, aplikace provádí pouze ad-hoc dotazy vůči serveru SQL Server. Pokud SQL Server určuje, že lze automaticky parametrizaci některých dotazů nebo pokud se opakovaně používá stejné parametry, vyžaduje každý dotaz, který je spuštěn SQL Server ke generování nového spuštění plánu. Sledování výkonu SQL Server byste měli vidět mnoho SQL kompilace za sekundu. To může být náročné na procesor pro mnoho souběžných uživatelů. Chcete-li tento problém vyřešit, vyhledejte nejvíce často spouštěny dotazy a zvažte vytvoření uložené procedury pro tyto dotazy pomocí značek parametrů nebo pomocí sp_executesql.