Friday, October 22, 2010

Why Indexing



I decided to create some sample table with different features. All the fields are the same, except in the primary key or indexes. In the next step, I inserted nearly a million records to them and check the fragmentation and storage size. After checking those values, I re-organize their existing indexes and compare to each other and results are as follow:
SQL Version 2008 Express Edition:
Microsoft SQL Server Management Studio            10.50.1600.1
Microsoft Data Access Components (MDAC)            3.85.1132
Microsoft MSXML                        2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer                    8.0.6001.18702
Microsoft .NET Framework                    2.0.50727.3603
Operating System                        5.1.2600


Use TestDB



Go



--Only Primary key

Create Table tblJobDetail_PK
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)
   
    Constraint PK_tblJobDetail_PK Primary Key (JobNo, JobMonth, JobTransNo)
)

Go
--Only Clustered Index
 Create Table tblJobDetail_IX
     (JobNo Varchar(10),
     JobMonth SmallDateTime,
     JobTransNo SmallInt,
     TransSource Varchar(10),
     SmallMoneyField SmallMoney,
     CharField Char(20),
     DateTimeField DateTime,
     DateField Date,
    FloatField Float,
     IntField Int,
     SmallIntField SmallInt,
     VarcharField Varchar(300)     )

    Create Clustered Index IX_tblJobDetail_IX On tblJobDetail_IX (JobNo, JobMonth, JobTransNo ASC) With FillFactor = 80

Go
--Only Non-Clustered Index
 Create Table tblJobDetail_NIX
    (JobNo Varchar(10),
     JobMonth SmallDateTime,
     JobTransNo SmallInt,
     TransSource Varchar(10),
     SmallMoneyField SmallMoney,
     CharField Char(20),
     DateTimeField DateTime,
     DateField Date,
    FloatField Float,
     IntField Int,
     SmallIntField SmallInt,
     VarcharField Varchar(300)
      )

    Create NonClustered Index IX_tblJobDetail_NIX On tblJobDetail_NIX (JobNo, JobMonth, JobTransNo ASC) With FillFactor = 80

Go

--Only Unique Clustered Index
 Create Table tblJobDetail_UIX
    (JobNo Varchar(10),
     JobMonth SmallDateTime,
     JobTransNo SmallInt,
     TransSource Varchar(10),
     SmallMoneyField SmallMoney,
     CharField Char(20),
     DateTimeField DateTime,
     DateField Date,
    FloatField Float,
     IntField Int,
     SmallIntField SmallInt,
     VarcharField Varchar(300)   )
    Create Unique Clustered Index IX_tblJobDetail_UIX On tblJobDetail_UIX (JobNo, JobMonth, JobTransNo ASC) With FillFactor = 80

Go

--Primary Key with Non-Clustered Index (Non-Clustered Index include Primary Key fields)
 Create Table tblJobDetail_PKIX
    (JobNo Varchar(10),
     JobMonth SmallDateTime,
     JobTransNo SmallInt,
     TransSource Varchar(10),
     SmallMoneyField SmallMoney,
     CharField Char(20),
     DateTimeField DateTime,
     DateField Date,
    FloatField Float,
     IntField Int,
     SmallIntField SmallInt,
     VarcharField Varchar(300)    Constraint PK_tblJobDetail_PKIX Primary Key (JobNo, JobMonth, JobTransNo )
 )

   Create NonClustered Index IX_tblJobDetail_NPKIX On tblJobDetail_PKIX (JobNo, JobMonth, JobTransNo, TransSource ASC)With FillFactor = 80

Go

--Primary Key With Non-Clustered Index (Non-Clustered Index does not include any field from Primary Key)
 Create Table tblJobDetail_NPKIX
    (JobNo Varchar(10),
     JobMonth SmallDateTime,
     JobTransNo SmallInt,
     TransSource Varchar(10),
     SmallMoneyField SmallMoney,
     CharField Char(20),
     DateTimeField DateTime,
     DateField Date,
    FloatField Float,
     IntField Int,
     SmallIntField SmallInt,
     VarcharField Varchar(300)
     Constraint PK_tblJobDetail_NPKIX Primary Key (JobNo, JobMonth, JobTransNo )
 )

    Create NonClustered Index IX_tblJobDetail_NPKIX1 On tblJobDetail_NPKIX (TransSource ASC)With FillFactor = 80

Go

