Megosztás a következőn keresztül:

Adatbázisok fájlterületének kezelése felügyelt Azure SQL-példányban

  • Cikk

A következőre vonatkozik: Felügyelt Azure SQL-példány

Ez a cikk bemutatja, hogyan monitorozhat és kezelhet fájlokat az adatbázisokban a felügyelt Azure SQL-példányokban. Áttekintjük az adatbázisfájl méretének monitorozását, a tranzakciónapló zsugorítását, a tranzakciónapló-fájl nagyítását és a tranzakciónapló-fájlok növekedésének szabályozását.

Ez a cikk a felügyelt Azure SQL-példányokra vonatkozik. Bár nagyon hasonló, a tranzakciós naplófájlok méretének az SQL Serverben való kezelésével kapcsolatos információkért lásd : A tranzakciónapló-fájl méretének kezelése.

Az adatbázis tárhelytípusainak ismertetése

Az adatbázis fájlterületének kezeléséhez fontos a következő tárterület-mennyiségek ismerete.

Az adatbázis mennyisége Definíció Megjegyzések
Felhasznált adattér Az adatbázisadatok tárolására használt terület. A felhasznált terület általában nő (csökken) a beszúrásokon (törléseken). Bizonyos esetekben a felhasznált terület nem változik a beszúrások és törlések esetében a műveletben érintett adatok mennyiségétől és mintájától, valamint a töredezettségtől függően. Ha például minden adatoldalról töröl egy sort, az nem feltétlenül csökkenti a felhasznált területet.
Lefoglalt adattér Az adatbázisadatok tárolásához elérhetővé tett formátumfájl területének mennyisége. A lefoglalt terület mennyisége automatikusan nő, de a törlés után soha nem csökken. Ez a viselkedés biztosítja, hogy a jövőbeli beszúrások gyorsabbak legyenek, mivel a helyet nem kell újraformálni.
Lefoglalt, de nem használt adatterület A lefoglalt és a felhasznált adatterület közötti különbség. Ez a mennyiség az adatbázis-adatfájlok zsugorításával visszanyerhető szabad terület maximális mennyiségét jelöli.
Adat maximális mérete Az adatbázisadatok tárolására használható maximális terület. A lefoglalt adatterület nem haladhatja meg a maximális adatméretet.

Az alábbi ábra az adatbázis különböző tárolási típusai közötti kapcsolatot szemlélteti.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Egyetlen adatbázis lekérdezése fájltérinformációkhoz

A sys.database_files a következő lekérdezés használatával adja vissza a lefoglalt adatbázisfájl-területet és a lefoglalt fel nem használt területet. A lekérdezés eredményeinek mértékegysége a MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Naplóterület használatának figyelése

Naplóterület használatának monitorozása sys.dm_db_log_space_usage használatával. Ez a DMV a jelenleg használt naplóterület mennyiségéről ad vissza információt, és jelzi, hogy a tranzakciónapló mikor igényel csonkolást.

Az aktuális naplófájl méretével, maximális méretével és a fájl automatikus kitöltési beállításával kapcsolatos információkért használhatja az adott naplófájlhoz tartozó , max_sizeés growth oszlopokat is a sizesys.database_files.

Az Azure Resource Manager-alapú metrikák API-jaiban megjelenített tárhelymetrikák csak a használt adatlapok méretét mérik. Példákat a PowerShell get-metrics című témakörben talál.

Naplófájl méretének csökkentése

Ha csökkenteni szeretné a fizikai naplófájlok fizikai méretét a nem használt terület eltávolításával, csökkentse a naplófájl méretét. A zsugorítás csak akkor tesz különbséget, ha egy tranzakciónapló-fájl nem használt területet tartalmaz. Ha a naplófájl megtelt, valószínűleg a nyitott tranzakciók miatt, vizsgálja meg , mi akadályozza a tranzakciónapló csonkolását.

Figyelmeztetés

