Aracılığıyla paylaş

Azure SQL Yönetilen Örneği veritabanları için dosya alanını yönetme

  • Makale

Şunlar için geçerlidir: Azure SQL Yönetilen Örneği

Bu makale, Azure SQL Yönetilen Örneği veritabanlarındaki dosyaların nasıl izleneceğini ve yönetileceğini kapsar. Veritabanı dosya boyutunu izlemeyi, işlem günlüğünü küçültmeyi, işlem günlüğü dosyasını büyütmeyi ve işlem günlüğü dosyasının büyümesini denetlemeyi gözden geçiriyoruz.

Bu makale Azure SQL Yönetilen Örneği için geçerlidir. Çok benzer olsa da, SQL Server'da işlem günlüğü dosyalarının boyutunu yönetme hakkında bilgi için bkz . İşlem günlüğü dosyasının boyutunu yönetme.

Veritabanı için depolama alanı türlerini anlama

Aşağıdaki depolama alanı miktarlarını anlamak, veritabanının dosya alanını yönetmek için önemlidir.

Veritabanı miktarı Tanım Yorumlar
Kullanılan veri alanı Veritabanı verilerini depolamak için kullanılan alan miktarı. Genel olarak, kullanılan alan eklemelerde (silmelerde) artar (azalır). Bazı durumlarda, işlemdeki verilerin miktarına ve düzenine ve herhangi bir parçalanmaya bağlı olarak eklemelerde veya silmelerde kullanılan alan değişmez. Örneğin her veri sayfasından bir satır silindiğinde kullanılan alan azalmayabilir.
Ayrılan veri alanı Veritabanı verilerinin depolanması için kullanılabilir hale getirilen biçimlendirilmiş dosya alanı miktarı. Ayrılan alan miktarı otomatik olarak artar ancak silme işlemlerinden sonra azalmaz. Bu davranış, alanın yeniden biçimlendirilmesi gerekmediğinden gelecekteki eklemelerin daha hızlı olmasını sağlar.
Ayrılan ancak kullanılmayan veri alanı Ayrılan veri alanı miktarıyla kullanılan veri alanı arasındaki fark. Bu miktar, veritabanı veri dosyalarının küçültülmesiyle geri kazanılabilecek maksimum boş alan miktarını temsil eder.
En büyük veri boyutu Veritabanı verilerini depolamak için kullanılabilecek maksimum alan miktarı. Ayrılan veri alanı miktarı, maksimum veri boyutunu aşamaz.

Aşağıdaki diyagramda, veritabanı için farklı depolama alanı türleri arasındaki ilişki gösterilmektedir.

Veritabanı miktarı tablosundaki fark veritabanı alanı kavramlarının boyutunu gösteren diyagram.

Dosya alanı bilgileri için tek bir veritabanını sorgulama

Ayrılan veritabanı dosya alanı miktarını ve ayrılan kullanılmayan alan miktarını döndürmek için sys.database_files aşağıdaki sorguyu kullanın. Sorgu sonucundaki değerler MB olarak döndürülür.

-- 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;

Günlük alanı kullanımını izleme

sys.dm_db_log_space_usage kullanarak günlük alanı kullanımını izleyin. Bu DMV, şu anda kullanılan günlük alanı miktarı hakkında bilgi döndürür ve işlem günlüğünün ne zaman kesilmesi gerektiğini gösterir.

Geçerli günlük dosyası boyutu, en büyük boyutu ve dosyanın otomatik büyütme seçeneği hakkında bilgi için, sys.database_files'da söz konusu günlük dosyasının , max_sizeve growth sütunlarını da kullanabilirsinizsize.

Azure Resource Manager tabanlı ölçümLER API'lerinde görüntülenen depolama alanı ölçümleri yalnızca kullanılan veri sayfalarının boyutunu ölçer. Örnekler için bkz. PowerShell get-metrics.

Günlük dosyası boyutunu küçültme

Kullanılmayan alanı kaldırarak fiziksel günlük dosyasının fiziksel boyutunu küçültmek için günlük dosyasını küçültün. Küçültme yalnızca işlem günlüğü dosyası kullanılmayan alan içerdiğinde fark yaratır. Büyük olasılıkla açık işlemler nedeniyle günlük dosyası doluysa işlem günlüğünün kesilmesini neyin engellediğini araştırın.

