Χθες κατά την διάρκεια του μαθήματος που έκανα ένα αγαπητός συνάδελφος στην συζήτηση που είχαμε ανέφερε ότι όποτε κάνεις ένα query που κάνει COUNT(*)  στα δεδομένα μας κάνει πάντα table scan.

Φυσικά δεν θα μπορούσα να αφήσω την ευκαιρία χαμένη και δείξω ότι δεν είναι έτσι τα πράγματα και για αυτό του έκανα το παρακάτω demo με τις απαραίτητες φυσικά επεξηγήσεις το οποίο θα μοιραστώ και με εσάς.

Για το λόγο αυτό ας φτιάξουμε ένα πίνακα του οποίου το record length θα είναι τέτοιο ώστε σε κάθε data page να χωράει ένα record και αυτό το κάνω ώστε να γίνουν ευκολότερα τα μαθηματικά που θα χρειαστούμε για το παράδειγμα μας.

CREATE TABLE T

(

         COL1 INT IDENTITY

       , COL2 INT DEFAULT (1)

       , COL3 CHAR(8000) DEFAULT (REPLICATE('A',8000))

);

GO

INSERT INTO T DEFAULT VALUES;

GO 10000

UPDATE T SET COL2=COL1;

GO

Ο πίνακας μας έτσι όπως είναι  δεν έχει κάποιον index και είναι σε heap όπως φαίνεται από την εκτέλεση των στατιστικών των indexes

SELECT  index_id

       , index_type_desc

       , index_depth

       , index_level

       , page_count

       , record_count

FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('T'),DEFAULT,DEFAULT,'DETAILED')

GO

index_id    index_type_desc   index_depth index_level page_count           record_count

----------- ----------------- ----------- ----------- -------------------- --------------------

0           HEAP              1           0           10000                10000

Αν κάνουμε COUNT(*) σε αυτό λογικό είναι ότι αυτό θα κάνει table scan όπως και φαίνεται από το execution plan

SET SHOWPLAN_ALL ON;

GO

SELECT COUNT(*) FROM T;

GO

SET SHOWPLAN_ALL OFF;

GO

StmtText                                                                     StmtId      NodeId      Parent      PhysicalOp        LogicalOp                    

---------------------------------------------------------------------------- ----------- ----------- ----------- ----------------- ---------------

SELECT COUNT(*) FROM T;                                                      1           1           0           NULL              NULL                         

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))  1           2           1           Compute Scalar    Compute Scalar                

       |--Stream Aggregate(DEFINE:([Expr1007]=Count(*)))                     1           3           2           Stream Aggregate  Aggregate                    

            |--Table Scan(OBJECT:([demodb].[dbo].[T]))                       1           4           3           Table Scan        Table Scan                   

Αν σε αυτό τον πίνακα φτιάξουμε έναν clustered index και δούμε τα index statistics θα δούμε ότι πλέον δεν έχουμε HEAP αλλά την αναμενόμενη δομή ενός clustered index.

CREATE CLUSTERED INDEX I1 ON T(COL1);

GO

SELECT  index_id

       , index_type_desc

       , index_depth

       , index_level

       , page_count

       , record_count

FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('T'),DEFAULT,DEFAULT,'DETAILED')

GO

index_id    index_type_desc                                              index_depth index_level page_count           record_count

----------- ------------------------------------------------------------ ----------- ----------- -------------------- --------------------

1           CLUSTERED INDEX                                              3           0           10000                10000

1           CLUSTERED INDEX                                              3           1           19                   10000

1           CLUSTERED INDEX                                              3           2           1                    19

Αν κάνουμε COUNT(*) όπως είναι τώρα θα δούμε το λογικό που δεν είναι κάτι άλλο από να χρησιμοποιήσει τον clustered index καθώς πλέον με βάση αυτόν είναι δομημένα τα δεδομένα μας στα data pages, και όπως φαίνεται και από το execution plan κάνει scan με βάση αυτόν (clustered index)

SET SHOWPLAN_ALL ON;

GO

SELECT COUNT(*) FROM T;

GO

SET SHOWPLAN_ALL OFF;

GO

StmtText                                                                     StmtId      NodeId Parent PhysicalOp           LogicalOp                     

---------------------------------------------------------------------------- ----------- ------ ------ -------------------- --------------------

SELECT COUNT(*) FROM T;                                                      1           1      0      NULL                 NULL                         

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))  1           2      1      Compute Scalar       Compute Scalar               

       |--Stream Aggregate(DEFINE:([Expr1007]=Count(*)))                     1           3      2      Stream Aggregate     Aggregate                    

            |--Clustered Index Scan(OBJECT:([demodb].[dbo].[T].[I1]))        1           4      3      Clustered Index Scan Clustered Index Scan         

Αν έτσι όπως είμαστε φτιάξουμε ακόμα ένα index ο οποίος θα είναι έτσι και αλλιώς nonclustered.

CREATE INDEX I2 ON T(COL2);

GO

Αν δούμε τα index statistics θα δούμε ότι έχουμε πλέον δύο indexes

SELECT  index_id

       , index_type_desc

       , index_depth

       , index_level

       , page_count

       , record_count

FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('T'),DEFAULT,DEFAULT,'DETAILED')

GO

index_id    index_type_desc                                              index_depth index_level page_count           record_count

----------- ------------------------------------------------------------ ----------- ----------- -------------------- --------------------

1           CLUSTERED INDEX                                              3           0           10000                10000

1           CLUSTERED INDEX                                              3           1           19                   10000

1           CLUSTERED INDEX                                              3           2           1                    19

2           NONCLUSTERED INDEX                                           2           0           20                   10000

2           NONCLUSTERED INDEX                                           2           1           1                    20

Εκτελώντας πάλι το COUNT(*) θα δούμε ότι αυτή την φορά χρησιμοποιεί τον δεύτερο index και αυτό γιατί ο SQL Server όταν κάνει COUNT(*)  σε ένα πίνακα στο οποίο υπάρχουν indexes χρησιμοποιεί πάντα αυτόν που έχει τον μικρότερο αριθμό σελίδων στο leaf level του καθώς μπορεί από εκεί με ασφάλεια να βγάλει το αποτέλεσμα.

SET SHOWPLAN_ALL ON;

GO

SELECT COUNT(*) FROM T;

GO

SET SHOWPLAN_ALL OFF;

GO

StmtText                                                                     StmtId      NodeId Parent PhysicalOp       LogicalOp                    

---------------------------------------------------------------------------- ----------- ------ ------ ---------------- ---------------

SELECT COUNT(*) FROM T;                                                      1           1      0      NULL             NULL                          

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))  1           2      1      Compute Scalar   Compute Scalar               

       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))                     1           3      2      Stream Aggregate Aggregate                    

            |--Index Scan(OBJECT:([demodb].[dbo].[T].[I2]))                  1           4      3      Index Scan       Index Scan                    

/*antonch*/