Megosztás:

A lekérdezési teljesítmény szűk keresztmetszeteinek észlelése

A következőkre vonatkozik:Azure SQL DatabaseSQL Database a Fabricben

Ha egy teljesítményt érintő szűk keresztmetszetet próbál elhárítani, először állapítsa meg, hogy a szűk keresztmetszet akkor jelentkezik-e, amikor a lekérdezés fut, vagy amikor várakozó állapotban van. A meghatározástól függően különböző állásfoglalások érvényesek. Az alábbi diagram segítségével megismerheti azokat a tényezőket, amelyek futással vagy várakozással kapcsolatos problémát okozhatnak. Az egyes problémákhoz kapcsolódó problémákat és megoldásokat ebben a cikkben tárgyaljuk.

Az adatbázis-figyelő vagy a dinamikus felügyeleti nézetekkel észlelheti az ilyen típusú teljesítménybeli szűk keresztmetszeteket.

A számítási feladatok állapotának diagramja, futtatás és várakozás.

Futással kapcsolatos problémák: A futással kapcsolatos problémák általában fordítási problémákhoz kapcsolódnak, amelyek az elégtelen vagy túlhasznált erőforrásokhoz kapcsolódó optimálisabb lekérdezéstervet vagy végrehajtási problémákat eredményeznek. Várakozással kapcsolatos problémák: A várakozással kapcsolatos problémák általában a következőkhöz kapcsolódnak:

  • Locks (blocking)
  • I/O
  • Használattal tempdb kapcsolatos versengés
  • Memóriahasználati várakozások

Ez a cikk az Azure SQL Database-ről szól, lásd még a felügyelt Azure SQL-példányok lekérdezési teljesítménybeli szűk keresztmetszeteinek észlelhető típusait.

Az optimálisnál rosszabb lekérdezési tervet eredményező fordítási problémák

Az SQL Query Optimizer által generált optimálisnál rosszabb terv okozhatja a lassú lekérdezési teljesítményt. Előfordulhat, hogy az SQL Query Optimizer nem optimális tervet hoz létre egy hiányzó index, elavult statisztika, a feldolgozandó sorok számának helytelen becslése vagy a szükséges memória pontatlan becslése miatt. Ha tudja, hogy a lekérdezés gyorsabban lett végrehajtva a múltban vagy egy másik adatbázisban, hasonlítsa össze a tényleges végrehajtási terveket, és ellenőrizze, hogy különböznek-e.

Lekérdezések feloldása optimálisnál rosszabb lekérdezés-végrehajtási tervekkel

A következő szakaszok azt ismertetik, hogyan oldhatók meg a lekérdezések a optimálisnál rosszabb lekérdezés-végrehajtási tervvel.

Paraméterérzékeny csomaggal (PSP) kapcsolatos problémákat tartalmazó lekérdezések

Paraméterérzékeny tervvel (PSP) kapcsolatos probléma akkor fordul elő, ha a lekérdezésoptimalizáló olyan lekérdezés-végrehajtási tervet hoz létre, amely csak egy adott paraméterértékhez (vagy értékkészlethez) optimális, és a gyorsítótárazott terv ezután nem optimális az egymást követő végrehajtásokban használt paraméterértékekhez. Az optimálisnak nem megfelelő csomagok lekérdezési teljesítményproblémát okozhatnak, és csökkenthetik a számítási feladatok teljes átviteli sebességét.

A paraméterek szippantásával és a lekérdezésfeldolgozással kapcsolatos további információkért tekintse meg a lekérdezésfeldolgozási architektúra útmutatóját.

