Migrálás utáni érvényesítési és optimalizálási útmutató

A következőkre vonatkozik:SQL Server

Az SQL Server áttelepítés utáni lépése kulcsfontosságú az adatok pontosságának és teljességének egyeztetéséhez, valamint a számítási feladat teljesítményével kapcsolatos problémák feltárásában.

Gyakori teljesítményforgatókönyvek

Az alábbiakban néhány gyakori teljesítményforgatókönyvet ismertünk az SQL Server Platformra való migrálás után, és azok megoldását. Ezek közé tartoznak az SQL Serverről SQL Serverre történő migrálásra (régebbi verziók újabb verziókra) és külső platformra (például Oracle, DB2, MySQL és Sybase) történő SQL Server-migrálásra vonatkozó forgatókönyvek.

Lekérdezési regressziók a számosságbecslő (CE) verziójának módosítása miatt

A következőkre vonatkozik: SQL Server és SQL Server közötti migrálás.

Ha az SQL Server régebbi verziójáról az SQL Server 2014-es (12.x) vagy újabb verzióira migrál, és a adatbázis kompatibilitási szintjét a legújabb elérhetőre frissíti, a számítási feladatok ki lehetnek téve a teljesítményregresszió kockázatának.

Ennek az az oka, hogy az SQL Server 2014-től kezdve (12.x) minden lekérdezésoptimalizáló-módosítás a legújabb adatbáziskompatibilitási szinthezvan kötve, így a csomagok nem módosulnak a frissítés időpontjában, hanem akkor, amikor egy felhasználó a legújabbra módosítja a COMPATIBILITY_LEVEL adatbázis-beállítást. Ez a funkció a Lekérdezéstárral kombinálva nagy mértékben szabályozhatja a lekérdezési teljesítményt a frissítési folyamat során.

Az SQL Server 2014-ben (12.x) bevezetett lekérdezésoptimalizáló-módosításokról további információt A lekérdezéstervek optimalizálása az SQL Server 2014 számosságbecslőcímű témakörben talál.

A számosság becsléséről (CE) további információkat a SQL Server dokumentációban talál.

A megoldás lépései

Módosítsa a adatbázis kompatibilitási szintjét a forrásverzióra, és kövesse az ajánlott frissítési munkafolyamatot az alábbi képen látható módon:

További információ a cikkről: A teljesítmény stabilitásának megőrzése az újabb SQL Server-való frissítés során.

A paraméterszimatolás érzékenysége

A következőkre vonatkozik: külső platformra (például Oracle, DB2, MySQL és Sybase) az SQL Server migrálására.

Jegyzet

Sql Serverről SQL Serverre történő migrálás esetén, ha ez a probléma a forrás SQL Serverben merült fel, az SQL Server újabb verziójára való migrálás as-is nem oldja meg ezt a forgatókönyvet.

Az SQL Server a tárolt eljárásokra vonatkozó lekérdezési terveket úgy állítja össze, hogy az első fordításkor a bemeneti paramétereket szippantja, és létrehoz egy paraméteres és újrafelhasználható tervet, amely a bemeneti adatok elosztására van optimalizálva. A triviális terveket létrehozó legtöbb utasítás akkor is paraméterezve van, ha nem tárolt eljárásokat. Miután egy tervet először gyorsítótárba helyeznek, a jövőbeli végrehajtások egy korábban gyorsítótárazott tervet használnak.

Lehetséges probléma akkor merül fel, ha az első összeállítás nem használja a leggyakoribb paramétereket a szokásos számítási feladathoz. A különböző paraméterek esetében ugyanaz a végrehajtási terv nem lesz hatékony. A cikkhez kapcsolódó további információkért lásd a Paraméterérzékenységcímű részt.

A megoldás lépései

1. Használja a RECOMPILE tippet. A rendszer minden alkalommal kiszámít egy tervet, amikor az egyes paraméterek értékéhez igazodik.

2. Írja át a tárolt eljárást a (OPTIMIZE FOR(<input parameter> = <value>))lehetőség használatához. Döntse el, hogy melyik érték felel meg a releváns számítási feladatok többségének, és hozzon létre és tartson fenn egy olyan tervet, amely a paraméteres érték szempontjából válik hatékonyabbá.

