ApsaraDB RDS:Insufficient space issues for RDS for SQL Server

tempdb veritabanı, SQL Server'da geçici verileri depolayan bir sistem veritabanıdır. tempdb içindeki veri dosyası alanı, aşağıdakiler gibi birçok senaryoda sıklıkla kullanılır:

• Kullanıcı nesneleri: Kullanıcılar tarafından oluşturulan geçici tablolar.

• Dahili nesneler: SQL Server tarafından dahili olarak oluşturulan geçici tablolar.

• Sürüm deposu: Bir veritabanı için anlık görüntü izolasyonu veya read committed snapshot etkinleştirildiğinde, sürüm oluşturma bilgileri tempdb içinde depolanır.

Uzun süren işlemler, çok sayıda geçici tablo oluşturulması veya anlık görüntü izolasyonu gibi belirli işlemler büyük miktarda alan kullanırsa veri dosyası şişebilir. Bu durum, dosya boyutunun normal aralığının çok ötesine büyümesi anlamına gelir. Daha fazla bilgi için bkz. tempdb veritabanı için resmi Microsoft öğreticisi.

Verileri arşivleme veya silme

Eski geçmiş kayıtlar gibi nadiren erişilen verileri başka bir örneğe veya arşiv formatına silin veya taşıyın. Bu işlem, kullanılan veri alanını doğrudan azaltır ve en etkili uzun vadeli çözümdür, ancak uygulama mantığında ve veritabanı tasarımında değişiklikler gerektirir.

Verileri sıkıştırma

SQL Server 2016 veya sonraki sürümleri çalıştıran örnekler ve daha eski sürümleri çalıştıran Enterprise Edition örnekleri, tablo, dizin veya bölüm düzeyinde satır ve sayfa sıkıştırmayı destekler. Ayrıntılar için bkz. Veri sıkıştırma.

Tasarruflar şemaya, sütun türlerine ve veri dağılımına bağlı olarak büyük ölçüde değişir (%10–%90). Sıkıştırmayı etkinleştirmeden önce tasarrufları tahmin etmek için sp_estimate_data_compression_savings kullanın.

Sıkıştırma ayarlarını değiştirmek bir Veri Tanımlama Dili (DDL) işlemidir. Büyük tablolarda bu işlem, uzun süren tablo kilitlemelerine neden olabilir. Yoğun olmayan saatlerde çalıştırın.

Enterprise Edition örneklerinde, iş etkisini en aza indirmek için ONLINE = ON ayarlayın.

Sıkıştırma CPU yükünü artırır. Yalnızca alan tasarrufunun maliyeti haklı çıkardığı büyük tablolarda etkinleştirin.

Dizini parçalama giderme

Yüksek dizin parçalanması sorguları yavaşlatır ve depolamayı boşa harcar. Parçalanma oranlarını görüntülemek için Autonomy Services > Performance Optimization bölümüne gidin ve Index Usage sekmesine tıklayın. Konsol, her tablo için parçalanma oranını gösterir ve her dizini yeniden oluşturup yeniden düzenlemeniz gerekip gerekmediğini önerir.

Dizin parçalanma oranı, mantıksal olarak bitişik olan dizin sayfalarının fiziksel olarak bitişik olmama yüzdesini ölçer. Bu, sayfalar içindeki boş alan yüzdesinden farklıdır. Sayfa başına ortalama boş alanı ölçmek için sys.dm_db_index_physical_stats öğesini SAMPLED veya DETAILED modunda sorgulayın ve avg_page_space_used_in_percent sütununu kontrol edin. Ayrıntılar için bkz. sys.dm_db_index_physical_stats (Transact-SQL) . Bu sorgu birçok dizin sayfasını okur; yoğun olmayan saatlerde çalıştırın.

Dizini yeniden oluşturma (REBUILD): Dizini tamamen yeniden oluşturur. Yüksek parçalanmaya sahip dizinler için yeniden düzenlemeden daha etkilidir. Varsayılan olarak, işlem sırasında tabloyu kilitler. Enterprise Edition örneklerinde, uzun tablo kilitlerinden kaçınmak için ONLINE = ON ayarlayın.

ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

Yeniden oluşturma işleminden sonra konsol dizin istatistiklerini eşzamansız olarak günceller. En son verileri almak için Recollect düğmesine tıklayın, ardından parçalanma oranını doğrulamak için Export Script düğmesine tıklayarak betiği indirin.

Dizini yeniden düzenleme (REORGANIZE): Tabloyu kilitlemeden yaprak düzeyi sayfaları yeniden yapılandırır. Yeniden oluşturmaya göre daha az etkilidir, ancak düşük parçalanmaya sahip dizinler için uygundur.

-- This script applies only to SQL Server 2012 and later versions. Specify the following parameters before running.
DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- Database name
DECLARE @fileName NVARCHAR(128)               -- Data file name
DECLARE @targetSize INT = 2000                -- Target size (MB)
DECLARE @shrinkSize INT = 5120                -- Size to shrink per iteration (MB). 5 GB recommended.
DECLARE @currentSize INT                      -- Current file size
DECLARE @freeSize INT                         -- Unallocated space
DECLARE @usedSize INT                         -- Used space

DECLARE @sql NVARCHAR(500)
DECLARE @waitTime INT = 10                    -- Wait between iterations (seconds)

-- Get the data file name
SELECT @fileName = name
FROM sys.master_files
WHERE database_id = DB_ID(@dbName)
AND type_desc = 'ROWS'