Számos áthidaló megoldás segíthet a PSP-problémák megoldásában. Minden kerülő megoldáshoz kapcsolódó kompromisszumok és hátrányok vannak:

  • Az SQL Server 2022 -ben (16.x) bevezetett új funkció a paraméterérzékeny terv optimalizálása, amely megpróbálja enyhíteni a paraméterérzékenység által okozott legoptimálisabb lekérdezési terveket. Ez az Azure SQL Database 160-es adatbáziskompatibilitási szintje esetén engedélyezett.
  • Minden lekérdezés végrehajtásához használja a RECOMPILE lekérdezési tippet. Ez a kerülő megoldás a fordítási időt és a nagyobb processzorhasználatot használja a jobb tervminőség érdekében. Ez RECOMPILE a lehetőség gyakran nem lehetséges olyan számítási feladatok esetében, amelyek nagy átviteli sebességet igényelnek.
  • Az OPTION (OPTIMIZE FOR...) lekérdezési tipp használatával felülbírálhatja a tényleges paraméterértéket egy tipikus paraméterértékkel, amely olyan tervet hoz létre, amely elég jó a legtöbb paraméterérték-lehetőséghez. Ez a beállítás az optimális paraméterértékek és a kapcsolódó tervjellemzők megfelelő megértését igényli.
  • Az OPTION (OPTIMIZE FOR UNKNOWN) lekérdezési tipp használatával felülbírálhatja a tényleges paraméterértéket, és ehelyett a sűrűségvektor átlagát használhatja. Ezt úgy is megteheti, hogy rögzíti a bejövő paraméterértékeket a helyi változókban, majd a helyi változókat használja a predikátumokban a paraméterek használata helyett. Ehhez a javításhoz az átlagos sűrűségnek elég jónak kell lennie.
  • Tiltsa le a paramétersziffing teljes egészét a DISABLE_PARAMETER_SNIFFING lekérdezési tipp használatával.
  • A KEEPFIXEDPLAN lekérdezési tipp használatával megakadályozhatja a gyorsítótár újrafordítását. Ez a megkerülő megoldás feltételezi, hogy a gyorsítótárban már megtalálható a elég jó közös terv. Letilthatja az automatikus statisztikai frissítéseket is, hogy csökkentse a jó terv kizárásának és egy új rossz terv fordításának esélyét.
  • Kényszerítse a tervet a USE PLAN lekérdezési tipp kifejezett használatával a lekérdezés újraírásával és a lekérdezés szövegének hozzáadásával. Vagy állítson be egy adott tervet a Lekérdezéstár használatával vagy az automatikus hangolás engedélyezésével.
  • Cserélje le az egyetlen eljárást olyan beágyazott eljárásokra, amelyek mindegyike használható feltételes logika és a kapcsolódó paraméterértékek alapján.
  • Dinamikus sztringvégrehajtási alternatívák létrehozása statikus eljárásdefinícióhoz.

A lekérdezési tippek alkalmazásához módosítsa a lekérdezést, vagy használja a Lekérdezéstár-tippeket, hogy kódmódosítások nélkül alkalmazza a tippet.

A PSP-problémák megoldásával kapcsolatos további információkért tekintse meg az alábbi blogbejegyzéseket:

Nem megfelelő paraméterezés által okozott fordítási tevékenység

Ha egy lekérdezés literálokkal rendelkezik, az adatbázismotor automatikusan paraméterezi az utasítást, vagy egy felhasználó explicit módon paraméterezi az utasítást a fordítások számának csökkentése érdekében. Az ugyanazon mintát, de eltérő konstansértékeket használó lekérdezés fordításainak nagy száma jelentős processzorhasználatot eredményezhet. Hasonlóképpen, ha csak részben paraméterez egy olyan lekérdezést, amely továbbra is konstansértékekkel rendelkezik, az adatbázismotor nem paraméterezi tovább a lekérdezést.

Íme egy példa egy részben paraméteres lekérdezésre:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

Ebben a példában a guid értéket veszi figyelembe@p1, t1.c1 de t2.c2 továbbra is a GUID-t használja literálként. Ebben az esetben, ha módosítja a lekérdezés értékét c2, a rendszer más lekérdezésként kezeli a lekérdezést, és új fordítás történik. A példában szereplő fordítások csökkentése érdekében a GUID-t is paramétereznie kell.

Az alábbi lekérdezés a lekérdezések számát mutatja lekérdezéskivonat alapján annak megállapításához, hogy a lekérdezés megfelelően van-e paraméterezve:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

A lekérdezésterv módosításait befolyásoló tényezők

A lekérdezés-végrehajtási terv újrafordítása olyan generált lekérdezéstervet eredményezhet, amely eltér az eredeti gyorsítótárazott tervtől. Előfordulhat, hogy egy meglévő eredeti terv különböző okokból automatikusan újrafordításra kerül:

  • A séma változásaira a lekérdezés hivatkozik
  • A lekérdezés hivatkozik a táblák adatváltozására
  • A lekérdezési környezet beállításai megváltoztak

A lefordított terveket különböző okokból lehet kibocsátani a gyorsítótárból, például:

  • Instance restarts
  • Adatbázis-hatókörű konfigurációs módosítások
  • Memory pressure
  • Explicit kérések a gyorsítótár törlésére

Ha RECOMPILE tippet használ, a rendszer nem gyorsítótárazza a csomagot.

