articles

Articles of SQLschool.gr Team

Understanding Index External Fragmentation - Part I (The cases)

Antonios Chatzipavlis

Wednesday 19 March 2014

FragmentationExternal FragmentationNonclustered IndexesConfigurationDB DeveloperDBAIndexesTroubleshooting

Είμαι σίγουρος ότι έχετε ακούσει ουκ ολίγες φορές ότι μια από τις αιτίες που μπορεί να μειώνει την απόδοση των queries μας σε μια database είναι το index fragmentation (εφόσον βέβαια έχουμε σωστούς indexes και σωστά στατιστικά για αυτούς και φυσικά έχουμε γράψει queries που έχουν where clause) και ότι τo χειρότερο είναι το external fragmentation.

Είμαι επίσης σίγουρος ότι έχετε βρει και έχετε διαβάσει αρκετά στο internet σχετικά με το θέμα και ότι όλοι (για αυτό δεν βάζω και το χέρι μου στο ευαγγέλιο) κάνετε σε τακτά διαστήματα index defragmentation είτε reorganize είτε rebuild.

Πριν από αρκετό καιρό είχα γράψει ένα post με τα οποίο μπορείτε να δείτε το index fragmentation που έχετε και μπορείτε να τα βρείτε στο link http://www.sqlschool.gr/blog/external-and-internal-index-fragmentation-930.aspx.

Αλήθεια πόσοι από εσάς έχετε ΜΗΔΕΝ σαν external fragmentation έστω σε έναν index;
(θα ήθελα πραγματικά να μάθω για αυτό αφήστε ένα σχόλιο στο συγκεκριμένο post στο SQLschool.gr)

Επειδή ότι οι περισσότεροι δεν θα έχετε ΜΗΔΕΝ, τουλάχιστον σε όσους SQL Servers στην Ελλάδα και στο εξωτερικό έχω κληθεί να δω το ποσοστό ήταν πολύ μικρό κάτω από το 2% σε αυτό το post θέλω να σας δείξω το πώς και πότε θα έχεις ΜΗΔΕΝ.

Ο SQL Server είναι ένα προϊόν το οποίο έχει πολλά μυστικά, ιδιορρυθμίες και κυρίως πολύ περιπτωσιολογία. Δεν είναι τυχαίο ότι συχνά δίνουμε σαν απάντηση το γνωστό εξαρτάται, που δεν αρέσει σε πολλούς και που αρκετοί νομίζουν ότι απαντάμε έτσι γιατί είτε δεν γνωρίζουμε είτε δεν θέλουμε να απαντήσουμε.

Για αυτό το λόγο θα πάρουμε τέσσερεις διαφορετικές περιπτώσεις. Να σημειώσω ότι οι παρακάτω περιπτώσεις έχουν γίνει πάνω σε ένα test server που είναι ιδιαίτερα προσεγμένος στην εγκατάσταση του από την πρώτη στιγμή με αυτό εννοώ από το OS, storage μέχρι τον SQL Server.

Περίπτωση 1η

Σε αυτή μπορούμε να κατατάξουμε αυτούς που έχουν απλά με next click - finish έχουν εγκαταστήσει τον SQL Server και με το γραφικό περιβάλλον απλά έφτιαξαν μια βάση με την λογική τι τα θέλω όλα τα άλλα η βάση φτιάχτηκε. Σε αυτή την περίπτωση επίσης μπορώ να κατατάξω και αυτούς που ενώ έχουν στήσει καλά τον SQL Server όταν φτιάχνουν μια βάση βάζουν κάτι filegrowth μικρά που συγχίζομαι και μόνο που το γράφω.

Αυτός λοιπόν φτιάχνει μια βάση με τα defaults (step 1) και σε αυτή φτιάχνει ένα πίνακα (step 2) και γεμίζει αυτόν με 10.000 γραμμές (step 3). Κάνουμε και μερικές διορθώσεις στα δεδομένα ώστε να έχουν σωστές τιμές τα πεδία και βλέπουμε τα δεδομένα (step 4).

Φτιάχνουμε ένα index (nonclustered) (step 5) και πάμε να δούμε το external fragmentation αυτού (step 6). Νέος index θα περίμενε κανείς να μην έχει fragmentation, αλλά έχει !!!. Στο μηχάνημα μου βγάζει 20,8333333333333% !!!!.

Τι έγινε ρε παιδιά;

Πάμε να τον κάνουμε rebuild (step 7) και ας δούμε τι έχει γίνει τώρα (step 8). Ωωωω τι έκπληξη δεν έχει γίνει ΜΗΔΕΝ το fragmentation!!!. Μειώθηκε (8,69565217391304% στο μηχάνημα μου) αλλά δεν έγινε μηδέν. ????!!!!

Τρέξε μερικές φορές με την σειρά τα step 7 & 8 και θα δείτε ότι ανεβαίνει σε κάποιες εκτελέσεις σε άλλες κατεβαίνει αλλά μηδέν δεν γίνεται!!!