Dikkat

Küçültme işlemleri normal bir bakım işlemi olarak kabul edilmemelidir. Düzenli, yinelenen iş işlemleri nedeniyle büyüyen veri ve günlük dosyaları için küçültme işlemleri gerekmez. Küçültme komutları çalışan veritabanlarının performansını etkiler ve mümkünse düşük kullanım dönemlerinde çalıştırılmalıdır. Normal uygulama iş yükü dosyaların yeniden aynı ayrılan boyuta kadar büyümesine neden olacaksa veri dosyalarını küçültmeniz önerilmez.

Veritabanı dosyalarını küçültmenin olası olumsuz performans etkisinin farkında olun, bkz . Küçültme sonrasında dizin bakımı. Nadir durumlarda, küçültme işlemleri otomatik veritabanı yedeklemelerinden etkilenebilir. Gerekirse küçültme işlemini yeniden deneyin.

İşlem günlüğünü küçültmeden önce günlük kesilmesini geciktirebilecek faktörleri aklınızda bulundurun. Günlük küçülttükten sonra depolama alanı yeniden gerekiyorsa işlem günlüğü yeniden büyür ve bunu yaparak günlük büyüme işlemleri sırasında performans ek yüküne neden olur. Daha fazla bilgi için bkz . Öneriler.

Günlük dosyasını yalnızca veritabanı çevrimiçiyken küçültebilirsiniz ve en az bir sanal günlük dosyası (VLF) ücretsizdir. Bazı durumlarda, bir sonraki günlük kesilmesinden sonra günlüğü küçültmek mümkün olmayabilir.

Uzun süre çalışan bir işlem gibi faktörler VLF'leri uzun süre etkin tutabilir, günlük küçültmeyi kısıtlayabilir, hatta günlüğün küçülmesini engelleyebilir. Bilgi için bkz . Günlük kesilmesini geciktirebilecek faktörler.