A zsugorítási műveletek nem tekinthetők rendszeres karbantartási műveletnek. A rendszeres, ismétlődő üzleti műveletek miatt növekvő adat- és naplófájlok nem igényelnek zsugorítási műveleteket. A zsugorítási parancsok hatással vannak az adatbázis-teljesítményre a futtatásuk során, és lehetőség szerint alacsony használatú időszakokban ajánlott futtatni őket. Nem javasolt csökkenteni az adatfájlok méretét, ha az alkalmazás általános működése során a fájlok mérete ismét megnő, és ugyanannyi lefoglalt területet fognak igényelni.

Vegye figyelembe az adatbázisfájlok zsugorításának lehetséges negatív teljesítményhatását, lásd : Indexkarbantartás zsugorodás után. Ritkán a zsugorítási műveleteket az automatikus adatbázis-biztonsági mentések befolyásolhatják. Szükség esetén próbálkozzon újra a zsugorítási művelettel.

A tranzakciónapló zsugorítása előtt vegye figyelembe azokat a tényezőket, amelyek késleltethetik a napló csonkolását. Ha egy napló zsugorítása után ismét szükség van a tárterületre, a tranzakciónapló újra nő, és ezzel teljesítményterhelést okoz a naplónövelési műveletek során. További információ: Javaslatok.

A naplófájlokat csak akkor zsugoríthatja, ha az adatbázis online állapotban van, és legalább egy virtuális naplófájl (VLF) ingyenes. Bizonyos esetekben előfordulhat, hogy a napló zsugorítása csak a következő naplófuttatás után lehetséges.

Az olyan tényezők, mint a hosszú ideig futó tranzakció, hosszabb ideig aktívak maradhatnak a virtuális gépeken, korlátozhatják a napló zsugorodását, vagy egyáltalán megakadályozhatják a napló zsugorodását. További információt a napló csonkolását késleltető tényezők című témakörben talál.

A naplófájlok zsugorítása eltávolít egy vagy több olyan virtuális merevlemezt, amely nem rendelkezik a logikai napló egy részével (azaz inaktív VLF-ekkel). A tranzakciónapló-fájlok zsugorításakor a rendszer eltávolítja az inaktív virtuális gépeket a naplófájl végéről, hogy a napló körülbelül a célméretre csökkenjen.

A zsugorítási műveletekkel kapcsolatos további információkért tekintse át a következőket:

Naplófájl zsugorítása (adatbázisfájlok zsugorítása nélkül)

Naplófájlok zsugorítási eseményeinek monitorozása

Naplóterület figyelése

Indexkarbantartás zsugorodás után

Az adatfájlokon végzett zsugorítási művelet után az indexek töredezetté válhatnak. Ez csökkenti a teljesítményoptimalizálás hatékonyságát bizonyos számítási feladatok, például a nagy vizsgálatokat használó lekérdezések esetében. Ha a teljesítménycsökkenés a zsugorítási művelet befejezése után következik be, fontolja meg az indexkarbantartást az indexek újraépítéséhez. Ne feledje, hogy az index-újraépítések szabad helyet igényelnek az adatbázisban, ezért a lefoglalt terület növekedhet, ami ellensúlyozza a zsugorodás hatását.

Az indexkarbantartásról további információt az indexkarbantartás optimalizálása a lekérdezési teljesítmény javítása és az erőforrás-felhasználás csökkentése érdekében című témakörben talál.

Indexlap sűrűségének kiértékelése

Ha az adatfájlok csonkolása nem eredményezte a lefoglalt terület megfelelő csökkenését, az adatbázis-adatfájlok zsugorítása mellett dönthet úgy, hogy a fel nem használt területet visszaigényeli ezekből a fájlokból. Nem kötelező, de ajánlott lépésként azonban először meg kell határoznia az adatbázis indexeinek átlagos oldalsűrűségét. Azonos mennyiségű adat esetén a zsugorodás gyorsabban befejeződik, ha az oldalsűrűség magas, mivel kevesebb oldalt kell áthelyeznie. Ha egyes indexek esetében alacsony az oldalsűrűség, érdemes lehet karbantartást végezni ezeken az indexeken, hogy növelje az oldalsűrűséget az adatfájlok zsugorítása előtt. Ez lehetővé teszi a zsugorítást a lefoglalt tárterület mélyebb csökkentése érdekében.

