Megosztás:

A lekérdezési teljesítmény szűk keresztmetszeteinek észlelhető típusai az SQL Serverben és a felügyelt Azure SQL-példányban

A következőkre vonatkozik:SQL ServerAzure SQL Managed Instance

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 egyes problémákhoz kapcsolódó problémákat és megoldásokat ebben a cikkben tárgyaljuk.

Az SQL Server DMV-kkel észlelheti az ilyen típusú teljesítménybeli szűk keresztmetszeteket.

Futással kapcsolatos problémák: A futással kapcsolatos problémák általában fordítási hibákhoz kapcsolódnak, amelyek szuboptimális lekérdezési tervet eredményeznek, vagy végrehajtási hibákhoz kapcsolódnak, amelyek elégtelen vagy túlhasznált erőforrásokból erednek. Várakozással kapcsolatos problémák: A várakozással kapcsolatos problémák általában a következőkhöz kapcsolódnak:

  • Zárolások (blokkolás)
  • I/O
  • A tempdb használatával kapcsolatos vita
  • Memória-engedélyezési várakozások

Ez a cikk az SQL Serverről és a felügyelt Azure SQL-példányról szól, lásd még az Azure SQL Database-ben a lekérdezési teljesítmény 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 létrehozott 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 példányon, hasonlítsa össze a tényleges végrehajtási terveket, hogy megállapítsa, különböznek-e.

A lekérdezések hangolása és tippek példája az Alkalmazások és adatbázisok teljesítményének finomhangolása cikkben bemutatja egy nem optimális lekérdezési terv hatását paraméterezett lekérdezésekre, hogyan észlelheti ezt a helyzetet, és hogyan használhat fel egy lekérdezési tippet a probléma megoldására.

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 (16.x) új funkciója a paraméterérzékeny terv optimalizálása, amely megpróbálja enyhíteni a paraméterérzékenység által okozott nem optimális lekérdezési terveket. Ez a 160-es adatbázis-kompatibilitási szinttel engedélyezve van.
  • Minden lekérdezés végrehajtásához használja a RECOMPILE lekérdezési tippet. Ez az ideiglenes megoldás a fordítási időt és a CPU kihasználtságának növelését 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árban történő újrafordításokat. 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 tipp beillesztésével a lekérdezés szövegébe. 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.

Ha lekérdezési tippeket szeretne alkalmazni, módosítsa a lekérdezést, vagy a Lekérdezéstár-tippek használatával kódmódosítások nélkül alkalmazza a tippet. Az SQL Server 2022 előtti SQL Server-verziókban használjon terv-útmutatókat.

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

Helytelen paraméterezés által okozott fordítási folyamat

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 követve, de különböző literális értékeket használva történő lekérdezés sokszori lefordítása magas processzorhasználatot okozhat. Hasonlóképpen, ha csak részben paraméterez egy olyan lekérdezést, amely továbbra is konstansokkal 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 t1.c1 átveszi a @p1-et, de a 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 fordítások bonyolultságának csökkentése érdekében ebben a példában a GUID-ot is paraméterezed.

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 tervet különböző okokból lehet eltávolítani a gyorsítótárból, például:

  • Példányok újraindítása
  • Adatbázis-hatókörű konfigurációs módosítások
  • Memóriaterhelés
  • Explicit kérések a gyorsítótár törlésére

Ha RECOMPILE utasítást használ, a terv nem kerül gyorsítótárba.

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ó. További információ a párhuzamosságról: A párhuzamosság maximális fokának konfigurálása kiszolgálókonfigurációs lehetőség.

  • 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ő, illetve az adatbázis-hatókörű konfigurációs QUERY_OPTIMIZER_HOTFIXES módosított állapota is befolyásolhatja a lekérdezésvégrehajtási terv kiválasztását a fordítási idő alatt. A 9481-et (régi CE kényszerítése) és a 2312-et (alapértelmezett CE kényszerítése) nyomkövető jelzők szintén hatással vannak a tervre.

Erőforráskorlátokkal kapcsolatos problémák a felügyelt Azure SQL-példányban

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 a felügyelt példány erőforráskorlátait. A szolgáltatási szint túlzott naplóírási teljesítménye lehet egy példa.

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. A felügyelt példányok skálázásával kapcsolatos információkért tekintse meg a szolgáltatásszintű erőforráskorlátokat

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.

  • Párhuzamosság: A túlzott párhuzamosság ronthatja a számítási feladatok egyéb egyidejű teljesítményét azáltal, hogy más cpu- és feldolgozószál-erőforrások lekérdezéseit éhezteti. További információ a párhuzamosságról: A párhuzamosság maximális fokának konfigurálása kiszolgálókonfigurációs lehetőség.

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:

  • Blokkolás:

    Előfordulhat, hogy egy lekérdezés olyan objektumokat zárol az adatbázisban, amelyekhez más lekérdezések is próbálnak hozzáférni. A blokkoló lekérdezéseket DMV-k használatával lehet azonosítani. További információ: A blokkolási problémákismertetése és megoldása.

  • I/O-problémák

    Előfordulhat, hogy egy lekérdezés arra vár, hogy a lap be legyen írva egy adat- vagy naplófájlba. Ebben az esetben ellenőrizze az INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG vagy PAGEIOLATCH_* várakozási statisztikáit a DMV-ben. Lásd a DMV-k I/O-teljesítményproblémák azonosítására való használatáról szóló cikket.

  • Tempdb-problémák

    Ha a számítási feladat ideiglenes táblákat használ, vagy a tervekben tempdb kishatárú kiírások történnek, a lekérdezések tempdb teljesítményproblémát okozhatnak. Az eset további felderítéséhez tekintse át a tempdb-problémák beazonosításáról szóló cikket.

  • Memóriával kapcsolatos problémák

    Ha a számítási feladathoz nem áll rendelkezésre elegendő memória, csökkenhet a lap várható élettartama, vagy a lekérdezések a szükségesnél kevesebb memóriát kaphatnak. Bizonyos esetekben a lekérdezésoptimalizáló beépített intelligenciája meg tudja oldani a memóriával kapcsolatos problémákat. Lásd a DMV-knek a memóriabeli ideiglenes tár problémáinak azonosítására való használatáról szóló cikket. Ha memóriahiány miatti hibákat tapasztal, tekintse át a sys.dm_os_out_of_memory_events dokumentációját. Továbbá fontolja meg a Memóriaoptimalizált prémium sorozat szintű, magasabb memória-virtuális mag arányú Azure SQL Managed Instance hardver használatát.

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:

  • 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 várakozási típusokhoz való megfeleltetését a sys.query_store_wait_stats táblázatban találhatja meg.
  • A sys.dm_os_wait_stats használatával adatokat kaphat a lekérdezési művelet során végrehajtott szálak által tapasztalt várakozásokról. Ez az összesített nézet az Azure SQL Managed Instance vagy az SQL Server-példány teljesítményproblémáinak diagnosztizálására használható. Az lekérdezések várakozhatnak erőforrásokra, sorban állás közbeni 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.
  • Nagy CPU-erőforrást igénylő lekérdezések futottak, amikor feladatátvétel történt.

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ézetet sys.dm_exec_requests használhatjuk a lekérdezések aktuális végrehajtásának és a kapcsolódó munkavégzési idő nyomon követésére.

Jótanács

További eszközök:

Következő lépések

  • Last updated on