Sorgu işleme bölümlenmiş tablolar ve dizinler üzerinde geliştirmeler
SQL Server 2008 sorgu işleme bölümlenmiş tabloları çok sayıda paralel planları için performansı artırır, paralel ve seri planlar gösterilir ve her iki derleme - sağlanan bölümleme bilgilerini geliştirir şeklini değiştirirsaat ve run -saat yürütme planları.Bu konu bu geliştirmeleri açıklar nasıl yorumlanacağı sorgu yürütme planları bölümlenmiş tablolar ve dizinler üzerindeki Kılavuzu sağlar ve bölümlenmiş nesneler üzerinde sorgu performansını artırmak için en iyi yöntemleri sağlar.
Not
Bölümlenmiş tablolar ve dizinler desteklenen yalnızca SQL Server kuruluş, Developer ve değerlendirme sürümleri.
Yeni bölüm-Aware arama işlemi
In SQL Server 2008, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column.PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row.Örneğin, tablo varsayalım Tolarak tanımlanan T(a, b, c), bölümlenmiş sütun üzerinde a, ve sütun kümelenmiş dizin olan b.De SQL Server 2008, bu bölümlenmiş tablo şema ile nonpartitioned bir tablo olarak dahili olarak kabul T(PartitionID, a, b, c) ve kümelenmiş dizin bileşik anahtar (PartitionID, b).Böylece sorgu iyileştiricisi gerçekleştirmek için arama işlemlerini temel PartitionID tüm bölümlenmiş tablo veya dizin.
Bölüm eleme şimdi yapılır bu arama işlemi.
Böylece üzerinde bir arama veya tarama işlemi tek bir koşulu ile yapılabilir sorgu iyileştiricisi buna ek olarak, genişletilmiş PartitionID (sütun olarak mantıksal satır aralığı) ve büyük olasılıkla diğer dizin anahtar sütunları ve daha sonra farklı bir koşulu ile bir ikinci düzey seek, bir veya daha fazla ek sütunlar, birinci düzey arama için operasyon. nitelik karşılayan ayrı her değer için yapılabilirOlarak adlandırılan diğer bir deyişle, bu işlem, bir Tarama, Atla bir arama gerçekleştirmek veya erişilmesini bölümlerini belirlemek için bir koşula göre işlem ve bir ikinci düzey dizin arama işlemi bu bölümlerden farklı bir koşula uyan satırları döndürmek için işleç içinde tarama sorgu iyileştiricisi sağlar.Örneğin, aşağıdaki sorgu göz önünde bulundurun.
SELECT * FROM T WHERE a < 10 and b = 2;
Bu örnek için bu tablo varsayalım Tolarak tanımlanan T(a, b, c), bölümlenmiş sütun üzerinde a, ve sütun kümelenmiş dizin olan b.Tablo bölüm sınırlarını T aşağıdaki tarafından tanımlanan bölümleme işlev:
CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);
Sorgu işlemci sorguyu çözmek için birinci düzey gerçekleştirir işlem koşulu karşılayan satırları içeren her bir bölümü bulmak için arama T.a < 10.Bu bölümleri erişilecek şekilde tanımlar.Her bölüm içinde tanımlanan işlemci sonra ikinci düzey gerçekleştirdiği sütun kümelenmiş dizin içinde arama b koşulu karşılayan satırları bulmak için T.b = 2 ve T.a < 10.
Aşağıdaki resimde bir tarama işlemi Atla mantıksal gösterimidir.Tablo gösterir T sütunlardaki veriler ile a ve b.Bölüm 1-4 arası kesik çizgili dikey çizgiyle gösterilen bölüm sınırları ile numaralandırılır.Bölüm 1, 2 ve 3 için tablo ve sütun yüklemi tanımlanan bölümleme tarafından örtülü arama koşulu karşılayan bir birinci düzey arama işlemi (resimde gösterilmemiştir) bölümler için belirlenen a.That is, T.a < 10.Tarama işlemi Atla ikinci düzey arama bölümünü geçiş yol eğri çizgi ile gösterilmiştir.Aslında, bu koşula uyan satırları için bölümler halinde tarama işlemi Atla yararlanılabileceğini b = 2.Gibi üç ayrı dizin yararlanılabileceğini tarama işlemi Atla toplam maliyeti aynıdır.
.gif "Atlamalı tarama işlemini gösterir.")
Planları sorgu yürütme planında bölümleme bilgilerini görüntüleme
Bölümlenmiş tablolar ve dizinler üzerinde sorgu yürütme planları kullanılarak incelenebilir Transact-SQL deyimleri set showplan_xml veya istatistik xml set, set veya grafik yürütme planı çıktı olarak kullanarak SQL Server Management Studio.Örneğin, derleme - görüntüleyebileceğinizsaat tıklatarak yürütme planı Tahmini yürütme planını görüntülemek sorgu Düzenleyicisi araç çubuğu ve çalışma -saat tıklayarak planı Fiili yürütme planı dahil.
Bu araçları kullanarak, aşağıdaki bilgileri koymak:
Taramalar gibi işlemleri yararlanılabileceğini, ekler, güncelleştirmeler, birleştirme ve access tabloları veya dizinler bölümlenmiş siler.
Sorgu tarafından erişilen bölümler.Örneğin, bölümler erişilen toplam sayısı ve aralıkları erişilen bitişik bölümlerinin çalışma - kullanılabilirsaat yürütme planları.
Ne zaman tarama işlemi Atla bir arama veya tarama işlemi, bir veya daha fazla bölümlerinden veri almak için kullanılır.
Yürütme planları görüntüleme hakkında daha fazla bilgi için bkz: Yürütme planı nasıl yapılır konuları.
Bölüm bilgilerini geliştirmeleri
SQL Server 2008 Gelişmiş bölümleme için her iki derleme - sağlarsaat ve run -saat yürütme planları.Yürütme planları şimdi aşağıdaki bilgileri sağlayın:
İsteğe bağlı bir Partitioned gibi arama, tarama, INSERT, update, birleştirme veya silme, bir işleç bölümlenmiş tablo üzerinde gerçekleştirildiğini belirten öznitelik.
Yeni bir SeekPredicateNew öğe ile bir SeekKeys içeren alt öğesi PartitionID gibi önde gelen dizin anahtar sütun ve filtre koşullarını aralık belirtin yararlanılabileceğini üzerinde PartitionID.İki varlığını SeekKeys alt öğelerini gösterir bir Atla tarama işlemi PartitionID kullanılır.
Erişilen bölümleri toplam sayısı sağlar özet bilgileri.Bu bilgiler yalnızca çalışma - kullanılabilirsaat planları.
Grafik yürütme planı çıktı ve xml Showplan çıktı bu bilgileri nasıl görüntüleneceğini göstermek için bölümlenmiş tablo üzerinde aşağıdaki sorgu göz önünde fact_sales.Bu sorguyu iki bölüm güncelleştirir.
UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;
Özellikleri aşağıda gösterilmiştir Kümelenmiş dizin arama işleç derleme -saat bu sorgu için yürütme planı.Tanımını görüntülemek için fact_sales Tablo ve bölüm tanımı "Example" Bu konuda.
.gif "Showplan çıktısında bölüm bilgisi.")
Bölümlenmiş öznitelik
Bir işleç gibi bir Dizin arama bir bölümlenmiş tablo veya dizin, yürütülen Partitioned öznitelik görünür derleme -saat ve çalışma -saat planlamak ve ayarlamak True (1).Olduğunda niteliği görüntülemek küme için False (0).
The Partitioned attribute can appear in the following physical and logical operators:
Tablo tarama
Dizin tarama
Dizin Ara
Ekle
Güncelleştirme
Sil
Birleştirme
Önceki şekilde gösterildiği gibi bu öznitelik tanımlanmış işleç özelliklerinde görüntülenir.Olarak, bu öznitelik xml Showplan çıktısında görünür Partitioned = "1" , RelOp düğümü işleç , tanımlandığı içinde.
Yeni arama koşulunu
xml Showplan çıktısında, SeekPredicateNew öğesi hangi BT içinde tanımlanmış işleç de görünür.En çok iki örneği içerebilir SeekKeys eklendiyse.İlk SeekKeys öğe, birinci düzey arama işlemi mantıksal dizin bölüm kimliği düzeyinde belirtir.Diğer bir deyişle, bu arama sorgu koşullarını için erişilmesi bölümlerini belirler.İkinci SeekKeys öğe birinci düzey seek tanımlanan her bölüm içinde oluşan tarama işlemi Atla ikinci düzey arama bölümünü belirtir.
Bölüm özeti bilgileri
Run-saat yürütme planları bölüm özet bilgileri erişilen bölümleri sayısı ve erişilen bölümleri gerçek kimliğini sağlar.Sorguda doğru bölümler erişilen ve tüm diğer bölümler dikkate elendiğinde doğrulamak için bu bilgileri kullanabilirsiniz.
Aşağıdaki bilgiler sağlanmıştır: Gerçek bölüm sayısı, ve bölümler erişilen.
Gerçek bölüm sayısı sorgu tarafından erişilen bölümleri toplam sayısıdır.
Accessed bölümleri, yeni görünen bölümü özet bilgileri xml Showplan çıktısında ise RuntimePartitionSummary öğesinde RelOp düğümü işleç , tanımlandığı içinde.Aşağıdaki örnek, içeriğini gösterir RuntimePartitionSummary öğesi, toplam iki bölüm olduğunu gösteren (Bölüm 2 ve 3).
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="2" >
<PartitionRange Start="2" End="3" />
</PartitionsAccessed>
</RunTimePartitionSummary>
Showplan diğer yöntemleri kullanarak bölümü bilgilerini görüntüleme
Showplan yöntemleri showplan_all, showplan_text ve İSTATİSTİKLERİ profili aşağıdaki özel durum ile bu konudaki anlatıldığı bölüm bilgileri rapor.Bir parçası olarak Ara doðrulama, erişilecek bölümleri bölüm kimliğini temsil eden hesaplanan sütun üzerinde bir aralık yüklemi tanımlanırAşağıdaki örnekte gösterildiği Ara için doðrulama bir Kümelenmiş dizin arama işleç.Bölüm 2 ve 3 erişilir ve seek işleç Filtre koşulu karşılayan satırları üzerinde date_id BETWEEN 20080802 AND 20080902.
|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),
SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)
AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)
AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))
ORDERED FORWARD)
Bölümlenmiş kümeler için yürütme yorumlama planları
De SQL Server 2008, yığın bölümlenmiş mantıksal bir dizin bölümünün kimliği olarak işlem görürBölümlenmiş bir yığın üzerinde bölüm eleme temsil yürütme planı içinde bir Tablo tarama işleç bir bölüm kimliği üzerinde arama yüklemi ileAşağıdaki örnek, sağlanan Showplan bilgileri gösterir:
|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)
Collocated birleştirme için yürütme yorumlama planları
İki tablo bölümlenmiş aynı veya eşdeğer işlev ve bölümleme sütunları birleştirmek işleminin her iki tarafından bölümleme öğesinde belirtilen birleştirmek birlikte bulunma meydana gelebilir birleştirmek koşulu sorgu.Sorgu iyileştirici bir plan oluşturabilirsiniz nerede bölümler her tablo kimlikleri ayrı ayrı katılan eşit bölüme sahip.Collocated birleştirme daha az bellek ve işlemci bunların gerektirdiği için birleşimler collocated çok daha hızlı olabilir saat.En iyi duruma getiricisi collocated planı veya temel maliyet tahminlerini collocated bir plan seçer.
Collocated plandaki İç içe döngüler birleştirmek iç taraftan bir veya birden çok Birleştirilmiş tablo ya da dizin bölümleri okur.İçindeki sayılar Sabit tarama işleçleri bölüm numaralarını temsil eder.
Bölümlenmiş tablo veya dizin, paralel collocated birleştirmek planları yaratılırken bir Parallelism işleç belirinceye arasında Sabit tarama ve İç içe döngüler birleştirmek operators.Bu durum, dış birleştirmek tarafında birden çok iş parçacığı her okumak ve çalışmak başka bir bölüm.
Bir paralel sorgu planı collocated birleştirmek için aşağıdaki resimde gösterilmektedir.
.gif "Aynı Yerde Bulunan Birleşim Yürütme Planı")
Bölümlenmiş nesneler için paralel sorgu yürütme stratejisi
Sorgu işlemci paralel yürütme stratejisi bölümlenmiş nesneleri içinden seçim sorguları için kullanır.Yürütme stratejisinin bir parçası, her bir bölüm tahsis etmek için sorgu ve iş parçacıkları bir oranı için gerekli tablo bölümleri sorgu işlemci belirler.Çoğu durumda, sorgu işlemci her bölüm için iş parçacıkları neredeyse eşit veya eşit sayıda ayırır ve sonra sorguyu bölümleri arasında paralel olarak yürütür.Aşağıdaki paragraflarda, iş parçacığı ayırma daha ayrıntılı açıklayın.
İş parçacığı sayısını bölüm sayısı azsa, başlangıçta bir veya daha fazla bölüm olmadan atanmış bir iş parçacığı bırakarak, farklı bir bölüm her iş parçacığı sorgu işlemci atar.Bir bölüme yürütme iş parçacığı sona erdiğinde, sorgu işlemci kadar her bölüm için atanan tek bir iş parçacığı, bir sonraki bölüme atar.Bu yalnızca, durum , sorgu işlemci diğer bölümler için iş parçacıkları reallocates içinde.
.gif "Tamamlandıktan sonra yeniden atanan iş parçacığını gösterir")
iş parçacığı sayısı için bölüm sayısına eşitse, sorgu işlemci her bölüm için iş parçacığı parçacığının atar.Yeniden bir iş parçacığı sona erdiğinde, onu başka bir bölüme ayrılan değil.
.gif "Her bölüme ayrılan bir iş parçacığını gösterir")
İş parçacığı sayısını bölümleri sayısından büyükse, her bölüm için iş parçacığı eşit sayıda sorgu işlemci ayırır.İş parçacığı sayısını bölüm sayısı tam katı değilse, sorgu işlemci tüm kullanılabilir iş parçacığı sayısı kullanmak için bazı bölümleri için ek bir iş parçacığı ayırır.Yalnızca bir bölümü ise, tüm iş parçacıklarını bu bölüme atanmış olduğunu unutmayın.Aşağıdaki çizimde, dört bölüm ve 14 iş parçacığı vardır.Her bölüm atanan 3 iş parçacığı vardır ve iki bölüm toplam 14 iş parçacığı atamaları için ek bir iş parçacığı vardır.Bir iş parçacığı sona erdiğinde, başka bir bölüme yeniden atanır.
.gif "Bölümlere ayrılan birden fazla iş parçacığını gösterir")
Yukarıdaki örnekler önermek iş parçacığı ayırma için basit bir yol olsa da, gerçek stratejisi daha karmaşıktır ve sorgu yürütme sırasında ortaya çıkan diğer değişkenler için hesaplar.Örneğin, bir ve bir sorgu koşul yan tümce tümce tümce vardır tablo bölümlenmiş ve sütun kümelenmiş dizin varsa WHERE A IN (13, 17, 25), bir sorgu işlemci tahsis veya bu üç için daha fazla iş parçacığı değerleri arama (A=13, A=17, ve A=25) , yerine her tablo bölüm.Yalnızca bu değerleri içeren bölümleri sorguyu yürütmek gerekli ve bunların hepsi arama yüklemler aynı tablo bölümünde raflarından, tablo aynı bölüm için tüm iş parçacıklarının atanacaktır.
Başka bir örnek olması için varsayalım tabloyu dört olan sınır noktaları (10, 20, 30), a sütunundaki sütun b üzerinde bir dizin bölümleri ve bir koşul yan tümce tümce tümce sorgusu var WHERE B IN (50, 100, 150). a üzerindeki değerleri Tablo bölümleri tabanlı olduğundan, b değerleri Tablo bölümleri birinde oluşabilirBu nedenle, sorgu işlemci her üç değeri (50, 100, 150) b her dört Tablo bölümleri arama.Bunu böylece sorgu işlemcisi iş parçacığı orantılı olarak atayacağı yürütmek her biri bu 12 sorgu taramalar paralel.
Tablo bölümleri esas sütun a |
İçin yararlanılabileceğini sütun her tablo bölüm b'de |
|---|---|
Tablo bölüm 1: A < 10 |
B = 50, B = 100, B = 150 |
Tablo bölüm 2: A >10 ve a = < 20 |
B = 50, B = 100, B = 150 |
Tablo bölüm 3: A >20 ve a = < 30 |
B = 50, B = 100, B = 150 |
Tablo bölüm 4: A >= 30 |
B = 50, B = 100, B = 150 |
En iyi yöntemler
Büyük bölümlenmiş tablolar ve dizinler büyük miktarda veri erişim sorguları performansını artırmak için aşağıdaki en iyi uygulamalar önerilir:
Her bölüm çok disk üzerinde şeritler halinde yazar.
Mümkün olduğunda, bir sunucu için yeterli ana bellek ile kullanmak uyma sık erişilen veya g/Ç maliyetini azaltmak için bellekte bulunan tüm bölümleri.
Sorgu verilerini yüklemez, uyma bellekte, tablolar ve dizinler sıkıştırın.Bu g/Ç maliyetini azaltır.
Hızlı işlemciler ve olabildiğince fazla sunucu kullanmak işlemci cores, paralel sorgu işleme yeteneği yararlanmak için gücünüze gibi.
Sunucu I/o denetleyicisi bant genişliği yeterli olduğundan emin olun.
B-ağacı tarama en iyi duruma getirmeleri yararlanmak için her büyük bölümlenmiş tablo üzerinde kümelenmiş dizin oluşturun.
Teknik incelemesindeki en iyi yöntemi önerilerini izleyin "Partitioned tabloya toplu verileri yükleme," ne zaman yükleme veri bölümlenmiş tabloya toplu.
Örnek
Aşağıdaki örnek, yedi bölüm ile tek bir tablo içeren bir sınama veritabanı oluşturur.Her iki derleme - bölümleme bilgilerini görüntülemek için bu örnekte sorguları yürütürken daha önce açıklanan araçları kullanmaksaat ve run -saat planları.
Not
Bu örnek 1 milyondan fazla satır ekler tablo.Bu örnek çalışan donanımınıza bağlı olarak birkaç dakika sürebilir.Bu örnek çalıştırmadan önce 1,5 GB'den fazla boş disk alanı olup olmadığını denetleyin.
USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact]
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int,
quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO