go backarticles

Articles of SQLschool.gr Team

How a SELECT COUNT(*) query executed

Antonios Chatzipavlis

Χθες κατά την διάρκεια του μαθήματος που έκανα ένα αγαπητός συνάδελφος στην συζήτηση που είχαμε ανέφερε ότι όποτε κάνεις ένα 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*/

 


Antonios Chatzipavlis

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.


Relative Articles

Comments

user-gravatar

On 18 Jul 2013 @ 9:34 AM Μάρκου Αθανάσιος wrote:

Πολύ ενδιαφέρον άρθρο, μπράβο.

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.