Günlük dosyasını küçültmek, mantıksal günlüğün (etkin olmayan VLF'ler) bir bölümünü tutmayan bir veya daha fazla VLF'yi kaldırır. İşlem günlüğü dosyasını küçülttüğünüzde, günlüğü yaklaşık hedef boyuta küçültmek için etkin olmayan VLF'ler günlük dosyasının sonundan kaldırılır.

Küçültme işlemleri hakkında daha fazla bilgi için aşağıdakileri gözden geçirin:

Günlük dosyasını küçültme (veritabanı dosyalarını küçültmeden)

Günlük dosyası küçültme olaylarını izleme

Günlük alanını izleme

Küçültme sonrasında dizin bakımı

Veri dosyalarında küçültme işlemi tamamlandıktan sonra dizinler parçalanabilir. Bu, büyük taramalar kullanan sorgular gibi belirli iş yükleri için performans iyileştirme verimliliğini azaltır. Küçültme işlemi tamamlandıktan sonra performans düşüşü oluşursa dizinleri yeniden oluşturmak için dizin bakımını göz önünde bulundurun. Dizin yeniden derlemelerinin veritabanında boş alan gerektirdiğini ve bu nedenle ayrılan alanın artmasına neden olabileceğini ve bu nedenle küçültmenin etkisinin karşılandığını unutmayın.

Dizin bakımı hakkında daha fazla bilgi için bkz . Sorgu performansını geliştirmek ve kaynak tüketimini azaltmak için dizin bakımını iyileştirme.

Dizin sayfası yoğunluğu değerlendirme

Veri dosyalarının kesilmesi ayrılan alanda yeterli azalmaya neden olmadıysa, bu dosyalardan kullanılmayan alanı geri kazanmak için veritabanı veri dosyalarını küçültmeye karar vekleyebilirsiniz. Ancak, isteğe bağlı ancak önerilen bir adım olarak, önce veritabanındaki dizinler için ortalama sayfa yoğunluğu belirlemeniz gerekir. Aynı miktarda veri için, sayfa yoğunluğu yüksekse küçültme işlemi daha hızlı tamamlanır çünkü daha az sayfa taşıması gerekir. Bazı dizinlerde sayfa yoğunluğu düşükse, veri dosyalarını küçültmeden önce sayfa yoğunluğunun artırılması için bu dizinlerde bakım yapmayı göz önünde bulundurun. Bu, küçültmenin ayrılan depolama alanında daha derin bir azalma elde etmesine de olanak sağlar.

Veritabanındaki tüm dizinler için sayfa yoğunluğu belirlemek için aşağıdaki sorguyu kullanın. Sayfa yoğunluğu sütunda avg_page_space_used_in_percent bildirilir.

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;

Sayfa yoğunluğu %60-70'in altında olan yüksek sayfa sayısına sahip dizinler varsa, veri dosyalarını küçültmeden önce bu dizinleri yeniden oluşturmayı veya yeniden düzenlemeyi göz önünde bulundurun.

Not

Daha büyük veritabanlarında sayfa yoğunluğunun belirlenmesi için yapılan sorgunun tamamlanması uzun sürebilir. Ayrıca, büyük dizinleri yeniden oluşturmak veya yeniden düzenlemek için de önemli zaman ve kaynak kullanımı gerekir. Bir yandan sayfa yoğunluğunun artırılmasına ek zaman harcamanın, küçültme süresini azaltmanın ve diğer yandan daha yüksek alan tasarrufu elde etmenin bir dezavantajı vardır.

Düşük sayfa yoğunluğuna sahip birden çok dizin varsa, işlemi hızlandırmak için bunları birden çok veritabanı oturumunda paralel olarak yeniden derleyebilirsiniz. Ancak, bunu yaparak veritabanı kaynak sınırlarına yaklaşmadığınızdan emin olun ve uygulama iş yükleri için yeterli kaynak alanı bırakın. Azure portalında kaynak tüketimini (CPU, Veri GÇ, Günlük GÇ) izleyin veya sys.dm_db_resource_stats görünümünü kullanın ve yalnızca bu boyutların her birinde kaynak kullanımı %100'ün çok altında kaldığında ek paralel yeniden derlemeler başlatın. CPU, Veri GÇ veya Günlük GÇ kullanımı %100'deyse daha fazla CPU çekirdeğine sahip olmak ve GÇ aktarım hızını artırmak için veritabanının ölçeğini artırabilir ve ek paralel yeniden derlemelerin işlemi daha hızlı tamamlayabilmesini sağlayabilirsiniz.

Örnek dizin yeniden oluşturma komutu

Aşağıda ALTER INDEX deyimini kullanarak bir dizini yeniden derlemek ve sayfa yoğunluğunu artırmak için örnek bir komut verilmiştir:

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);

Bu komut, çevrimiçi ve devam ettirilebilen bir dizin yeniden derlemesi başlatır. Bu, yeniden oluşturma işlemi devam ederken eşzamanlı iş yüklerinin tabloyu kullanmaya devam etmesini sağlar ve herhangi bir nedenle kesintiye uğrarsa yeniden derlemeyi sürdürmenize olanak tanır. Ancak, bu tür yeniden derleme, tabloya erişimi engelleyen çevrimdışı yeniden derlemeden daha yavaştır. Yeniden oluşturma sırasında tabloya başka hiçbir iş yükünün erişmesi gerekmiyorsa ve RESUMABLE seçeneklerini olarak OFF ayarlayın ONLINE ve yan tümcesini WAIT_AT_LOW_PRIORITY kaldırın.

Dizin bakımı hakkında daha fazla bilgi edinmek için bkz . Sorgu performansını geliştirmek ve kaynak tüketimini azaltmak için dizin bakımını iyileştirme.

Birden çok veri dosyasını küçültme

Daha önce belirtildiği gibi, veri taşıma ile küçültme uzun süre çalışan bir işlemdir. Veritabanında birden çok veri dosyası varsa, birden çok veri dosyasını paralel olarak küçülterek işlemi hızlandırabilirsiniz. Bunu birden çok veritabanı oturumu açarak ve her oturumda farklı file_id bir değerle kullanarak DBCC SHRINKFILE yaparsınız. Daha önce dizinleri yeniden derlemeye benzer şekilde, her yeni paralel küçültme komutunu başlatmadan önce yeterli kaynak odanıza (CPU, Veri GÇ, Günlük GÇ) sahip olduğunuzdan emin olun.

Aşağıdaki örnek komut, ayrılan boyutunu dosya içinde taşıyarak 52.000 MB'a düşürmeye çalışarak veri dosyasını 4 ile file_id küçültür:

