SQL Server Included Column Index

Included ındex SQL Server 2005 ile hayatımıza girmiştir. Included Column Index ‘in amacı sorgularımızı cover edip LookUp yapmamaktır.

Sorguyu Cover etmek , LookUp yapma ihtiyacı olmadan istenen tüm bilgileri LeafLevel Page ‘lerinde bulundurmaktır.

Included Column Index ‘ler NonClustered ındexlerdir.

Örneğimize geçecek olursak  ;

-- Test Database 'imizi oluşturuyoruz.
IF ( DB_ID('SQLTR') IS NULL )
    CREATE DATABASE SQLTR;
GO

USE SQLTR;
GO

--Test tablomuzu oluşturuyoruz.
IF OBJECT_ID('SQLTRPersonel') IS NOT NULL
    DROP TABLE SQLTRPersonel;
GO

CREATE TABLE SQLTRPersonel
    (
      ID INT IDENTITY ,
      PersonelNo CHAR(10) ,
      Name CHAR(400) ,
      SurName CHAR(400) ,
      Adress CHAR(400) ,
      Descriptions CHAR(400)
    );

 

Yukarıda ki scriptlerimiz ile test veritabanımızı ve test tablomuzu oluşturuyoruz daha sonra aşağıda ki script ile oluşturduğumuz tablomuzun içerisine veri basıyoruz.

-- Oluşturduğumuz Test Tablomuza Veri basıyoruz.
DECLARE @i INT= 1;
DECLARE @Name VARCHAR(50)= 'Yusuf';
DECLARE @SurName VARCHAR(75)= 'KAHVECİ';
WHILE @i <= 100000
    BEGIN
        INSERT  SQLTRPersonel
                SELECT  CAST(@i AS CHAR(10)) ,
                        @Name + '_' + CAST(@i AS VARCHAR(10)) ,
                        @SurName + '-' + CAST(@i AS VARCHAR(10)) ,
                        'İstanbul/Türkiye 3400 Pk.' ,
                        'www.sqlturkiye.com / [email protected]';
        SET @i = @i + 1;
    END;
GO

 

Bu işlemlerden sonra tablomuzun ID alanine Clustered Index tanımlıyoruz.

-- Tablomuza Clustered Index tanımlıyoruz.
CREATE CLUSTERED INDEX SCIX_SQLTRPersonel_Id ON SQLTRPersonel (id);
GO

 

Index ‘imizi tanımladıktan sonra belirli personel aralığında bulunan personellerin isim ve soy isimlerini çeken query ‘mi EXECUTE ediyorum ve EXECUTION Planını inceliyoruz.

---- Tablomuz üzerinden bir Query çalıştırıyoruz ve Execution Planını inceliyoruz.
--CI Index
--Logical Reads : 27432
SET STATISTICS IO ON;
SELECT  Name ,
        SurName
FROM    SQLTRPersonel
WHERE   PersonelNo > '540'
        AND PersonelNo < '670';
SET STATISTICS IO OFF;

 

1

 

Sorgumuzun Execution Planına baktığımızda Clustered Index ‘e Scan yapıldığını görmekteyiz. Peki neden Clustered Index Scan yapılmakta ? Çünkü tabloda sadece ID kolonu üzerinde Clustered Index olduğundan ve bizim query ‘miz de WHERE koşulumuzda PersonelNo kolonu olduğundan datanın tamamını okumakta.

Daha sonra PersonelNo kolonu üzerine Non-Clustered Index tanımlıyoruz ve aynı sorgumuzu tekrar çalıştırıp EXECUTION planımızı inceliyoruz.

-- PersonelNo kolonuna NonClustered Index tanımlıyoruz.
IF EXISTS ( SELECT  *
            FROM    sys.indexes
            WHERE   object_id = OBJECT_ID('SQLTRPersonel')
                    AND name = 'SNCIX_SQLTRPersonel_PersonelNo' )
    DROP INDEX SNCIX_SQLTRPersonel_PersonelNo ON SQLTRPersonel;