Az adatbázis összes indexe oldalsűrűségének meghatározásához használja az alábbi lekérdezést. Az oldalsűrűség az oszlopban avg_page_space_used_in_percent van feltüntetve.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Ha vannak olyan indexek, amelyeknek az oldalsűrűsége 60-70%-nál alacsonyabb, érdemes újraépíteni vagy átrendezni ezeket az indexeket az adatfájlok zsugorítása előtt.

Megjegyzés:

Nagyobb adatbázisok esetén az oldalsűrűséget meghatározó lekérdezés végrehajtása hosszú időt (órákat) vehet igénybe. Emellett a nagy indexek újraépítése vagy átrendezése jelentős időt és erőforrás-használatot is igényel. Az oldalsűrűség növelésével, a zsugorítás időtartamának csökkentésével és a nagyobb helytakarékosság elérésével egy másik oldalon kompromisszumot lehet elérni.

Ha több, alacsony lapsűrűségű index van, a folyamat felgyorsítása érdekében több adatbázis-munkamenetben is újraépítheti őket párhuzamosan. Győződjön meg arról, hogy ezzel nem közelíti meg az adatbázis erőforráskorlátait, és hagyja meg a megfelelő erőforrás-fejteret az esetleg futó alkalmazások számítási feladataihoz. Figyelje az erőforrás-felhasználást (CPU, adat IO, napló IO) az Azure Portalon vagy a sys.dm_db_resource_stats nézet használatával, és csak akkor kezdjen további párhuzamos újraépítéseket, ha az erőforrások kihasználtsága ezen dimenziók mindegyikén lényegesen alacsonyabb, mint 100%. Ha a processzor-, adat-IO- vagy napló-IO-kihasználtság 100%-os, felskálázhatja az adatbázist, hogy több processzormaggal rendelkezzen, és növelje az IO átviteli sebességét. Ez további párhuzamos újraépítéseket is lehetővé tehet a folyamat gyorsabb befejezéséhez.

Mintaindex újraépítési parancsa

Az alábbiakban egy mintaparancsot követünk egy index újraépítéséhez és az oldalsűrűség növeléséhez az ALTER INDEX utasítás használatával:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

Ez a parancs online és újrakezdhető index-újraépítést kezdeményez. Ez lehetővé teszi, hogy az egyidejű számítási feladatok továbbra is használják a táblát, amíg az újraépítés folyamatban van, és lehetővé teszi az újraépítés folytatását, ha bármilyen okból megszakad. Az ilyen típusú újraépítés azonban lassabb, mint egy offline újraépítés, amely letiltja a táblához való hozzáférést. Ha más számítási feladatoknak nem kell hozzáférnie a táblához az újraépítés során, állítsa be és RESUMABLE távolítsa el a WAIT_AT_LOW_PRIORITY záradékot és a ONLINE beállításokatOFF.

Az indexkarbantartásról további információt az indexkarbantartás optimalizálása a lekérdezési teljesítmény javítása és az erőforrás-felhasználás csökkentése érdekében című témakörben talál.

Több adatfájl zsugorítása

Ahogy korábban már említettük, az adatáthelyezéssel való zsugorodás egy hosszú ideig futó folyamat. Ha az adatbázis több adatfájllal rendelkezik, felgyorsíthatja a folyamatot több adatfájl párhuzamos zsugorításával. Ezt úgy teheti meg, hogy több adatbázis-munkamenetet nyit meg, és minden munkameneten más file_id értéket használDBCC SHRINKFILE. Az indexek korábbi újraépítéséhez hasonlóan minden új párhuzamos zsugorítási parancs indítása előtt győződjön meg arról, hogy elegendő erőforrás-kezelőtér (CPU, Adat IO, Napló IO) áll rendelkezésre.