DBCC SHRINKFILE (4, 52000);

Dosya için ayrılan alanı mümkün olan en aza düşürmek istiyorsanız, hedef boyutu belirtmeden deyimini yürütün:

DBCC SHRINKFILE (4);

Bir iş yükü aynı anda küçültme ile çalışıyorsa, küçültme işlemi tamamlanmadan ve dosya kesilmeden önce küçültme tarafından boşaltılan depolama alanını kullanmaya başlayabilir. Bu durumda, küçültme işlemi belirtilen hedefe ayrılan alanı azaltamaz.

Her dosyayı daha küçük adımlarda küçülterek bunu azaltabilirsiniz. Bu, komutta DBCC SHRINKFILE dosya için ayrılan geçerli alandan biraz daha küçük olan hedefi ayarladığınız anlamına gelir. Örneğin, file_id 4 içeren dosya için ayrılan alan 200.000 MB ise ve 100.000 MB'a küçültmek istiyorsanız, önce hedefi 170.000 MB olarak ayarlayabilirsiniz:

DBCC SHRINKFILE (4, 170000);

Bu komut tamamlandıktan sonra dosyayı kesmiş ve ayrılmış boyutunu 170.000 MB'a düşürmüştür. Daha sonra bu komutu yineleyebilir, hedef önce 140.000 MB, ardından 110.000 MB vb. olarak ayarlanır ve dosya istenen boyuta küçülene kadar. Komut tamamlanırsa ancak dosya kesilmemişse, daha küçük adımlar kullanın; örneğin 30.000 MB yerine 15.000 MB.

Eşzamanlı olarak çalışan tüm küçültme oturumlarında küçültme ilerleme durumunu izlemek için aşağıdaki sorguyu kullanabilirsiniz:

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');

Not

Küçültme ilerlemesi doğrusal olmayabilir ve küçültme işlemi devam ediyor olsa bile sütundaki percent_complete değer uzun süreler boyunca neredeyse değişmeden kalabilir.

Tüm veri dosyaları için küçültme tamamlandıktan sonra, ayrılan depolama boyutundaki azalmayı belirlemek için alan kullanımı sorgusunu kullanın. Kullanılan alan ile ayrılan alan arasında hala büyük bir fark varsa, dizinleri yeniden oluşturabilirsiniz. Bu, ayrılan alanı geçici olarak artırabilir, ancak dizinleri yeniden derledikten sonra veri dosyalarının yeniden küçültülmesi ayrılan alanda daha derin bir azalmaya neden olmalıdır.

Günlük dosyasını büyütme

Azure SQL Yönetilen Örneği'da, var olan günlük dosyasını (disk alanı izin verirse) büyüyerek günlük dosyasına alan ekleyin. Veritabanına günlük dosyası ekleme desteklenmez. Günlük alanı tükenmedikçe ve günlük dosyasının bulunduğu birimde disk alanı da bitmediği sürece bir işlem günlük dosyası yeterlidir.

Günlük dosyasını büyütmek için ve MAXSIZE söz dizimini belirterek deyiminin ALTER DATABASE yan tümcesini SIZE kullanınMODIFY FILE. Daha fazla bilgi için bkz . ALTER DATABASE (Transact-SQL) Dosya ve Dosya Grubu seçenekleri.

Daha fazla bilgi için bkz . Öneriler.

İşlem günlüğü dosyasının büyümesini denetleme

İşlem günlüğü dosyasının büyümesini yönetmek için ALTER DATABASE (Transact-SQL) File and Filegroup options deyimini kullanın. Aşağıdakileri dikkate alın:

  • Geçerli dosya boyutunu KB, MB, GB ve TB birimlerinde değiştirmek için seçeneğini kullanın SIZE .
  • Büyüme artışını değiştirmek için seçeneğini kullanın FILEGROWTH . 0 değeri, otomatik büyümenin kapalı olarak ayarlandığını ve ek alan izin verilmediğini gösterir.
  • Kb, MB, GB ve TB birimlerinde günlük dosyasının en büyük boyutunu denetlemek veya büyümeyi SINIRSIZ olarak ayarlamak için seçeneğini kullanın MAXSIZE .

Öneriler