CREATE NONCLUSTERED INDEX SNCIX_SQLTRPersonel_PersonelNo ON SQLTRPersonel 
(
PersonelNo
);

-- NC Index PersonelNo
-- Logical Reads = 44279
SET STATISTICS IO ON;
SELECT  Name ,
        SurName
FROM    SQLTRPersonel WITH ( INDEX = SNCIX_SQLTRPersonel_PersonelNo )
WHERE   PersonelNo > '540'
        AND PersonelNo < '670';
SET STATISTICS IO OFF;

 

2

 

Gördüğünüz üzere Execution planımız değişti. PersonelNo kolonu üzerinden Index Seek işlemi gerçekleştiğini görmekteyiz. Ancak o index ‘in Leaf Level ‘ında PersonelNo  bilgisi bir de LookUp yapmak için ID bilgisi var. Ama benim ihtiyacım olan ise , isim ve soyisim verisini görmek istediğimden Name ve SurName alanlarını Select etmek.

Bu durumda SQL Server şu şekilde bir işlem gerçekleştiriyor; Bu bilgileri çekmen için LookUp işlemi yapman lazım . Query ‘mize bakacak olursak Index Seek işleminde PersonelNo da belirlediğimiz aralıktakileri buldu fakat Name ve SurName verilerini getirmek için Clustered Index ‘e LookUp yaptı. LookUp ‘ın maliyeti bu işlemde {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}100 . Bu çok büyük bir değer. Sorgumuzun executing işleminin neredeyse tamamı Lookup işlemini kapsamakta.

 

Bu durumu düzeltmek için aklımıza gelen ilk seçenek ne olmalıdır ?

Included Column ‘ı bilmediğimizi var sayalım ve bu durumda maliyeti azaltmak için ilk başvuracağımız nokta Composite Index ‘tir. Yani bu durumda ben Name ve SurName kolonlarını oluşturduğumuz Non-Clustered Index ‘imizin içerisine dahil edecek olursak LookUp işleminden kurtulmuş olacağız.

--Covering Index 'imizi tanımlıyoruz ...
IF EXISTS ( SELECT  *
            FROM    sys.indexes
            WHERE   object_id = OBJECT_ID('SQLTRPersonel')
                    AND name = 'SNCIX_SQLTRPersonel_PersonelNo_Composite' )
    DROP INDEX SNCIX_SQLTRPersonel_PersonelNo ON SQLTRPersonel;
CREATE NONCLUSTERED INDEX SNCIX_SQLTRPersonel_PersonelNo_Composite ON SQLTRPersonel 
(
PersonelNo,
name,
SurName
);

--Covering Index
--Logical Reads : 1798
SET STATISTICS IO ON;
SELECT  Name ,
        SurName
FROM    SQLTRPersonel
WHERE   PersonelNo > '540'
        AND PersonelNo < '670';
SET STATISTICS IO OFF;

 

3
(14443 row(s) affected)
Table ‘SQLTRPersonel’. Scan count 1, logical reads 1798, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Yukarıda ki scriptleri EXECUTE ederek Composite Index’imizi oluşturmuş oluyoruz. Ortaya çıkan Execution Plan ‘a baktığımızda ise Index Seek işlemi gerçekleşmektedir.

Bu durumda query ‘imde ki tüm kolonların üzerine index ‘leme işlemi gerçekleştirmiş olduk.

PersonelNo,Name ve SurName kolonları bütün Level ‘lar içerisinde ki Page ‘lerde yer alıyor. Oysa ki biz Name ve SurName kolonları üzerinden arama işlemi gerçekleştirmiyoruz. Yani Leaf ‘in üzerinde ki Page ‘lerde bunların olmasının bir espirisi yok.

Included Column ‘ın espirisi de tam bu noktada ortaya çıkıyor. Included olarak tanımladığımız kolonları Leaf Level ‘a yerlştiriyor ve arada ki Page ‘lerde bulundurmuyor.Bu sebepten dolayı arada ki Page ‘ler daha fazla kayıt alabiliyor ve daha fazla kayıt alması demek daha az Page ‘den oluşmasını sağlıyor.

