sys.dm_db_index_physical_stats (Transact-SQL)

işlev yürütüldüğü mod işlevtarafından kullanılan istatistiksel veriler elde etmek için gerçekleştirilen tarama düzey belirler.modeLIMITED, sampled veya ayrıntılı belirtilir.Belirtilen tablo veya dizin bölümleri kurmak ayırma birim için sayfa zincirlerini işlev erişir.sys.dm_db_index_physical_statssadece bir Intent-Shared (Is) tablo kilit, onu çalıştırır modu ne olursa olsun gerektirir.Kilitleme hakkında daha fazla bilgi için bkz: Kilit modları.

SINIRLI mod hızlı mod ve sayfaların en küçük sayıyı tarar.Bir dizin için yalnızca üst -düzey sayfalar B-ağacı (yani yukarıda yaprak düzeysayfaları) taranır.Bir yığın, yalnızca ilişkili pfs ve IAM sayfaları incelenir; Yığın veri sayfalarını tarama değil.De SQL Server 2005, tüm sayfaları bir yığın modu sınırlı taranan

SINIRLI modu compressed_page_count null olduğundan Veritabanı Altyapısı olmayan tek taramaları-yaprak sayfaları B-ağacı ve IAM ve pfs sayfaları Kaçağı.sampled modu için tahmini bir değer almak için kullanmak compressed_page_countve ayrıntılı modda gerçek değerini almak için compressed_page_count...sampled modu yüzde 1'örneği dizin veya yığın. tüm sayfaların esas istatistiklerini verirDizin veya yığın 000'den az sayfa varsa, Ayrıntılı modda sampled yerine kullanılır.

Ayrıntılı modu tüm sayfaları tarar ve tüm istatistikleri döndürür.

Daha fazla iş her modunda gerçekleştirildiğinden modları ayrıntılı için sınırlı giderek yavaştır.Boyut veya parçalanma düzey , bir tablo ya da dizin hızlı bir şekilde ölçmek için sınırlı modunu kullanın.Hızlı olduğu ve IN_ROW_DATA ayırma birimi dizinin her nonleaf düzey satır döndürmez.

Kullanabileceğiniz Transact-SQL işlevleri DB_ID ve OBJECT_ID için bir değer belirtmek için database_id ve object_id parametreleri.Ancak, bu işlevler için geçersiz değerler geçirerek istenmeyen sonuçlarneden olabilir.Örneğin, yok veya yanlış yazılmış çünkü veritabanı veya nesne adı bulunamazsa, her iki işlevi null döndürür.sys.dm_db_index_physical_statsişlev null tüm veritabanları veya tüm nesneleri belirleyen bir joker karakter değeri yorumlar.

Ayrıca, OBJECT_ID işlev önce işlenen sys.dm_db_index_physical_stats işlev çağrılırsa ve bu nedenle geçerli veritabanı bağlamında değerlendirilmesi veritabanı belirtilen database_id.Bu davranış, boş bir değer dönmek OBJECT_ID işlev neden olabilir; veya nesne adı geçerli veritabanı içeriği ve belirtilen veritabanı varsa, hata iletisi döndürülebilir.Aşağıdaki örnekler, bu istenmeyen sonuçlargöstermektedir.

Aşağıdaki örnekte, OBJECT_ID bağlamında değerlendirilmesi master veritabanı.Çünkü Person.Address yok master, işlev verir NULL.Zaman NULL olarak belirtilen bir object_id, veritabanındaki tüm nesneler döndürülür.Geçersiz nesne belirtildiğinde aynı sonuçlar döndürülür.

USE master;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO

Aşağıdaki örnek, her ikisi de geçerli veritabanı içeriği ve belirtilen veritabanında bulunan geçerli bir ad belirterek sonuçlar gösterir database_id parametresi sys.dm_db_index_physical_stats işlev.Kimlik değeri olarak döndürdüğü bir hata döndürülür OBJECT_ID nesnesinde ID değeri eşleşmiyorbelirtilen veritabanı.

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2008R2;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

En iyi

tablo ve dolayısıyla ' % s'tablo tabloüzerinde tanımlı dizinler için yaptığınız veri değişiklikleri (INSERT, update ve delete deyimlerini) sürecinde parçalanma oluşur.Bu değişiklikler genellikle eşit dizinler ve tablo satırları arasında dağıtılır değil çünkü her sayfa tamlık saatiçinde değişebilir.Kısmını veya tamamını bir tablodizinleri tara sorguları için bu tür bir parçalanma ek sayfa okuma neden olabilir.Bu paralel veri tarama yavaşlattığını.

Parçalanma hesaplama algoritması daha kesin olarak SQL Server 2008 biçimde SQL Server 2000.Sonuç olarak, parçalanma değerleri daha yüksek görünür.Örneğin, SQL Server 2000, tablo sayfa 11 ve sayfa 13'te aynı kapsam ancak değil sayfa 12 varsa parçalanmış sayılmaz.Parçalanma sayılan bu şekilde ancak bu iki sayfalarına erişmek için fiziksel iki g/Ç işlemlerini gerektiren SQL Server 2008.