Az alábbi mintaparancs 4-zel file_id zsugorítja az adatfájlt, és 52 000 MB-ra próbálja csökkenteni a lefoglalt méretet a fájlon belüli oldalak áthelyezésével:

DBCC SHRINKFILE (4, 52000);

Ha a lehető legkisebbre szeretné csökkenteni a fájl lefoglalt területét, a célméret megadása nélkül hajtsa végre az utasítást:

DBCC SHRINKFILE (4);

Ha egy számítási feladat egyidejűleg zsugorítással fut, a zsugorítással felszabadított tárterületet használhatja, mielőtt a zsugorítás befejeződik, és csonkolja a fájlt. Ebben az esetben a zsugorítás nem tudja csökkenteni a megadott cél számára lefoglalt területet.

Ezt csökkentheti az egyes fájlok kisebb lépésekben történő zsugorításával. Ez azt jelenti, hogy a DBCC SHRINKFILE parancsban a célérték valamivel kisebb, mint a fájl aktuális lefoglalt területe. Ha például a 4-es file_id fájl lefoglalt területe 200 000 MB, és 100 000 MB-ra szeretné csökkenteni, a célértéket először 170 000 MB-ra állíthatja be:

DBCC SHRINKFILE (4, 170000);

A parancs befejeződése után csonkolja a fájlt, és 170 000 MB-ra csökkentette a lefoglalt méretét. Ezután megismételheti ezt a parancsot, a célértéket először 140 000 MB-ra, majd 110 000 MB-ra stb. állíthatja be, amíg a fájl a kívánt méretre nem csökken. Ha a parancs befejeződött, de a fájl nem csonkolt, használjon kisebb lépéseket, például 30 000 MB helyett 15 000 MB-ot.

Az egyidejűleg futó zsugorodási munkamenetek zsugorítási folyamatának figyeléséhez használja a következő lekérdezést:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Megjegyzés:

Előfordulhat, hogy a zsugorítási folyamat nem lineáris, és az percent_complete oszlop értéke hosszú ideig gyakorlatilag változatlan marad, annak ellenére, hogy a zsugorítás még folyamatban van.

Ha a zsugorítás befejeződött az összes adatfájl esetében, a helyhasználati lekérdezéssel állapítsa meg a lefoglalt tárterület méretének csökkenését. Ha továbbra is nagy a különbség a használt terület és a lefoglalt terület között, újraépítheti az indexeket. Ez ideiglenesen tovább növelheti a lefoglalt területet, azonban az indexek újraépítése után az adatfájlok újra zsugorítása a lefoglalt terület mélyebb csökkenését eredményezheti.

Naplófájl nagyítása

A felügyelt Azure SQL-példányban adjon helyet egy naplófájlhoz a meglévő naplófájl hozzáadásával (ha a lemezterület megengedi). Nem támogatott naplófájl hozzáadása az adatbázishoz. Egy tranzakciós naplófájl elegendő, kivéve, ha elfogy a naplóterület, és a lemezterület is elfogy a naplófájlt tartalmazó köteten.

A naplófájl nagyításához használja az MODIFY FILE utasítás záradékátALTER DATABASE, és adja meg a SIZE szintaxist.MAXSIZE További információ: ALTER DATABA Standard kiadás (Transact-SQL) Fájl- és Fájlcsoportbeállítások.

További információ: Javaslatok.

Tranzakciónapló-fájlok növekedésének szabályozása

A tranzakciónapló-fájlok növekedésének kezeléséhez használja az ALTER DATABA Standard kiadás (Transact-SQL) fájl- és fájlcsoportbeállítási utasítást. Vegye figyelembe a következőket:

  • Az aktuális fájlméret kb-, MB-, GB- és TB-egységekben való módosításához használja a SIZE lehetőséget.
  • A növekedési növekedés módosításához használja a FILEGROWTH lehetőséget. A 0 érték azt jelzi, hogy az automatikus növekedés ki van kapcsolva, és nincs további hely.
  • Ha a naplófájlok maximális méretét szeretné szabályozni KB, MB, GB és TB egységekben, vagy korlátlan értékűre szeretné állítani a növekedést, használja a MAXSIZE lehetőséget.