Az újrafordítás (vagy a gyorsítótár kiürítése utáni új fordítás) továbbra is az eredetivel azonos lekérdezés-végrehajtási terv létrehozását eredményezheti. Ha a terv az előző vagy az eredeti tervtől változik, ezek a magyarázatok valószínűleg a következők:

  • Módosított fizikai kialakítás: Az újonnan létrehozott indexek például hatékonyabban fedik le a lekérdezések követelményeit. Az új indexek akkor használhatók egy új fordításban, ha a lekérdezésoptimalizáló úgy dönt, hogy az új index használata optimálisabb, mint a lekérdezés végrehajtásának első verziójához eredetileg kiválasztott adatstruktúra használata. A hivatkozott objektumok bármilyen fizikai módosítása új tervválasztást eredményezhet fordításkor.

  • Kiszolgálói erőforrás-különbségek: Ha egy rendszer egyik csomagja eltér a másik rendszer tervétől, az erőforrások rendelkezésre állása, például az elérhető processzorok száma befolyásolhatja, hogy melyik terv jön létre. Ha például egy rendszer több processzorral rendelkezik, párhuzamos csomag is kiválasztható. Az Azure SQL Database-beli párhuzamosságról további információt az Azure SQL Database max. párhuzamossági fokának (MAXDOP) konfigurálása című témakörben talál.

  • Különböző statisztikák: Előfordulhat, hogy a hivatkozott objektumokhoz társított statisztikák megváltoztak, vagy lényegesen eltérnek az eredeti rendszer statisztikáitól. Ha a statisztikák megváltoznak, és újrafordítás történik, a lekérdezésoptimalizáló a módosításuktól kezdve használja a statisztikákat. A módosított statisztikák adateloszlása és gyakorisága eltérhet az eredeti összeállításétól. Ezek a módosítások számosságbecslések létrehozására szolgálnak. (A számosság becslése azoknak a soroknak a száma, amelyek várhatóan áthaladnak a logikai lekérdezési fán.) A számosságbecslések módosítása különböző fizikai operátorok és a hozzájuk tartozó műveleti rendelések kiválasztásához vezethet. A statisztikák kisebb módosításai is megváltoztathatják a lekérdezések végrehajtási tervét.

  • Módosított adatbáziskompatibilitási szint vagy számosságbecslő verziója: Az adatbázis-kompatibilitási szint módosítása új stratégiákat és funkciókat tehet lehetővé, amelyek eltérő lekérdezés-végrehajtási tervet eredményezhetnek. Az adatbáziskompatibilitási szinten túl a 4199-es letiltott vagy engedélyezett nyomkövetési jelző vagy az adatbázis-hatókörű konfigurációs QUERY_OPTIMIZER_HOTFIXES megváltozott állapota is befolyásolhatja a lekérdezés-végrehajtási terv fordítási idejének választását. A 9481-et (régi CE kényszerítése) és a 2312-et (alapértelmezett CE kényszerítése) jelzők szintén hatással vannak a tervre.

Erőforráskorlátokkal kapcsolatos problémák

A lassú lekérdezési teljesítmény, amely nem kapcsolódik a nem optimális lekérdezési tervekhez, és a hiányzó indexek általában az elégtelen vagy túlhasznált erőforrásokhoz kapcsolódnak. Ha a lekérdezési terv optimális, előfordulhat, hogy a lekérdezés (és az adatbázis) eléri az adatbázis vagy a rugalmas készlet erőforráskorlátait. Ilyen lehet például a szolgáltatásszint túlzott naplóírási teljesítménye.

Ha a problémát elégtelen erőforrásként azonosítja, frissítheti az erőforrásokat, hogy növelje az adatbázis kapacitását, hogy elnyelje a cpu-követelményeket. További információ: Önálló adatbázis-erőforrások méretezése az Azure SQL Database-ben és rugalmas készleterőforrások méretezése az Azure SQL Database-ben.

A megnövekedett számítási feladatok mennyisége által okozott teljesítményproblémák