Βάζεις το μυαλό σου να σκεφτεί και λες να παίξω με το fillfactor και το βάζεις 100% και κάνεις rebuild τον index (step 9) και αφού τελειώσει βλέπει πάλι τι έχει γίνει (step 10). Πάλι τα ίδια ….

-- CASE 1

-- step 1 
-- create database with defaults

use master;
go

set nocount on;
go

create database idx1
on
( name='idx1_data', filename='D:\DEFAULT\idx1_data.mdf', size=5MB)
log on
( name='idx1_log', filename='E:\DEFAULT\idx1_log.ldf', size=1MB)
go

-- step 2
-- create table
use idx1;
go

create table T ( col1 int, col2 int, col3 int, col4 char(8000) );
go

-- step 3
-- insert 10000 rows
insert into T values (1,1,1,replicate('a',8000));
go 10000

-- step 4
-- fixing data
declare @i int = 0;
update T set @i=col1=@i+1;
go

declare @i int = 10;
update T set @i=col2=@i+10;
go

declare @i int = 100;
update T set @i=col3=@i+100;
go

select * from T;
go

-- step 5
-- create index on col1
create index T_I1 on T(col1);
go

-- step 6
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 7
-- rebuild index
alter index T_I1 on T 
rebuild;
go
-- step 8
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 9
-- rebuild index with fillfactor
alter index T_I1 on T 
rebuild with (fillfactor=100);
go

-- step 10
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

Περίπτωση 2η

Σε αυτή την περίπτωση είναι ο αυτός που έχει μελετήσει σχεδόν όλα τα blogs εμπιστεύεται κάποιους bloggers και ακολουθεί κατά γράμμα οδηγίες όπως ότι πρέπει να κάνει capacity estimation πριν δημιουργήσει μια database και κάνει pre-allocate τον χώρο.

Αυτός λοιπόν φτιάχνει μια βάση με 1GB data file & 512 MB log file (step 1) κάνει όλα τα βήματα. Φτιάχνει ένα πίνακα (step 2) και γεμίζει αυτόν με 10.000 γραμμές (step 3). Κάνουμε και μερικές διορθώσεις στα δεδομένα ώστε να έχουν σωστές τιμές τα πεδία και βλέπουμε τα δεδομένα (step 4).

Τι έγινε ρε παιδιά; Τα ίδια και αυτός !!!!!!!!!!!!

Και είναι όπως και ο προηγούμενος !!!

-- CASE 2

-- step 1 
-- create database with defaults
use master;
go

set nocount on;
go

create database idx2
on
( name='idx2_data', filename='D:\DEFAULT\idx2_data.mdf', size=1GB)
log on
( name='idx2_log', filename='E:\DEFAULT\idx2_log.ldf', size=512MB)
go

-- step 2
-- create table
use idx2;
go

create table T ( col1 int, col2 int, col3 int, col4 char(8000) );
go

-- step 3
-- insert 10000 rows
insert into T values (1,1,1,replicate('a',8000));
go 10000

-- step 4
-- fixing data
declare @i int = 0;
update T set @i=col1=@i+1;
go

declare @i int = 10;
update T set @i=col2=@i+10;
go

declare @i int = 100;
update T set @i=col3=@i+100;
go

select * from T;
go

-- step 5
-- create index on col1
create index T_I1 on T(col1);
go

-- step 6
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 7
-- rebuild index
alter index T_I1 on T 
rebuild;
go
-- step 8
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 9
-- rebuild index with fillfactor
alter index T_I1 on T 
rebuild with (fillfactor=100);
go

-- step 10
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

Το τρίτο πρόσωπο

Πρέπει να πούμε ότι όπως έχει γίνει μέχρι τώρα το παράδειγμα δεν υπήρχε άλλο process στον SQL Server.

Ας βάλουμε στο παιχνίδι κάποιο άλλο process και ας κάνουμε ξανά τα βήματα από την αρχή αφού πρώτα διαγράψουμε τις δύο αυτές databases μέσα από τον SSMS Object Explorer.

Πριν ξεκινήσουμε ξανά σε ένα νέο query τρέχουμε το παρακάτω script και το αφήνουμε να τρέχει καθ' όλη την διάρκεια του πειράματος μας.

Ακολουθούμε τα βήματα των παραπάνω περιπτώσεων και το αποτέλεσμα είναι το ίδιο!

Σταματήστε το script που είχατε βάλει να τρέχει .

-- ANOTHER PROCESS

-- step 1 
-- create database with defaults
use master;
go

set nocount on;
go

create database idx0
on
( name='idx0_data', filename='D:\DEFAULT\idx0_data.mdf', size=5MB)
log on
( name='idx0_log', filename='E:\DEFAULT\idx0_log.ldf', size=1MB)
go

-- step 2
-- create table
use idx0;
go

create table T ( col1 int, col2 int, col3 int, col4 char(8000) );
go

-- step 3
-- insert 10000 rows
while 1=1
begin
    insert into T values (1,1,1,replicate('a',8000));
end
go

