go backarticles

Articles of SQLschool.gr Team

When the filtered indexes saves

Antonios Chatzipavlis

Η παλαιότερη και πιο συνηθισμένη ερώτηση που γίνεται είναι αυτή που αφορά το query performance. Για αυτή την ερώτηση έχουν γραφτεί εκατομμύρια γραμμές σε άρθρα και έχουν γίνει χιλιάδες παρουσιάσεις.

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

Αυτό που μπορώ με σιγουριά να πω είναι ότι για να δώσει κάποιος λύση στο εκάστοτε πρόβλημα θα πρέπει να έχει γνώση των εσωτερικών δομών και αρχιτεκτονική του SQL Server πάνω από καλώς.

Δείτε μια περίπτωση που μου έτυχε χθες.

Έχω μια database αρκετά συμπαθητική σε μέγεθος και η οποία είναι ζωτικής σημασίας. Σε αυτή υπάρχουν δύο κυρίαρχοι πίνακες ο Τ1 με πάνω από 100Μ rows και ο Τ2 με μερικές χιλιάδες.

Σε αυτούς τους πίνακες υπάρχουν indexes και φυσικά υπάρχει και primary key.

Υπάρχει ένα view που μαζεύει δεδομένα από αυτούς τους δύο και το οποίο είναι το παρακάτω


CREATE VIEW [dbo].V1
AS
SELECT T1.*
FROM   dbo.T1 
LEFT OUTER JOIN   dbo.T2 ON dbo.T1.Code = dbo.T2.Code
WHERE     (dbo.T1.Status = 0) AND (dbo.T1.Display = 1)
GO

Και γίνεται κλήση στο συγκεκριμένο view κάθε 5 sec καθώς πρέπει να γεμίζει ένα monitor tool.


select * from dbo.V1

Και το execution plan για αυτό είναι το παρακάτω



Επίσης αυτό έχει το παρακάτω IO cost



Όπως φαίνεται για να επιστρέψει 5 rows διαβάζει το σύμπαν και επειδή είναι μεγάλος ο αριθμός των εγγραφών το query παραλληλίζει για να επιστρέψει όσο το δυνατόν γρήγορα τα δεδομένα. Δεν με απασχολεί ιδιαίτερα ο παραλληλισμός καθώς είναι κάτι που γίνεται αλλά αυτό που δημιούργησε το παραλληλισμό και δεν είναι άλλο από τoν αριθμό των logical reads που είναι τεράστιος.

Σε ένα quiz που έκανα για να δω τις απαντήσεις με συναδέλφους όλοι πρότειναν να δημιουργηθεί index composite στα πεδία που χρησιμοποιούνται στο where του view δηλαδή ο παρακάτω


CREATE NONCLUSTERED INDEX [IDX_T1_Status_Display] ON [dbo].[T1]
(
    [Status] ASC,
    [Display] ASC
)

Το αποτέλεσμα όμως της εκτέλεσης είναι το εξής



Όπως φαίνεται δεν γίνεται χρήση του index και ο λόγος είναι απλός οι τιμές που έχουν αυτά τα πεδία είναι είτε 0 είτε ένα 1, άρα το selectivity και το density του index είναι τέτοιο που οδηγεί τον optimizer να κάνει table scan μόνο που στην περίπτωση μας ο πίνακας έχει clustered index και έτσι κάνει clustered index scan.

Θα μπορούσα να σταματήσω εδώ το άρθρο αυτό και να σας το θέσω σαν quiz αλλά δεν θα το κάνω :)

Η απάντηση ήταν και ξεκάθαρη από την αρχή στο μυαλό μου και είναι δημιουργία filtered index με βάση το πώς ζητάει τις εγγραφές το view. Ναι θα φτιάξω ένα index για ένα και μόνο query που τυγχάνει όμως να είναι το κυρίαρχο για την εφαρμογή και την βάση, και δεν είναι τσαπατσούλικη λύση καθώς όποιος πιστεύει ότι πρέπει να έχει μόνο γενικού σκοπού indexes λυπάμαι αλλά έχει λάθος.

Έτσι λοιπόν ο index έγινε filtered όπως παρακάτω


CREATE NONCLUSTERED INDEX [IDX_T1_Status_Display] ON [dbo].[T1]
(
    [Status] ASC,
    [Display] ASC
)
WHERE ([status]=(0) AND [display]=(1))

Και το αποτέλεσμα της εκτέλεσης έγινε όπως παρακάτω



Όπως εμφανώς είναι αναγνώσιμο τα logical reads έχουν δραματικά μειωθεί και είναι εύκολο για τον καθένα πλέον να φανταστεί το πόσο έχει βελτιωθεί το query αλλά και η απόκριση της εφαρμογής.


άμα είσαι Clay Regazzoni...


/*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 03 Nov 2016 @ 4:28 PM Θόδωρος Αραμπατζής wrote:

Μπράβο Αντώνη, εξαιρετικό !!!κυρίως γιατί γκρεμίζεις τον μύθο που λέει ότι η χρήση filtered indexes είναι κακή πρακτική !!!!

user-gravatar

On 04 Nov 2016 @ 1:02 PM Antonios Chatzipavlis wrote:

Thanks Θεόδωρε

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.