İşlem günlüğü dosyalarıyla çalışırken bazı genel öneriler aşağıdadır:

  • İşlem günlüğünün seçenek tarafından FILEGROWTH ayarlandığı şekilde otomatik büyüme (otomatik büyüme) artışı, iş yükü işlemlerinin gereksinimlerini karşılayacak kadar büyük olmalıdır. Günlük dosyasındaki dosya büyüme artışı, sık genişlemeyi önlemek için yeterince büyük olmalıdır. İşlem günlüğünü düzgün bir şekilde boyutlandırmak için iyi bir işaretçi, şu sırada kaplanan günlük miktarını izlemektir:

    • Günlük yedeklemeleri bitene kadar gerçekleşemeyeceğinden tam yedekleme yürütmek için gereken süre.
    • En büyük dizin bakım işlemleri için gereken süre.
    • Veritabanındaki en büyük toplu işlemi yürütmek için gereken süre.

  • Seçeneği kullanarak FILEGROWTH veri ve günlük dosyaları için otomatik büyütmeyi ayarlarken, büyüme oranı üzerinde daha iyi denetim sağlamak için yerine içinde ayarlamak size percentagetercih edilebilir, bunun nedeni yüzdenin sürekli büyüyen bir miktar olmasıdır.

    • Azure SQL Yönetilen Örneği'da, anlık dosya başlatma işlemi 64 MB'a kadar işlem günlüğü büyüme olaylarından yararlanabilir. Yeni veritabanları için varsayılan otomatik büyüme boyutu artışı 64 MB'tır. 64 MB'tan büyük işlem günlüğü dosyası otomatik büyütme olayları, anlık dosya başlatma işleminden yararlanamaz.
    • En iyi yöntem olarak, işlem günlükleri için seçenek değerini 1.024 MB'ın üzerine ayarlamayın FILEGROWTH .

  • Küçük bir otomatik büyüme artışı çok fazla küçük VLF oluşturabilir ve performansı düşürebilir. Belirli bir örnekteki tüm veritabanlarının geçerli işlem günlüğü boyutu için en uygun VLF dağılımını ve gerekli boyuta ulaşmak için gerekli büyüme artışlarını belirlemek için, SQL Tiger Ekibi tarafından sağlanan VLF'leri analiz etmek ve düzeltmek için bu betike bakın.

  • Büyük bir otomatik büyüme artışı iki soruna neden olabilir:

    • Büyük bir otomatik büyüme artışı, yeni alan ayrılırken veritabanının duraklatmasına neden olabilir ve bu da sorgu zaman aşımlarına neden olabilir.
    • Büyük bir otomatik büyüme artışı çok az ve büyük VLFs oluşturabilir ve performansı da etkileyebilir. Belirli bir örnekteki tüm veritabanlarının geçerli işlem günlüğü boyutu için en uygun VLF dağılımını ve gerekli boyuta ulaşmak için gerekli büyüme artışlarını belirlemek için, SQL Tiger Ekibi tarafından sağlanan VLF'leri analiz etmek ve düzeltmek için bu betike bakın.

  • Otomatik büyütme etkinleştirildiğinde bile, sorgunuzun gereksinimlerini karşılayacak kadar hızlı büyüyemediğinde işlem günlüğünün dolu olduğunu belirten bir ileti alabilirsiniz. Büyüme artışını değiştirme hakkında daha fazla bilgi için bkz . ALTER DATABASE (Transact-SQL) Dosya ve Dosya Grubu seçenekleri.

  • Günlük dosyaları otomatik olarak küçültülecek şekilde ayarlanabilir. Ancak bu önerilmez ve auto_shrink veritabanı özelliği varsayılan olarak YANLIŞ olarak ayarlanır. auto_shrink TRUE olarak ayarlanırsa, otomatik küçültme yalnızca alanının yüzde 25'inden fazlası kullanılmadığında dosyanın boyutunu küçültür.

    • Dosya, dosyanın yalnızca yüzde 25'inin kullanılmayan alan boyutuna veya dosyanın özgün boyutuna (hangisi daha büyükse) küçültülür.
    • auto_shrink özelliğinin ayarını değiştirme hakkında bilgi için bkz. Bir Veritabanının Özelliklerini Görüntüleme veya Değiştirme ve ALTER DATABASE SET Seçenekleri (Transact-SQL).

İlgili içerik