Το μέχρι τώρα συμπέρασμα είναι ότι είσαι προσεκτικός είτε όχι πάντα θα έχει external fragmentation.

Αλλά αλήθεια είναι έτσι;

Περίπτωση 3η

Είναι ακριβώς όπως και η πρώτη αλλά θα βάλουμε αντί για 10000 γραμμές 100000 γραμμές.

Αφού τρέξουμε τα βήμα φτιάξουμε τον index και δούμε το index fragmentation μας περιμένει μια έκπληξη.

Στο μηχάνημα μου έχω fragmentation 0,446428571428571% !

Καθόλου κακό θα έλεγα, αλλά ρε φίλε μόλις τώρα έφτιαξα τον index γιατί έχω πάλι fragmentation;

Ας κάνουμε ένα rebuild (step 7) και για να δούμε το fragmentation (step 8).

Στο μηχάνημα μου έχω 0,448430493273543%.

Για να κάνουμε μερικές φορές ακόμα rebuild index και να δούμε τι έχει γίνει κάθε φορά.

Μετά από μερικές εκτελέσεις θα δούμε ΜΗΔΕΝ, αλλά αν το εκτελέσουμε ξανά πάλι θα δούμε ένα μικρό μεν αλλά όχι μηδέν fragmentation.

-- CASE 3

-- step 1 
-- create database with defaults
use master;
go

set nocount on;
go

create database idx3
on
( name='idx3_data', filename='D:\DEFAULT\idx3_data.mdf', size=5MB)
log on
( name='idx3_log', filename='E:\DEFAULT\idx3_log.ldf', size=1MB)
go

-- step 2
-- create table
use idx3;
go

create table T ( col1 int, col2 int, col3 int, col4 char(8000) );
go

-- step 3
-- insert 100000 rows
insert into T values (1,1,1,replicate('a',8000));
go 100000

-- step 4
-- fixing data
declare @i int = 0;
update T set @i=col1=@i+1;
go

declare @i int = 10;
update T set @i=col2=@i+10;
go

declare @i int = 100;
update T set @i=col3=@i+100;
go

select * from T;
go

-- step 5
-- create index on col1
create index T_I1 on T(col1);
go

-- step 6
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 7
-- rebuild index
alter index T_I1 on T 
rebuild;
go
-- step 8
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 9
-- rebuild index with fillfactor
alter index T_I1 on T 
rebuild with (fillfactor=100);
go

-- step 10
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

Περίπτωση 4η

Είναι ακριβώς όπως και η δεύτερη αλλά θα βάλουμε αντί για 10000 γραμμές 100000 γραμμές.

Αφού τρέξουμε τα βήμα φτιάξουμε τον index και δούμε το index fragmentation μας περιμένει μια έκπληξη.

Στο μηχάνημα μου έχω fragmentation 0,446428571428571% !

Καθόλου κακό θα έλεγα, αλλά ρε φίλε μόλις τώρα έφτιαξα τον index γιατί έχω πάλι fragmentation;

Ας κάνουμε ένα rebuild (step 7) και για να δούμε το fragmentation (step 8).

Στο μηχάνημα μου έχω 0%. Γιούπι ΜΗΔΕΝ ΜΗΔΕΝ ΜΗΔΕΝ!!!!!

-- CASE 4

-- step 1 
-- create database with defaults
use master;
go

set nocount on;
go

create database idx4
on
( name='idx4_data', filename='D:\DEFAULT\idx4_data.mdf', size=1GB)
log on
( name='idx4_log', filename='E:\DEFAULT\idx4_log.ldf', size=512MB)
go

-- step 2
-- create table
use idx4;
go

create table T ( col1 int, col2 int, col3 int, col4 char(8000) );
go

-- step 3
-- insert 10000 rows
insert into T values (1,1,1,replicate('a',8000));
go 100000

-- step 4
-- fixing data
declare @i int = 0;
update T set @i=col1=@i+1;
go

declare @i int = 10;
update T set @i=col2=@i+10;
go

declare @i int = 100;
update T set @i=col3=@i+100;
go

select * from T;
go

-- step 5
-- create index on col1
create index T_I1 on T(col1);
go

-- step 6
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 7
-- rebuild index
alter index T_I1 on T 
rebuild;
go
-- step 8
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

-- step 9
-- rebuild index with fillfactor
alter index T_I1 on T 
rebuild with (fillfactor=100);
go

-- step 10
-- viewing EXTERNAL FRAGMENTATION
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    FROM sys.dm_db_index_physical_stats(
        DB_ID(),OBJECT_ID('T'),2, DEFAULT, 'LIMITED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

Για να κάνουμε μερικές φορές ακόμα rebuild index και να δούμε τι έχει γίνει κάθε φορά.

Όσες φορές και να κάνω rebuild πάντα μα πάντα θα έχω ΜΗΔΕΝ!!!!

Τι κρύβεται πίσω από την κουρτίνα; Πώς εξηγούνται όλα αυτά;

Συνεχίζεται…

/*antonch*/

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.