3. Írja át a tárolt eljárást helyi változóval az eljáráson belül. Az optimalizáló most a sűrűségvektort használja a becslésekhez, így a paraméterértéktől függetlenül ugyanazt a tervet eredményezi.

4. Írja át a tárolt eljárást a (OPTIMIZE FOR UNKNOWN)lehetőség használatához. Ugyanaz a hatás, mint a helyi változó technika használata.

5. Írja át a lekérdezést az útmutatás DISABLE_PARAMETER_SNIFFINGhasználatával. Ugyanaz a hatás, mint a helyi változó használatával a paraméter szimatolás teljes letiltásáig, kivéve, ha OPTION(RECOMPILE), WITH RECOMPILE vagy OPTIMIZE FOR <value> van használva.

Borravaló

A Management Studio csomagelemzési funkciójával gyorsan azonosíthatja, hogy ez probléma-e. További információ: Újdonságok az SSMS-ben: A lekérdezési teljesítmény hibaelhárítása egyszerűbbé vált.

Hiányzó indexek

A következőkre vonatkozik: Külső platformok (például Oracle, DB2, MySQL és Sybase) és SQL Server-SQL Server közötti migrálás.

A helytelen vagy hiányzó indexek többlet I/O-t okoznak, ami többlet memóriát és processzorkihasználtságot eredményez. Ennek az lehet az oka, hogy a számítási feladatok profilja megváltozott, például különböző predikátumokat használ, érvényteleníti a meglévő indextervet. A gyenge indexelési stratégia vagy a számítási feladatprofil változásai a következők:

• Keressen ismétlődő, redundáns, ritkán használt és teljesen nem használt indexeket.
• Különös figyelmet igényelnek a nem használt indexekkel végzett frissítések.

A megoldás lépései

1. A hiányzó indexhivatkozásokhoz használja a grafikus végrehajtási tervet.

2. A Database Engine Tuning Advisoráltal létrehozott indexelési javaslatok.

3. Használja a sys.dm_db_missing_index_details.

4. Használjon meglévő DMV-ket használó, már meglévő szkripteket, amelyek betekintést nyújtanak a hiányzó, duplikált, redundáns, ritkán használt és teljesen nem használt indexekbe, de akkor is, ha az adatbázis meglévő eljárásaiban és függvényeiben bármilyen indexhivatkozásra utal/keményen kódol.

Borravaló

Ilyen, már nem használt szkriptek például az indexlétrehozás és az indexinformációk.

Nem lehet predikátumokat használni az adatok szűréséhez

A következőkre vonatkozik: Külső platformok (például Oracle, DB2, MySQL és Sybase) és SQL Server-SQL Server közötti migrálás.

Jegyzet

Sql Serverről SQL Serverre történő migrálás esetén, ha ez a probléma a forrás SQL Serverben merült fel, az SQL Server újabb verziójára való migrálás as-is nem oldja meg ezt a forgatókönyvet.

Az SQL Server Lekérdezésoptimalizáló csak a fordításkor ismert információkért tud elszámolni. Ha egy számítási feladat olyan predikátumokra támaszkodik, amelyek csak a végrehajtási időpontban ismertek, akkor nő a rossz tervválasztás lehetősége. A jobb minőségű terv érdekében a predikátumoknak SARGable-nek kell lenniük.

Jegyzet

A relációs adatbázisokban a SARGable kifejezés egy olyan S-earch ARG ument-predikátumra utal, amely index használatával felgyorsíthatja a lekérdezés végrehajtását. További információ: SQL Server és Azure SQL indexarchitektúra és tervezési útmutató.

Néhány példa nem SARGable predikátumokra:

• Implicit adatkonvertálások, például varcharnvarchar, vagy intvarchar. Keresse meg a futásidejű CONVERT_IMPLICIT figyelmeztetéseket a tényleges végrehajtási tervekben. Az egyik típusról a másikra való konvertálás a pontosság elvesztését is okozhatja.

• Összetett, meghatározatlan kifejezések, például WHERE UnitPrice + 1 < 3.975, de nem WHERE UnitPrice < 320 * 200 * 32.

• Függvényeket használó kifejezések, például WHERE ABS(ProductID) = 771 vagy WHERE UPPER(LastName) = 'Smith'

