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

Επειδή δεν θέλεις να το κάνεις αυτό στο παραγωγικό περιβάλλον¹ θα πρέπει για να πειραματιστείς με κάτι τέτοιο ένα μη παραγωγικό περιβάλλον² να κατεβάσεις τα παραγωγικά δεδομένα³ και να δημιουργήσεις τον index⁴ και μετά να τρέξεις πάλι το query και να αποφανθείς αν πραγματικά αυτός λύνει ουσιαστικά το πρόβλημα της απόδοσης αλλιώς θα πρέπει να δοκιμάσεις κάποιον άλλον και ίσως και άλλον και άλλον…

Συμπληρωματικές σκέψεις της παραπάνω παραγράφου

¹ιδιαίτερα όταν αυτό δεν είναι Enterprise Edition που δεν έχεις την δυνατότητα να κάνεις ONLINE index build ώστε να το επιβαρύνεις το δυνατόν λιγότερο
²που μπορεί να μην έχεις διαθέσιμο
³που πέρα από χρονοβόρο μπορεί να μην επιτρέπεται να γίνει
⁴που ανάλογα με τον όγκο θα χρειαστεί και χρόνο

Στο SQL Server υπάρχουν αρκετά πράγματα που είναι undocumented και που όσοι περιστασιακά ασχολούνται με αυτόν δεν τα γνωρίζουν (θα μπορούσα να πω ότι και όσοι ασχολούνται με αυτόν συστηματικά δεν τα γνωρίζουν, πλην των "καμένων"). Επίσης θα πρέπει να σημειώσω ότι την αντίστοιχη υλοποίηση εκτελείτε στο παρασκήνιο από τον DTA (Database Tuning Advisor)

Hypothetical Index

Στο SQL Server μπορείς να δημιουργήσεις ένα υποθετικό (hypothetical) index.

Ένας τέτοιος index είναι ένας index που όταν το δημιουργούμε φτιάχνονται μόνο τα metadata του στον sys.indexes και τα statistics αυτού αλλά στην πραγματικότητα δεν δημιουργείτε.

'Ένα τέτοιo index μπορεί κάποιος να το δημιουργήσει αρκεί στο DML statement να προσθέσει τo option STATISTICS_ONLY (που είναι documented) ίσο με την τιμή -1 (μείον ένα, που είναι undocumented).

AUTOPILOT mode

Η δημιουργία ενός τέτοιου index δεν λαμβάνεται υπόψη από τον query optimizer εκτός και αν στο session ενεργοποιηθεί το AUTOPILOT mode.

Κάτι τέτοιο μπορεί να γίνει με την SET AUTOPILOT ON/OFF (undocumented) αλλά από μόνο του αυτό δεν φτάνει. Θα πρέπει πριν από αυτή ο query optimizer να αποκτήσει την γνώση για τον hypothetical index και αυτό μπορεί να γίνει με την χρήση του DBCC AUTOPILOT(undocumented).

Για την DBCC AUTOPILOT όπως είναι γνωστό σε όσους ασχολούνται σε βάθος με τον SQL Server μπορείς να μάθεις τις παραμέτρους που παίρνει (όπως και για τα άλλα DBCC statements) ενεργοποιώντας το trace flag 2588 και μετά να εκτελέσεις την DBCC HELP

DBCC TRACEON (2588);
DBCC HELP ( 'AUTOPILOT' );

Το αποτέλεσμα του παραπάνω είναι το παρακάτω

DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])

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

Το typeid μπορεί να πάρει τις τιμές
5 - για να ξεκινήσεις ένα νέο session ή να καθαρίσεις το υπάρχον,
6 - για να χρησιμοποιηθεί o cluster index και
0 - για να χρησιμοποιηθούν οι non clustered indexes.

Το dbid είναι το database id

Το maxQueryCost πρέπει να είναι το query cost αλλά δεν το έχω δει σε ότι έχω κάνει να χρησιμοποιείται.

Το tabid είναι το table id.

Το indid είναι το index id.

To pages για να γίνει εξομοίωση των physical pages

To flag παραμένει άγνωστο

Το rowcounts χρησιμοποιείται για να ορίσει τον αριθμό των rows.

Από τα παραπάνω δεν χρειάζονται όλες οι παράμετροι για να κάνουμε δουλειά. Αυτές που χρειάζονται είναι οι typeid, dbid, tabid, indid.

Τις τιμές για το πρώτο τις αναφέρω παραπάνω για τα άλλα μπορεί κάποιος να τα πάρει χρησιμοποιώντας τα παρακάτω functions σε ένα select statement

select   DB_ID() as dbid
    ,    OBJECT_ID('<schema>.<table name>') as tabid
    ,    INDEXPROPERTY(OBJECT_ID('<schema>.<table name>'), '<hypo index name>', 'IndexID') as indid;

Example

Για πάρετε μια γεύση το πως όλα αυτά μαζί συνεργάζονται θα κάνω ένα παράδειγμα χρησιμοποιώντας την WorldWideImporters database και τον πίνακα Sales.Orders. Σε αυτόν τον πίνακα δεν υπάρχει index στο OrderDate field και όπως είναι λογικό ένα query όπως το παρακάτω θα δείξει στο execution plan

select *
from sales.Orders
where orderdate ='2013-01-10';
go

Για να φτιάξω το hypothetical index αρκεί να κάνω ότι ανέφερα και παραπάνω όπως φαίνεται και στο παρακάτω παράδειγμα (προσέξτε το statistics_only = -1)

CREATE NONCLUSTERED INDEX hypoindex
ON [Sales].[Orders] ([OrderDate]) with statistics_only = -1

Για να πάρω τις τιμές που μου χρειάζονται για τις παραμέτρους της DBCC AUTOPILOT θα εκτελέσω το παρακάτω

select   DB_ID() as dbid
    ,    OBJECT_ID('Sales.Orders') as tabid
    ,    INDEXPROPERTY(OBJECT_ID('Sales.Orders'), 'hypoindex', 'IndexID') as indid

Οι τιμές που στο περιβάλλον μου επιστρέφονται είναι dbid=9, tabid=1154103152 και indid=9

Όλα αυτά μαζί τα εκτελώ όπως παρακάτω και θα δω αλλαγμένο το execution plan στο οποίο φαίνεται να γίνεται η χρήση του hypothetical index.

dbcc autopilot (5,11);
dbcc autopilot (0,11,1154103152,9);
go

set autopilot on;
go

select *
from sales.Orders
where orderdate ='2013-01-10';
go

set autopilot off;
go

DROP Hypothetical Indexes

Αφού έχουμε αξιολογήσει το αποτέλεσμα της εργασίας μας καλό είναι να σβήνουμε τους hypothetical indexes που έχουμε δημιουργήσει, αν και δεν έχουν καμία επίπτωση. Ο λόγος είναι απλός διότι αν κάποιος πάει να κάνει κάποια εργασία σε αυτή θα δει στα statistics του συγκεκριμένου πίνακα ένα μεγάλο (πιθανά) πλήθος από τέτοια και θα χρειαστεί κάποιο χρόνο μέχρι να καταλάβει τι έχει στα χέρια του.

Για να βρει κάποιος τους hypothetical indexes αρκεί να τρέξει το παρακάτω query αλλά θα πρέπει να κάνει και DROP STATISTICS πέρα από DROP INDEX.

select    SCHEMA_NAME(o.schema_id) as schema_name
    ,    o.name as table_name
    ,    i.name as index_name
from sys.indexes as i
join sys.objects as o 
    on o.object_id = i.object_id
where is_hypothetical=1

//antonch