Bir dizin veya yığın parçalanma düzey gösterilir avg_fragmentation_in_percent sütun.Yığınlar için yığın kapsam parçalanma değerini temsil eder.Dizinler için dizinin mantıksal parçalanma değerini temsil eder.dbcc SHOWCONTIG aksine iki durumda parçalanma hesaplama algoritmaları birden fazla yayılmıştır depolama düşünün ve bu nedenle, doğru.

Mantıksal parçalanma

Sıra dışı bir dizinin yaprak sayfalar sayfalarında yüzdesidir.Sıra dışı sayfa sayfa , dizine ayrılan sonraki fiziksel sayfa olmadığı için tarafından İleri pag ulaşılan sayfa olane işaretçisi geçerli yaprak sayfa.

Ölçüde parçalanması

Bir yığın yaprak sayfaları içinde sıra dışı kapsamlarını yüzdesidir.Sıra dışı kapsam , bir yığın için geçerli sayfa içerir kapsam fiziksel olarak İleri kapsam önceki sayfaiçeren kapsam sonra olmadığı biridir.

Değeri avg_fragmentation_in_percent olması gereken gibi sıfıra yakın olabildiğince maksimum performans.Ancak, yüzde 10 ile yüzde 0 değerleri kabul edilebilir olabilir.Tüm yeniden inşa etmek, yeniden düzenleme veya yeniden oluşturmayı, parçalamayı azaltma yöntemleri, bu değerleri azaltmak için kullanılabilir.Bir dizindeki parçalanma derecesini çözümlenmesi hakkında daha fazla bilgi için bkz: Yeniden düzenleme ve dizinler yeniden oluşturuluyor.

Dizin parçalanma sorgu performansını etkileyen bir şekilde parçalanır parçalanma azaltmak için üç seçenek vardır:

• Bırakın ve kümelenmiş dizinyeniden oluşturun.

  kümelenmiş dizin yeniden oluşturma, veri ve sonuçlar tam veri sayfalarında yeniden dağıtır.Tamlık düzey create INDEX FILLFACTOR seçeneği kullanılarak yapılandırılabilir.Bu yöntem dezavantajları şunlardır: dizin bırakma sırasında çevrimdışı ve döngü yeniden ve işlem atomikolduğunu.Dizin oluşturma yarıda kesilirse, dizini yeniden değil.Daha fazla bilgi için, bkz. CREATE INDEX (Transact-SQL).

• alter dizini yeniden DÜZENLEMEK, yerine dbcc INDEXDEFRAG yaprak düzey sayfalar dizinin mantıksal sırada yeniden düzenlemek için kullanın.Bu çevrimiçi bir işlem olduğundan, dizin deyim çalışırken kullanılabilir.İşlem zaten tamamlandı iş kaybetmeden de kesintiye uğraması.Bu yöntem olumsuz yanı, olarak iyi bir dizin yeniden oluşturma işlemi ve güncelleştirme istatistikleriyapar gibi verileri yeniden düzenleme, bir iş yapmaz, ' dir.

• alter dizin yeniden, dbcc DBREINDEX yerini, çevrimiçi veya çevrimdışı dizini yeniden oluşturmak için kullanın.Daha fazla bilgi için, bkz. alter INDEX (Transact-SQL).

Parçalanma tek başına yeniden düzenlemek veya bir dizini yeniden oluşturmak için yeterli bir neden değil.Ana parçalanması da yavaşlattığını ise kapalı sayfa ileriye oku üretilen dizin taramalar sırasında.Bu, yavaş yanıt süreleri neden olur.Parçalanmış tablo ya da dizin üzerinde sorgu iş yükünü iş yükünü öncelikle singleton aramaları olduğundan taramalar, katılmadığı, parçalanma kaldırma etkisi olabilir.Daha fazla bilgi için bakın Microsoft Web sitesi.

Bir yığın kapsam parçalanma azaltmak için bir kümelenmiş dizin tablo oluşturmak ve sonra Dizin bırakın.kümelenmiş dizin oluşturulurken bu verileri yeniden dağıtır.Bu da mümkün olduğu kadar en iyi dağıtım veritabanında kullanılabilen boş alanı kurmayı sağlar.kümelenmiş dizin yığın yeniden sonra kesildiği zaman veri değil taşınır ve en iyi konumda kalır.Bu işlemleri gerçekleştirme hakkında daha fazla bilgi için bkz: create INDEX ve drop INDEX.

Dikkat
Bu tablo tüm kümelenmemiş dizinler oluşturma ve bir tablo kümelenmiş dizin bırakarak, iki kez yeniden oluşturur.

Varsayılan olarak, alter dizin yeniden DÜZENLEMEK deyim büyük nesne (lob) veri içeren sayfaları sıkıştırır.lob sayfaları boş zaman ayırmanın değil, bu veri sıkıştırma disk alanı kullanımını lob verileri çok sayıda silinmiş ya da bir lob sütun bırakılan artırabilir.

