Yeniden düzenleme ve dizinleri yeniden oluşturma
The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data.Zamanla bu değişiklikleri bilgileri dizinde (parçalanmış) veritabanında dağılmış duruma neden olabilir.Parçalanma, sayfaları, mantıksal, anahtar değeri temelinde sıralama eşleşmiyor fiziksel dizin varsa, mevcut veri dosyası içinde sipariş.Çok parçalanmış dizin sorgu performansını düşürmesine ve uygulama yavaş yanıt vermesine neden.Daha fazla bilgi için bkz: BuMicrosoft Office 2010 Suite Web sitesi.
Dizin parçalanması onarmak ya da dizini yeniden düzenleme ya da dizini yeniden oluşturuluyor.Yerleşik olarak bir bölümleme düzeni bölümlenmiş dizinler için bu yöntemlerin tam bir dizin veya dizin için tek bir bölüm ya da kullanabilirsiniz.
Parçalanma algılama
Parçalanma düzeyini belirlemek için dizin çözümlemek için ilk adım kullanılacak birleştirme yöntem karar değil.Sistem işlev kullanaraksys.dm_db_index_physical_stats, tüm dizinler bir tablo ya da dizinli görünüm, veritabanındaki tüm dizinleri veya tüm veritabanları. tüm dizinlerde belirli bir dizinde parçalanma algılayabilir Bölümlenmiş dizinler içinsys.dm_db_index_physical_stats için her bölümün. parçalanma bilgi de sağlar...
Sonucu küme döndürülensys.dm_db_index_physical_stats işlev aşağıdaki sütunları içerir.
Sütun |
Açıklama |
|---|---|
avg_fragmentation_in_percent |
Mantıksal (sıra dışı sayfa dizin içinde) parçalanma yüzdesi. |
fragment_count |
Dizindeki parçaları (fiziksel olarak ardışık yaprak sayfaları) sayısı. |
avg_fragment_size_in_pages |
Bir dizinin bir parçasını sayfalarında ortalama sayısı. |
Parçalanma düzeyini bilinen sonra aşağıdakileri kullanarak tablo parçalanma düzeltmek için en iyi yöntem belirlemek için.
avg_fragmentation_in_percent value |
Düzeltme deyim |
|---|---|
>% 5 ve<= 30% |
DİZİN REORGANIZE DEĞİŞTİR |
> 30% |
DİZİNİ YENİDEN İLE DEĞİŞTİRMEK (ÇEVRİMİÇİ = AÇIK) * |
* Dizin yeniden çevrimiçi veya çevrimdışı çalıştırılabilir.Dizini yeniden düzenleme her zaman çevrimiçi yürütülür.Reorganize seçeneğine benzer kullanılabilirlik elde etmek için çevrimiçi dizinler yeniden oluşturmalısınız.
Bu değerler, ALTER dizini yeniden oluşturmak ve ALTER INDEX yeniden düzenleme arasında geçer noktasını belirlemek için kaba bir kılavuz sağlar.Ancak, gerçek değerleri büyük/küçük durum durum arasında değişebilir.Ortamınız için en iyi eşiği belirlemek için denemeler yapmak için önemlidir.
Yeniden düzenleme ya da dizinin yeniden oluşturulması ile maliyet kazancı parçalanma küçük miktar kaldırma arasında hemen her zaman vastly olarak outweighed için çok az düzeyde parçalanma (az yüzde 5) Bu komutlardan birini giderilmemiş.
Not
Genel olarak, küçük dizinler üzerinde parçalanma denetlenebilir değil genellikle.Küçük dizin sayfaları karışık uzantılarına göre saklanır.Bu nedenle, küçük bir dizin içinde parçalanma yeniden düzenleme ya da dizini yeniden oluşturuluyor sonra azalabilir. karışık alanları en çok sekiz nesneler tarafından paylaşılır.Karma alanları, daha fazla bilgi için bkz:Anlama sayfaları ve alanları.
Örnek
Aşağıdaki örnek sorgularsys.dm_db_index_physical_statsdinamik yönetimi işlev, tüm dizinler için ortalama parçalanma dönmek içinProduction.ProductTablo.Önceki kullanarak tablo, yeniden düzenlemek için önerilen çözümü olanPK_Product_ProductIDve yeniden diğer dizinleri.
USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
deyim Aşağıdakine benzer bir sonuç kümesi döndürebilir.
index_id name avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1 PK_Product_ProductID 15.076923076923077
2 AK_Product_ProductNumber 50.0
3 AK_Product_Name 66.666666666666657
4 AK_Product_rowguid 50.0
(4 row(s) affected)
Dizin yeniden düzenleme
Bir veya daha fazla dizini yeniden düzenlemek için REORGANIZE yan tümcesinde ALTER INDEX deyimini kullanın.Bu deyim, DBCC INDEXDEFRAG deyim değiştirir.Bölümlenmiş bir dizinin tek bir bölümü yeniden düzenlemek için ALTER INDEX PARTITION yan tümce kullanın.
Dizini yeniden düzenleme, kümelenmiş ve kümelenmemiş dizinleri tablolar ve görünümler üzerinde yaprak düzey fiziksel yaprak düzeyindeki sayfalar yaprak düğümlerin mantıksal sırası (soldan sağa) eşleştirmek için yeniden birleştirir.Sayfaları sıraya sahip dizin tarama performansı artırır.Dizin içinde kendisine ayrılan varolan sayfaların reorganized; yeni sayfa ayrılır.Bir dizin birden fazla dosya yayılmışsa, yeniden düzenlenen bir anda dosyalardır.Sayfaları dosya arasında geçiş.
Yeniden düzenleme, dizin sayfaları sıkıştırır.Bu düzenleme ile oluşturulan tüm boş sayfalar ek disk alanı sağlayan kaldırılır.Sıkıştırma doldurma faktörü değeri esassys.indexes Katalog görünümü.
Reorganize işlemi en az sistem kaynağı kullanır.Ayrıca, yeniden düzenleyerek otomatik olarak çevrimiçi gerçekleştirilir.İşlem durdurma uzun süreli kilit tutun; bu nedenle, çalışan sorguları veya güncelleştirmeleri engellemez.
Dizin, dizin çok parçalanmış düzenleyebilirsiniz.Parçalanma yönergeler için önceki tabloya bakın.Ancak dizin çok parçalanmış, dizinin yeniden oluşturulması ile daha iyi sonuçlar elde.
Büyük nesne veri türü düzenleme
Bir veya daha fazla dizin, kümelenmiş dizinde bulunan büyük nesne veri türleri (LOB'lar) yeniden düzenleme yanı sıra veya temel alınan tablo dizin reorganized, varsayılan olarak düzenlenmiş.Veri türleriimage,text,ntext,varchar(max),nvarchar(max),varbinary(max), and xmlolan büyük nesne veri türleri.Bu veri sıkıştırma daha iyi disk alanı kullanımını neden olabilir:
Belirli bir kümelenmiş dizini yeniden düzenleme yaprak düzeyinde kümelenmiş dizin (verilerin satır) bulunan tüm LOB sütunları düzenleyin.
Kümelenmemiş bir dizin yeniden düzenleme düzenleme tüm LOB sütunları olan olmayan (dahil) sütun dizini.
ALL belirtilirse, belirtilen tabloyu veya görünümü ile ilişkilendirilmiş tüm dizinler reorganized ve kümelenmiş dizin, tablo ve sütunlar ile kümelenmemiş dizin ile ilişkili tüm LOB sütunlarda düzenlenmiş.
LOB_COMPACTION yan LOB sütunları yok sayılır.
Dizin yeniden oluşturma
Dizini yeniden dizin keser ve yeni bir tane oluşturur.Böylece, parçalanma kaldırılır, disk alanı belirtilen veya varolan bir doldurma faktörü ayarı kullanarak sayfa düzenleme tarafından iadesi ve (gerekirse yeni sayfalar ayırma) bitişik sayfa dizini satırları reordered.Bu disk istenen verileri elde etmek için gereken sayfa okuma sayısını azaltarak performansı artırabilir.
Kümelenmiş ve kümelenmemiş dizinleri yeniden oluşturmak için aşağıdaki yöntemleri kullanılabilir:
ALTER INDEX yeniden yan tümce ile birlikte.Bu deyim, DBCC DBREINDEX deyim değiştirir.
CREATE INDEX DROP_EXISTING yan tümce ile birlikte.
Her yöntemin aynı işlev görür, ancak avantajları ve dezavantajları aşağıdaki tabloda gösterildiği gibi göz önünde bulundurulması gereken vardır.
İşlevsellik |
DİZİNİ YENİDEN DEĞİŞTİR |
DROP_EXISTING İLE DİZİN OLUŞTURMA |
|---|---|---|
Dizin tanımı eklemesini veya kaldırmasını anahtar sütunları, sütun sırasını değiştirme veya sütunun sıralama düzeni order.* değiştirme olarak değiştirilebilir |
Hayır |
Evet** |
Dizin seçeneklerini küme değiştirme veya. |
Evet |
Evet |
Tek bir işlemde birden fazla dizin yeniden. |
Evet |
Hayır |
Dizin türlerinin çoğu çevrimiçi çalışan sorguları veya güncelleştirmeleri engellenmeden yeniden oluşturulması. |
Evet |
Evet |
Bölümlenmiş dizin repartitioned. |
Hayır |
Evet |
Dizin, başka bir dosya grubu için taşınabilir. |
Hayır |
Evet |
Ek geçici disk alanı gereklidir. |
Evet |
Evet |
kümelenmiş dizin yeniden oluşturur ilişkili kümelenmemiş dizinleri. |
Hayır Sürece ALL belirtilen anahtar sözcüğü. |
Hayır Dizin tanımı değiştirmediği takdirde. |
birincil anahtar ve benzersiz kısıtlamaları zorlamayı dizinleri silmek ve yeniden kısıtlamaları yeniden. |
Evet |
Evet |
Tek bir dizin bölümü yeniden. |
Evet |
Hayır |
* Bir kümelenmemiş dizin kümelenmiş dizini tanımında belirterek bir kümelenmiş dizin türüne dönüştürülemez.Bu işlem çevrimiçi seçeneğiyle gerçekleştirilmelidir küme için kapalı.Dönüştürme için kümelenmemiş kümelenmiş bağımsız olarak çevrimiçi ayarı desteklenmez.
** Varsa, aynı ad, sütun ve sıralama düzeni düzenini kullanarak dizin yeniden sıralama düzeni işlemi atlanabilir.Yeniden oluşturma işlemi, dizin oluşturma sırasında satırlar sıralanır denetler.
Ayrıca, ilk bırakma dizini DROP INDEX deyim ve bunun ayrı bir CREATE INDEX deyim ile yeniden dizin yeniden oluşturabilirsiniz.Ayrı ifadeler olarak bu işlemleri gerçekleştirme çeşitli sakıncaları vardır ve bu önerilmez.
İşlem sırasında disk alanı tasarrufu için devre dışı bırakma kümelenmemiş dizinleri yeniden
Bir kümelenmemiş dizin devre dışı bırakıldığında, dizin verileri satırlar silinir, ancak dizin tanımı meta veriler kalır.Dizini, yeniden etkinleştirilir.Yeniden oluşturma işlemi, kümelenmemiş dizin devre dışıyken, hem eski ve yeni dizini depolamak için yeterli geçici disk alanı gerektirir.Ancak, devre dışı bırakma ve yeniden ayrı işlemlerde kümelenmemiş bir dizin tarafından devre dışı bırakma dizini olarak kullanılabilir disk alanını izleyen yeniden veya başka bir işlem tarafından yeniden kullanılabilir.Genelde yüzde 20'sini dizin boyutu olan sıralama, geçici disk alanı dışında hiçbir ek alan gerekiyor.Kümelenmemiş dizin birincil anahtar, etkin, başvuru yabancı anahtar kısıtlamaları tüm otomatik olarak devre dışı bırakılacak.Dizini yeniden sonra bu kısıtlamaları el ile etkinleştirilmesi gerekir.Daha fazla bilgi için bkz:Dizinleri devre dışı bırakmaveDizinler ve kısıtlamalar etkinleştirme yönergeleri.
Büyük dizinleri yeniden oluşturuluyor
Dizinleri 128'den fazla kapsam içeren iki ayrı aşamada yeniden: mantıksal ve fiziksel. Mantıksal aşaması, dizini tarafından kullanılan varolan ayırma birimleri miktarda kaldırma için işaretlenmiş, veri satırlarına kopyalanır ve sıralanmış ve daha sonra depo için oluşturulan yeni bir ayırma birimi yeniden dizin taşındı.Fiziksel aşamasında, daha önce miktarda kaldırma için işaretlenmiş ayırma birimi fiziksel kısa arka planda gerçekleşmesi ve pek çok kilit gerek hareketleri bırakılır.Daha fazla bilgi için bkz:Bırakma ve büyük nesneleri yeniden oluşturma.
Dizin seçeneklerini ayarlama
Dizini yeniden düzenleme, dizin seçeneklerini tarif edilemez.Ancak, ALTER dizini yeniden OLUŞTURMANIZ veya CREATE INDEX ile DROP_EXISTING dizini yeniden oluşturduğunuzda aşağıdaki dizin seçenekleri ayarlanabilir:
PAD_INDEX |
DROP_EXISTING (yalnızca CREATE INDEX) |
FILLFACTOR |
Çevrimiçi |
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
STATISTICS_NORECOMPUTE |
MAXDOP |
Not
Sıralama işlemi gerekmiyorsa veya sıralama, belleğe yapılabilir, SORT_IN_TEMPDB seçeneği göz ardı edilir.
Ayrıca küme yan tümce ALTER DİZİNDE deyim dizini yeniden olmadan aşağıdaki dizin seçeneklerini ayarlamanıza olanak sağlar:
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
Daha fazla bilgi için bkz:Dizin seçeneklerini ayarlama.
Yeniden oluşturmak için dizin yeniden düzenleme
Bir dizini silmek ve yeniden tek bir adımda bir dizin yeniden oluşturmak için
Örnekler
C.Dizini yeniden oluşturuluyor
Aşağıdaki örnek, tek bir dizin oluşturur.
B.Tüm dizinler üzerinde yeniden bir tablo ve seçeneklerini belirtme
Aşağıdaki örnek, anahtar belirtirALL.Bu tablo ile ilişkili tüm dizinleri yeniden oluşturur.Üç seçenek belirtildi.
C.Dizin ile LOB düzenleme yeniden düzenleme
Aşağıdaki örnek, tek kümelenmiş dizin reorganizes.Dizinde yaprak düzeyindeki LOB veri türü içerdiğinden, deyim de büyük nesne verisi içeren tüm sayfaları sıkıştırır.İLE belirtmeniz gerekmez, Not (LOB_Compaction) seçeneği, varsayılan değer açık olduğu için.
See Also