Javaslatok

A tranzakciónapló-fájlok használatakor az alábbiakban néhány általános javaslatot lásunk:

  • A tranzakciónapló automatikus növekedésének (automatikus növekedésének) a beállításnak FILEGROWTH megfelelően elég nagynak kell lennie ahhoz, hogy a számítási feladatok tranzakcióinak igényei előtt maradjon. A naplófájlok fájlnövekményének elég nagynak kell lennie a gyakori bővítés elkerülése érdekében. A tranzakciónapló megfelelő méretére mutató jó mutató a következő időszakban elfoglalt naplók mennyiségének monitorozása:

    • A teljes biztonsági mentés végrehajtásához szükséges idő, mert a naplók biztonsági mentései csak akkor következnek be, ha az befejeződik.
    • A legnagyobb indexkarbantartási műveletekhez szükséges idő.
    • Az adatbázis legnagyobb kötegének végrehajtásához szükséges idő.

  • Ha a beállítással automatikusan beállítja az FILEGROWTH adatok és naplófájlok automatikus létrehozását, érdemes lehet ahelyettpercentage, size hogy a növekedési arány jobb szabályozását tenné lehetővé, mivel a százalékos érték egyre nő.

    • A felügyelt Azure SQL-példányban az azonnali fájl inicializálása akár 64 MB-os tranzakciónapló-növekedési eseményeket is igénybe vehet. Az új adatbázisok alapértelmezett automatikus növekedési méretnövekedése 64 MB. A tranzakciónapló-fájl 64 MB-nál nagyobb eseményeinek automatikus növelése nem előnyös az azonnali fájl inicializálásából.
    • Ajánlott eljárásként ne állítsa be a FILEGROWTH beállítás értékét 1024 MB fölé a tranzakciónaplók esetében.

  • Egy kis automatikus növekedés túl sok kis virtuális merevlemezt generálhat, és csökkentheti a teljesítményt. Az adott példány összes adatbázisának aktuális tranzakciónapló-méretéhez tartozó optimális VLF-eloszlás és a szükséges növekedési lépések meghatározásához tekintse meg ezt a szkriptet a virtuális merevlemezek elemzéséhez és javításához, amelyet az SQL Tiger Team biztosít.

  • A nagy automatikus növekedés két problémát okozhat:

    • Egy nagy automatikus növekedés az adatbázis szüneteltetését okozhatja az új terület lefoglalása közben, ami lekérdezési időtúllépéseket okozhat.
    • A nagy automatikus növekedés túl kevés és nagy virtuális merevlemezt hozhat létre, és hatással lehet a teljesítményre is. Az adott példány összes adatbázisának aktuális tranzakciónapló-méretéhez tartozó optimális VLF-eloszlás és a szükséges növekedési lépések meghatározásához tekintse meg ezt a szkriptet a virtuális merevlemezek elemzéséhez és javításához, amelyet az SQL Tiger Team biztosít.

  • Ha az automatikus növekedés engedélyezve van, akkor is kaphat egy üzenetet, hogy a tranzakciónapló megtelt, ha az nem tud elég gyorsan növekedni a lekérdezés igényeinek kielégítéséhez. A növekedési növekmény módosításáról további információt az ALTER DATABA Standard kiadás (Transact-SQL) fájl- és fájlcsoportbeállításokban talál.

  • A naplófájlok automatikusan zsugorodhatnak. Ez azonban nem ajánlott, és a auto_shrink adatbázis tulajdonsága alapértelmezés szerint FAL Standard kiadás értékre van állítva. Ha auto_shrink ÉRTÉKE IGAZ, az automatikus zsugorítás csak akkor csökkenti a fájl méretét, ha a tárhely több mint 25 százaléka nincs használatban.

Következő lépések