Belirtilen kümelenmiş dizin yeniden düzenleme kümelenmiş diziniçerdiği tüm lob sütunları düzenler.kümelenmemiş dizin yeniden düzenleme, anahtarı olmayan (dahil) sütunlar dizin içindeki tüm lob sütunları düzenler.TÜM belirtilen ' % s'deyimi deyimiçinde belirtilen tablo veya görünümü ile ilişkili tüm dizinler yeniden düzenlenmesi.Ayrıca, kümelenmiş dizin, temel tabloveya dahil sütunlarla kümelenmemiş dizin ile ilişkili tüm lob sütunları dursa.

avg_page_space_used_in_percentsütun sayfa tamlık gösterir. En yüksek disk alanı kullanımını elde etmek için bu değer için çok sayıda rasgele ekler olmayacak bir dizin için yüzde 100'e yakın olmalıdır.Ancak, çok sayıda rasgele ekler ve çok dolu sayfalar vardır bir dizin sayfa ayırmaya sayısındaki artışla olacaktır.Bu, daha fazla parçalanma neden olur.Bu nedenle, sayfa ayırmaya azaltmak için değer yüzde 100'den az olmalıdır.FILLFACTOR taşıyan bir dizin yeniden oluşturma seçeneği belirtilen uyma dizin sorgu düzeni değiştirilecek sayfa tamlık sağlar.doldurma faktörühakkında daha fazla bilgi için bkz: Dolgu etmeni.Ayrıca, alter dizin yeniden düzenle dizin belirtilen son FILLFACTOR sayfaları doldurmak üzere deneyerek sıkıştırır.Bu değeri artırır avg_space_used_in_percent.alter dizin yeniden Düzenle sayfa tamlık azaltmak olamaz unutmayın.Bunun yerine, bir dizin yeniden yapılması gerekiyor.

Bir parça ayırma birimiiçin aynı dosyada fiziksel olarak ardışık yaprak sayfaları oluşur.Bir dizin en az bir parça vardır.Bir dizini olabilir en büyük parçacık eşit sayfa sayısı yaprak düzey dizin.Büyük parça, daha az disk g/Ç aynı sayfa sayısını okumak için gerekli anlamına gelir.Bu nedenle, büyük avg_fragment_size_in_pages değeri, daha iyi aralık tarama performansı.avg_fragment_size_in_pages Ve avg_fragmentation_in_percent değerleri birbirine inversely orantılı.Bu nedenle, yeniden oluşturulması veya bir dizin yeniden düzenleme parçalanma miktarını azaltmak ve parça boyutunu artırın.

Aşağıdaki örnek, tüm dizinler ve bölümlerini boyutu ve parçalanma istatistikleri döndürür Person.Address tablo AdventureWorks2008R2 veritabanı.Tarama modu küme için ise 'LIMITED' en iyi performans ve döndürülen istatistikleri sınırlamak için.Bu sorguyu yürüttükten gerektirir, en azından Denetim izni üzerinde Person.Address tablo.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

Aşağıdaki örnek öbek tüm istatistiklerini verir dbo.DatabaseLog , AdventureWorks2008R2 veritabanı.tablo lob veri içerdiğinden, bir satır için döndürülen LOB_DATAayırma birimi için döndürülen satır ek olarak IN_ROW_ALLOCATION_UNIT veri sayfalarını saklamaöbek.Bu sorguyu yürüttükten gerektirir, en azından Denetim izni üzerinde dbo.DatabaseLog tablo.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.DatabaseLog');
IF @object_id IS NULL 
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

Aşağıdaki örnek için tüm tablolar ve dizinler örnek içindeki tüm istatistikleri döndürür SQL Server joker belirterek NULL için tüm parametreleri.Bu sorguyu yürüttükten görünüm server state izni gerektirir.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

Aşağıdaki örnek, otomatik olarak yeniden düzenler veya yüzde 10 üzerinde bir ortalama parçalanma olan tüm bölümler bir veritabanında yeniden oluşturur.Bu sorguyu yürüttükten veritabanı durumunu görüntüleme izni gerektirir.Bu örnek belirtir DB_ID ilk parametre belirtmeden bir veritabanı adı.Geçerli veritabanı Uyumluluk düzey 80 veya daha düşük olması durumunda bir hata oluşturulur.Hatayı gidermek için yerini DB_ID() geçerli bir veritabanı adı.Veritabanı uyumluluk düzeyleri hakkında daha fazla bilgi için bkz: sp_dbcmptlevel (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Aşağıdaki örnek, görüntüleme ve toplam satır sayfalar ve sıkıştırılmış sayfa sayfa sayısını karşılaştırmak gösterilmiştir.Bu bilgiler, bir dizin veya tabloiçin sıkıştırma sağlayan bir yararı belirlemek için kullanılabilir.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count, ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY record_count DESC;