--NO Primary Key and NO Index as well
 Create Table tblJobDetail_NOIX
     (JobNo Varchar(10),
     JobMonth SmallDateTime,
     JobTransNo SmallInt,
     TransSource Varchar(10),
     SmallMoneyField SmallMoney,
     CharField Char(20),
     DateTimeField DateTime,
     DateField Date,
     FloatField Float,
     IntField Int,
     SmallIntField SmallInt,
     VarcharField Varchar(300)
     )

Go


Declare @RowNo Int
 Declare @JobNo Varchar(10)
 Declare @JobMonth SmallDateTime
 Declare @JobTransNo Int
 Declare @TransSource Varchar(10)
Set @RowNo = 1
Set @JobNo = ''
 Set @JobMonth = '07/01/2032'
 Set @JobTransNo = 1
Set @TransSource = ''
While @RowNo <= 210000
 Begin
     While @JobTransNo <= 1000
    Begin
         Set @JobNo = Cast(DATEPART(Month, @JobMonth) As CHAR(2)) + Cast(DATEPART(Day, @JobMonth) As CHAR(2)) + Cast(DATEPART(Year, @JobMonth) As CHAR(4))

        Set @TransSource = DATENAME(Month, @JobMonth)

      

        Insert Into tblJobDetail_PK (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'

      

        Insert Into tblJobDetail_IX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_NIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_NPKIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_PKIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_UIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_NOIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Set @JobTransNo = @JobTransNo + 1

        Set @RowNo = @RowNo + 1

    End

  

    Set @JobTransNo = 1

    Set @JobMonth = DATEADD(Month, -1, @JobMonth)

End



Go

  

Run the routine five times and got 1,050,000 records.


After insert is being done in the table, the index space and data space for each table is as follow:

  1. tblJobDetail_PK
    Before Re-Organizing Index 
    After Re-Organizing Index 
    Variance
    Row Count 
    1,050,000 
    1,050,000 
    Index Space 
    0.953 MB 
    0.953 MB 
    Data Space 
    155.711 MB
    147.008 MB
    8.703 MB 
    Page Fullness 
    91.89 % 
    96.78 %
    -4.89 %
    Total Fragmentation 
    16.23 %
    0.67 %
    15.56 % 
    Average Row Size 
    138 
    138 
    Dept 
    3 
    3 
    Leaf Level Rows 
    1,060,600
    1,056,500
    4,100 
    Maximum Row Space 
    141 
    141
    Minimum Row Space 
    135 
    135 
    Pages 
    19,931 
    18,817
    1,114 
  2. tblJobDetail_IX
    Before Re-Organizing Index 
    After Re-Organizing Index 
    Variance
    Row Count 
    1,050,000 
    1,050,000 
    Index Space 
    1.086 MB 
    1.086 MB 
    Data Space 
    159.133 MB 
    154.445 MB
    4.688 MB 
    Page Fullness 
    93.16 % 
    92.87 %
    0.29 % 
    Total Fragmentation
    15.95 % 
    0.72 %
    15.23 % 
    Average Row Size 
    140 
    140 
    Dept 
    3 
    3 
    Leaf Level Rows 
    1,055,800 
    1,020,700
    35,100 
    Maximum Row Space 
    143 
    143 
    Minimum Row Space 
    137 
    137 
    Pages 
    20,369 
    19,769
    600 

     

  1. tblJobDetail_NIX
    Before Re-Organizing Index 
    After Re-Organizing Index
    Variance
    Row Count 
    1,050,000 
    1,050,000 
    Index Space 
    48.758 MB 
    40.109 MB 
    8.649 MB
    Data Space 
    148.063 MB 
    148.063 MB 
    Page Fullness 
    67.05 % 
    81.66 % 
    -14.61 %
    Total Fragmentation 
    51.03 % 
    1.06 % 
    49.97 %
    Average Row Size 
    30 
    30 
    Dept 
    3 
    3 
    Leaf Level Rows
    1,050,000 
    1,050,000 
    Maximum Row Space 
    30 
    30 
    Minimum Row Space 
    30 
    30 
    Pages 
    6,189 
    5,082 
    1,107

     

  1. tbl JobDetail_UIX
    Before Re-Organizing Index 
    After Re-Organizing Index 
    Variance
    Row Count 
    1,050,000 
    1,050,000 
    Index Space 
    1.063 MB 
    1.063 MB 
    Data Space
    155.711 MB 
    152.391 MB 
    3.32 MB
    Page Fullness 
    91.26 % 
    93.37 % 
    -2.11 %
    Total Fragmentation 
    16.31 % 
    0.72 % 
    15.59 %
    Average Row Size 
    138 
    138 
    Dept 
    3 
    3 
    Leaf Level Rows 
    1,043,900 
    1,051,900 
    Maximum Row Space 
    141 
    141 
    Minimum Row Space 
    135 
    135 
    Pages
    19,931 
    19,506 
    425

     

  1. tbl JobDetail_PKIX
Clustered Index 
Before Re-Organizing Index 
After Re-Organizing Index 
Variance
Row Count 
1,050,000 
1,050,000 
Index Space 
47.680 MB 
47.680 MB 
Data Space 
155.711 MB 
147.008 MB 
8.703 MB
Page Fullness 
91.89 % 
96.78 % 
-4.89 %
Total Fragmentation 
16.22 % 
0.67 % 
15.55 %
Average Row Size 
138 
138 
Dept 
3 
3 
Leaf Level Rows 
1,060,600 
1,056,500 
4,100
Maximum Row Space 
141 
141 
Minimum Row Space 
135 
135 
Pages 
19,931 
18,817 
1,114

Non-Clustered Index 
Before Re-Organizing Index
After Re-Organizing Index 
Variance
Row Count 
1,050,000 
1,050,000 
Index Space 
47.680 MB 
40.672 MB 
7.008 MB
Data Space 
155.711 MB 
147.008 MB 
8.703 MB
Page Fullness 
70.35 % 
82.89 % 
-12.54 %
Total Fragmentation 
53.23 % 
1.05 % 
52.18 %
Average Row Size
30 
30 
Dept 
3 
3 
Leaf Level Rows 
1,050,000 
1,050,000 
Maximum Row Space 
33 
33 
Minimum Row Space 
27 
27 
Pages 
5,931 
5,034 
897

  1. tbl JobDetail_NPKIX
Clustered Index 
Before Re-Organizing Index 
After Re-Organizing Index 
Variance
Row Count
1,050,000 
1,050,000 
Index Space 
47.680 MB 
47.680 MB 
Data Space 
155.711 MB 
147.008 MB 
8.703 MB
Page Fullness 
91.89 % 
96.78 % 
-4.89 %
Total Fragmentation 
16.23 % 
0.67 % 
15.56 %
Average Row Size 
138 
138 
Dept 
3 
3 
Leaf Level Rows 
1,054,900 
1,050,800
4,100
Maximum Row Space 
141 
141 
Minimum Row Space 
135 
135 
Pages 
19,931 
18,817 
1,114

Non-Clustered Index 
Before Re-Organizing Index 
After Re-Organizing Index 
Variance
Row Count 
1,050,000 
1,050,000 
Index Space 
47.680 MB 
40.883 MB 
6.797 MB
Data Space
155.711 MB 
147.008 MB 
8.703 MB
Page Fullness 
70.35 % 
82.45 % 
-12.1 % 
Total Fragmentation 
53.08 % 
0.97 % 
52.11 % 
Average Row Size 
30 
30 
Dept 
3 
3 
Leaf Level Rows 
1,050,000 
1,050,000 
Maximum Row Space 
33 
33 
Minimum Row Space 
27 
27 
Pages 
5,931
5,061 
870 


  1. tblJobDetail_NOIX
    Before Re-Organizing Index 
    Row Count 
    1,050,000 
    Index Space 
    0 MB 
    Data Space 
    148.063 MB 
    Page Fullness 
    0 % 
    Total Fragmentation 
    0 % 
    Average Row Size 
    0 
    Dept 
    0 
    Leaf Level Rows 
    0 
    Maximum Row Space 
    0 
    Minimum Row Space 
    0
    Pages 
    0 

     

In the next step, I am trying to do an update on all tables on same column. Here are the results:

      Update tblJobDetail_IX Set DateField = CONVERT(Date, JobMonth)



Update tblJobDetail_NIX Set DateField = CONVERT(Date, JobMonth)


Update tblJobDetail_NOIX Set DateField = CONVERT(Date, JobMonth)


Update tblJobDetail_NPKIX Set DateField = CONVERT(Date, JobMonth)


Update tblJobDetail_PK Set DateField = CONVERT(Date, JobMonth)


Update tblJobDetail_PKIX Set DateField = CONVERT(Date, JobMonth)


Update tblJobDetail_UIX Set DateField = CONVERT(Date, JobMonth)
Posted by Unknown at 9:01 AM 0 comments
Subscribe to: Posts (Atom)