• Az elején helyettesítő karakterrel rendelkező sztringek, például WHERE LastName LIKE '%Smith', de nem WHERE LastName LIKE 'Smith%'.

A megoldás lépései

1. Mindig deklarálja a változókat/paramétereket célként Adattípusok.

   Ez magában foglalhatja az adatbázisban tárolt felhasználó által definiált kódszerkezetek (például tárolt eljárások, felhasználó által definiált függvények vagy nézetek) és az alapul szolgáló táblákban (például sys.columns) használt adattípusok adatait tároló rendszertáblák összehasonlítását.

2. Ha nem tudja az összes kódot az előző pontra váltani, akkor ugyanahhoz a célhoz módosítsa a tábla adattípusát úgy, hogy az megfeleljen a változó/paraméter deklarációjának.

3. Az alábbi szerkezetek hasznosságának magyarázata:

   • Predikátumként használt függvények;
   • Helyettesítő karakterek keresése;
   • Oszlopos adatokon alapuló összetett kifejezések – értékelje ki, hogy létre kell-e hoznia egy állandó számítási oszlopot, amely indexelhető;

Jegyzet

Ezek a lépések programozott módon is elvégezhetők.

Táblaértékű függvények használata (többsoros vs egysoros)

A következőkre vonatkozik: Külső platformok (például Oracle, DB2, MySQL és Sybase) és SQL Server-SQL Server közötti migrálás.

Jegyzet

Sql Serverről SQL Serverre történő migrálás esetén, ha ez a probléma a forrás SQL Serverben merült fel, az SQL Server újabb verziójára való migrálás as-is nem oldja meg ezt a forgatókönyvet.

A Táblaérték függvények olyan táblázat adattípust ad vissza, amely alternatívát jelenthet a nézetek számára. Bár a nézetek egyetlen SELECT utasításra korlátozódnak, a felhasználó által definiált függvények további utasításokat is tartalmazhatnak, amelyek a nézetekben lehetségesnél több logikát tehetnek lehetővé.

Mivel egy többutas táblaértékelő függvény (MSTVF) kimeneti táblája nem fordításkor jön létre, az SQL Server Lekérdezésoptimalizáló a sorbecslések meghatározásához heurisztikus és nem tényleges statisztikákra támaszkodik.

Még ha indexeket is hozzáad az alaptáblákhoz, ez nem fog segíteni.

AZ MSTVF-ek esetében az SQL Server rögzített 1 becslést használ az MSTVF által visszaadni kívánt sorok számához (az SQL Server 2014-től kezdve (12.x) a rögzített becslés 100 sor.

A megoldás lépései

1. Ha az MSTVF csak egy utasítás, alakítsa át beágyazott táblaértékű függvénnyé.

   CREATE FUNCTION dbo.tfnGetRecentAddress (@ID INT)
   RETURNS
       @tblAddress TABLE ([Address] VARCHAR (60) NOT NULL)
   AS
   BEGIN
       INSERT INTO @tblAddress ([Address])
       SELECT TOP 1 [AddressLine1]
       FROM [Person].[Address]
       WHERE AddressID = @ID
       ORDER BY [ModifiedDate] DESC;
       RETURN;
   END
   

   A beágyazott formátumra vonatkozó példa a következő lépésben jelenik meg.

   CREATE FUNCTION dbo.tfnGetRecentAddress_inline
   (@ID INT)
   RETURNS TABLE
   AS
   RETURN
       (SELECT TOP 1 [AddressLine1] AS [Address]
        FROM [Person].[Address]
        WHERE AddressID = @ID
        ORDER BY [ModifiedDate] DESC)
   

2. Ha összetettebb, érdemes lehet Memory-Optimized táblákban vagy ideiglenes táblákban tárolt köztes eredményeket használni.

Kapcsolódó tartalom

• Legjobb gyakorlatok a számítási feladatok figyelésére a Lekérdezéstárral
• Mintaadatbázis In-Memory OLTP-hez
• Felhasználó által definiált függvények
• táblaváltozók és sorbecslések – 1. rész
• táblaváltozók és sorbecslések – 2. rész
• Végrehajtási terv gyorsítótárazása és újrafelhasználása