-- Shrink in a loop
WHILE 1 = 1
BEGIN
    -- Get current size and space breakdown
    DECLARE @sql0 NVARCHAR(MAX) = N'
    USE [' + @dbName + '];
    SELECT
      @currentSize = (SUM(total_page_count) * 1.0 / 128),
      @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
    FROM sys.dm_db_file_space_usage
    WHERE database_id = DB_ID();'

    EXEC sp_executesql
      @sql0,
      N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
      @currentSize OUTPUT,
      @freesize OUTPUT

    PRINT 'Current size:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
    PRINT 'Free size:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'

    SET @usedSize = @currentSize - @freeSize
    PRINT 'Used size:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'

    -- Validate target size
    IF @targetSize <= @usedSize
    BEGIN
        PRINT 'The target size is too small. Specify a new size. The target size cannot be smaller than: ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Exit when target is reached
    IF @currentSize <= @targetSize
    BEGIN
        PRINT 'Shrink completed. Current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Calculate the new size for this iteration
    DECLARE @newSize INT = @currentSize - @shrinkSize
    IF @newSize < @targetSize
        SET @newSize = @targetSize

    -- Run the shrink
    SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
    PRINT 'Executing shrink: ' + @sql
    EXEC (@sql)

    -- Wait before the next iteration
    PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds to continue...'
    WAITFOR DELAY '00:00:10'
END

DBCC SHRINKFILE(<File ID>, <Target size in MB>)

Yaygın veri alanı sorunlarını giderme

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;

Senaryo 1: tempdb şu anda büyük (canlı örnek)

Adım 1 — Nesne türüne göre alanı kontrol etme

Her kategoriye (kullanıcı nesneleri, dahili nesneler, sürüm deposu) ne kadar alan tüketildiğini görmek için aşağıdaki sorguyu çalıştırın:

SELECT
    SUM(version_store_reserved_page_count) AS [version store pages used],
    (SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version store space (MB)],
    SUM(user_object_reserved_page_count) AS [user object pages used],
    (SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_object_reserved_page_count) AS [internal object pages used],
    (SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_file_space_usage;

Referans için bkz. sys.dm_db_file_space_usage.

Adım 2a — Kullanıcı nesneleri veya dahili nesneler büyükse

En çok tempdb alanı kullanan oturumları bulun:

SELECT
    session_id,
    SUM(user_objects_alloc_page_count) AS [user object pages used],
    (SUM(user_objects_alloc_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_objects_alloc_page_count) AS [internal object pages used],
    (SUM(internal_objects_alloc_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_session_space_usage
GROUP BY
    session_id;

Referans için bkz. sys.dm_db_session_space_usage.

Ardından, oturumun çalıştırdığı SQL ifadesini bulmak için session_id değerini kullanın:

SELECT
    r.session_id AS [SPID],
    r.start_time AS [Start Time],
    r.status AS [Status],
    r.command AS [Command Type],
    r.wait_type AS [Wait Type],
    r.wait_time AS [Wait Time (ms)],
    r.last_wait_type AS [Last Wait Type],
    t.text AS [Executing Statement]
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE
    r.session_id = <session_id>; -- Replace with the session_id from the previous query

Adım 2b — Sürüm deposu büyükse

Veritabanında muhtemelen temizlemeyi engelleyen uzun süren işlemlerle birlikte anlık görüntü izolasyonu etkindir.

Anlık görüntü izolasyonunun hangi veritabanlarda etkin olduğunu kontrol edin:

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state
FROM sys.databases;

is_read_committed_snapshot_on veya snapshot_isolation_state değeri 1 ise, anlık görüntü izolasyonu aktiftir.

Sürüm deposu temizlemesini engelleyen, taahhüt edilmemiş uzun süren işlemlere sahip oturumları bulun:

SELECT * FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

Oturumun ne yaptığını kontrol etmek için session_id değerini kullanın:

SELECT * FROM sys.sysprocesses WHERE spid = <session_id>;

Oturum durumu sleeping ise, son yürütülen ifadeyi kontrol edin:

DBCC INPUTBUFFER(<session_id>);

Senaryo 2: Yeniden başlatmadan sonra geçmiş tempdb büyümesini araştırma

Örnek zaten yeniden başlatıldıysa ve canlı veriler mevcut değilse, ne olduğunu yeniden yapılandırmak için Ortalama Etkin Oturumlar (AAS), yavaş sorgu günlükleri ve denetim günlüklerini kullanın.

Adım 1 — Büyüme penceresini bulma

Örnek ayrıntıları sayfasında Monitoring and Alerts > Standard Monitoring bölümüne gidin. Instance Storage bölümünde, tmp_size ölçümünün yükselmeye başladığı ve durduğu zamanları not edin. tempdb öncelikle mevcut boş alanı yeniden kullanabilir; gerçek dosya genişletmesi yalnızca mevcut alan tükendiğinde gerçekleşir, bu nedenle grafikteki büyüme SQL işleminin başlangıcından gecikebilir.

Adım 2 — AAS kullanarak analiz etme

Autonomy Services > Performance Optimization > Performance Insight bölümüne gidin. İlgili tüm işlemleri yakalamak için başlangıç zamanını daha önceye uzatarak zaman penceresini seçin. Disk tabanlı geçici tabloları yoğun kullanan SQL ifadelerini arayın (örneğin, # tablolarının sık oluşturulması).

Adım 3 — Yavaş sorgu günlüklerini anahtar kelimeye göre filtreleme

Autonomy Services > Slow Query Logs bölümüne gidin. Anahtar kelimeye göre arama yapın (örneğin, bir geçici tablo adı). Sorgu başlangıç zamanlarını ve sürelerini kontrol ederek sorgu bitiş zamanının tempdb büyümeyi durdurduğu zamanla hizalanıp hizalanmadığını görün.

USE tempdb;
DBCC SHRINKFILE(templog, 1);
GO