Az alkalmazásforgalom és a számítási feladatok mennyiségének növekedése megnövelheti a processzorhasználatot. De óvatosnak kell lennie, hogy megfelelően diagnosztizálja ezt a problémát. Ha magas processzorhasználati problémát tapasztal, válaszoljon ezekre a kérdésekre, és állapítsa meg, hogy a növekedést a számítási feladatok mennyiségének változásai okozzák-e:

  • Az alkalmazás lekérdezései okozzák a magas processzorhasználati problémát?

  • A legnagyobb processzorhasználatú lekérdezések esetében, amelyek azonosíthatók:

    • Több végrehajtási terv is társítva volt ugyanahhoz a lekérdezéshez? Ha igen, miért?
    • Az azonos végrehajtási tervvel rendelkező lekérdezések esetében a végrehajtási idő konzisztens volt? Nőtt a végrehajtások száma? Ha igen, a számítási feladatok növekedése valószínűleg teljesítményproblémát okoz.

Összefoglalva, ha a lekérdezés-végrehajtási terv nem másként fut, de a processzorhasználat a végrehajtási számmal együtt nőtt, a teljesítményproblémát valószínűleg a számítási feladatok számának növekedése okozná.

Nem mindig könnyű azonosítani a számítási feladatok mennyiségének olyan módosítását, amely processzorproblémát okoz. Vegye figyelembe az alábbi tényezőket:

  • Módosított erőforrás-használat: Vegyük például azt a forgatókönyvet, amikor a processzorhasználat hosszabb ideig 80%-ra nőtt. A processzorhasználat önmagában nem jelenti azt, hogy a számítási feladatok mennyisége megváltozott. A lekérdezés-végrehajtási terv regressziói és az adatterjesztés változásai is hozzájárulhatnak az erőforrás-használathoz annak ellenére, hogy az alkalmazás ugyanazt a számítási feladatot hajtja végre.

  • Új lekérdezés megjelenése: Egy alkalmazás különböző időpontokban új lekérdezéseket eredményezhet.

  • A kérelmek számának növekedése vagy csökkentése: Ez a forgatókönyv a számítási feladatok legnyilvánvalóbb mértéke. A lekérdezések száma nem mindig felel meg a nagyobb erőforrás-kihasználtságnak. Ez a metrika azonban továbbra is jelentős jel, feltéve, hogy más tényezők nem változnak.

Az adatbázis-figyelővel észlelheti a számítási feladatok növekedését, és megtervezheti a regressziókat az idő függvényében.

Miután megszüntetett egy optimálisnál rosszabb tervet és a végrehajtási problémákhoz kapcsolódó várakozással kapcsolatos problémákat, a teljesítménnyel kapcsolatos probléma általában az, hogy a lekérdezések valószínűleg valamilyen erőforrásra várnak. A várakozással kapcsolatos problémákat az alábbiak okozhatják:

A leggyakoribb várakozási kategóriák megjelenítésének módszerei

Ezeket a módszereket gyakran használják a várakozási típusok felső kategóriáinak megjelenítésére:

  • Az adatbázis-figyelővel azonosíthatja a megnövekedett várakozások miatti teljesítménycsökkenéssel rendelkező lekérdezéseket.
  • A Lekérdezéstár használatával az egyes lekérdezések időbeli várakozási statisztikáit keresheti meg. A Lekérdezéstárban a várakozási típusok várakozási kategóriákba vannak kombinálva. A várakozási kategóriák megfeleltetését a sys.query_store_wait_stats.
  • A sys.dm_db_wait_stats használatával adatokat ad vissza a lekérdezési művelet során végrehajtott szálak által tapasztalt várakozásokról. Ezzel az összesített nézettel diagnosztizálhatja az Azure SQL Database teljesítményproblémáit, valamint adott lekérdezésekkel és kötegekkel. A lekérdezések várakozhatnak erőforrásokra, várólista-várakozásokra vagy külső várakozásokra.
  • A sys.dm_os_waiting_tasks használatával adatokat ad vissza az egyes erőforrásokra várakozó tevékenységek várólistájáról.

Magas processzorhasználat esetén előfordulhat, hogy a lekérdezéstár és a várakozási statisztika nem tükrözi a processzorhasználatot, ha:

  • A nagy processzorhasználatú lekérdezések továbbra is futnak.
  • A nagy processzorhasználatú lekérdezések feladatátvételkor futottak.

A lekérdezéstárat és a várakozási statisztikákat nyomon követő DMV-k csak a sikeresen befejezett és időtúllépéses lekérdezések eredményeit jelenítik meg. Az utasítások befejezéséig nem jelenítik meg az aktuálisan végrehajtó utasítások adatait. A dinamikus felügyeleti nézet sys.dm_exec_requests a lekérdezések aktuális végrehajtásának nyomon követéséhez és a kapcsolódó munkavégző idő nyomon követéséhez.

Tip

Additional tools:

Related content

  • Last updated on