--Included Column Index
IF EXISTS ( SELECT  *
            FROM    sys.indexes
            WHERE   object_id = OBJECT_ID('SQLTRPersonel')
                    AND name = 'SNCIX_SQLTRPersonel_PersonelNo_INCLUDE' )
    DROP INDEX SNCIX_SQLTRPersonel_PersonelNo ON SQLTRPersonel;
CREATE NONCLUSTERED INDEX SNCIX_SQLTRPersonel_PersonelNo_INCLUDE ON SQLTRPersonel 
(
PersonelNo
)
INCLUDE
(
name,
SurName
);

--Included Columns Index
--Logical Reads : 1622
SET STATISTICS IO ON;
SELECT  Name ,
        SurName
FROM    SQLTRPersonel WITH ( INDEX = SNCIX_SQLTRPersonel_PersonelNo_INCLUDE )
WHERE   PersonelNo > '540'
        AND PersonelNo < '670';
SET STATISTICS IO OFF;

 

4
(14443 row(s) affected)
Table ‘SQLTRPersonel’. Scan count 1, logical reads 1622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Yukarıda ki script’leri EXECUTE ederek Included Column Index ‘imizi oluşturuyoruz ve akabinde query ‘mizi çalıştırıp Execution Planını incelediğimizde Covering Index ‘in yaptığı gibi bu da {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}100 Index Seek işlemi gerçekleştiriyor fakat IO değerlerini incelediğimizde Composite Index 1798 okuma yaparken INCLUDED COLUMN Index 1622 okuma gerçekleştirmektedir.Arada 176 IO fark var. Bu drurum daha da dramatik hale gelebilir. Name ve SurName kolonları geniş olduğunda vb. türden işlemler gerçekleştiğinde bu fark çok daha yüksek değerlere çıkmakta ki genelde büyük çaplı OLTP sistemlerde daha net görebilirsiniz.

 

Compositeve Included Column Index ‘lerimizin boyutlarını karşılaştıracak olursak ;

-- Index 'imizin Boyutuna bakalım

SELECT  b.name ,
        b.index_id ,
        SUM(a.reserved_page_count) AS PageCount ,
        SUM(a.reserved_page_count) * 8 / 1024. AS Size_MB
FROM    sys.dm_db_partition_stats a
        INNER JOIN sys.indexes b ON a.object_id = b.object_id
                                    AND a.index_id = b.index_id
WHERE   OBJECT_ID('SQLTRPersonel') = a.object_id
        AND b.name IN ( 'SNCIX_SQLTRPersonel_PersonelNo_Composite',
                        'SNCIX_SQLTRPersonel_PersonelNo_INCLUDE' )
GROUP BY b.name ,
        b.index_id;

 

5

Son olarak ise ;

INCLUDED COLUMN INDEX ‘in bize sağladığı başlıca kazançlar nelerdir onları bir gözden geçirelim ;

  • Veriye daha hızlı ulaşmamızı sağlıyor. Yani daha az IO yaparak ( daha az page okuyarak ) .
  •  Sorgularımızı daha az maliyetle Cover ediyor.
  • Storage ‘ımızda Indexlerimizin kapladığı gereksiz alanların boyutunu azaltıyor ki bir DBA gözüyle bakacak olursak bu hem şirketin Storage maliyetini ( ekonomik açıdan ) rahatlatırken hemde DBA için örneğin Backup sürelerine dahi etki edebiliyor.

Bunun gibi daha bir çok ince fakat çok önemli kazançları bulunmaktadır Included Column Index ‘in..

 

 

 

INCLUDED COLUMN INDEX SLAYT

INCLUDED_COLUMNS_SQLTURKIYE 

Tıkla İNDİR !!!

INCLUDED COLUMN INDEX SQL TV

 Tıkla İZLE    !!!

 

İyi Çalışmalar.

Leave a Reply

Your email address will not be published. Required fields are marked *