go backsqlschool blogs list

Update Statistics and Trace Flag 2371

by Antonios Chatzipavlis

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

Πρόσφατα δέχθηκα μια ερώτηση από έναν συνάδελφο που απετέλεσε  το ερέθισμα για το post αυτό.

Ο συγκεκριμένος συνάδελφος αντιμετώπισε ένα πρόβλημα με την απόδοση ενός query που ενώ δούλευε μια χαρά ξαφνικά άρχισε να εμφανίζει μεγάλο χρόνο εκτέλεσης. Το συγκεκριμένο query διάβαζε πραγματικά μεγάλο όγκο εγγραφών και η αρχική έρευνα έδειξε ότι τα στατιστικά ενός από τους πίνακες που εμπλέκονταν σε αυτό ήταν outdated.

Μετά από την ενημέρωση αυτών το συγκεκριμένο query επανήλθε στον φυσιολογικό ,για το μέγεθος των δεδομένων και των διαθέσιμων πόρων, χρόνο του. Εύλογη ήταν λοιπόν η απορία του συναδέλφου γιατί δεν είχαν ενημερωθεί αυτόματα τα στατιστικά του πίνακα καθώς είχε ενεργοποιημένο, και σωστά το είχε, το auto update statistics.

Για όσους δεν το γνωρίζουν πρέπει να αναφέρω ότι στον SQL Server υπάρχει ένας "κανόνας" για το πότε θα ενημερώνονται τα στατιστικά και αυτός δε είναι άλλος από τον "κανόνα" του 20%+500 rows. Τι σημαίνει αυτό;

Σημαίνει ότι αν υπάρχουν αλλαγές τέτοιες στον πίνακα που να είναι αθροιστικά πάνω από το 20%+500 εγγραφές του συνόλου των εγγραφών στον πίνακα τα στατιστικά χαρακτηρίζονται σαν outdated και ο query optimizer όταν θα τα ζητήσει θα βγάλει άλλο execution plan από αυτό που θα έβγαζε αν ήταν ενημερωμένα.

Αν έχω για παράδειγμα ένα πίνακα με 10000 εγγραφές αυτός ο "κανόνας" είναι στην ουσία 2500 εγγραφές που έχουν αλλάξει σε αυτό. Αντίστοιχα αν έχω 100000 εγγραφές πάμε στις 25000 και να έχω 1000000 εγγραφές πάμε στις 250000 εγγραφές.

Ο "κανόνας" αυτός στις περισσότερες περιπτώσεις δουλεύει, υπάρχει όμως ένα θέμα όταν έχω αρκετά μεγάλους πίνακες καθώς η στιγμή που θα ενημερωθούν αυτά αργεί να έρθει επειδή το 20%+500 threshold δεν έχει "πιαστεί". Αυτό είναι ένα γνωστό θέμα και αποτελεί εφιάλτη των απανταχού DBAs για αυτό και πάντα λέω και έχω γράψει ότι πάντα κάνουμε σε τακτά χρονικά διαστήματα update statistics.

Επειδή όμως αυτή η διαδικασία δεν μπορεί να τρέχει συνέχεια καθώς έχει Ι/Ο από τον SQL Server 2008 R2 SP1 έχουμε ένα φάρμακο αρκετά δραστικό, και μάλιστα δεν βλέπω και τον λόγο γιατί να μην χρησιμοποιείτε πάντα. 

Αυτό δεν είναι άλλο από το να ξεκινήσουμε τον SQL Server instance με το trace flag 2371. To οποίο αλλάζει την συμπεριφορά του κανόνα του 20%+500 rows σε δυναμικό threshold το οποίο βασίζεται στον αριθμό των γραμμών του πίνακα. Με αυτό σε μεγάλους σε αριθμό εγγραφών πίνακες έχω μικρότερο threshold με αποτέλεσμα σε αυτούς να έχω συχνότερη ενημέρωση των στατιστικών.

Ιδανικές περιπτώσεις για την ενεργοποίηση του trace flag αυτού είναι ERP όπως το SAP, Dynamic AX ή databases που αντιμετωπίζουν συχνά το σενάριο που παραπάνω περιεγράφηκε.

Keep Rocking with SQL Server

/*antonch*/

Ημερομηνία: 13 January 2014 16:10
Αξιολόγηση: ( 1 )
Κατηγορίες:
Tags:
Share it:

Σχετικά Blog Post

Σχόλια - Comments

user-gravatar

Στις 13 Jan 2014 @ 7:59 PM o/η Kostas Backas έγραψε:

Ωραία!Σύμφωνα με το link αυτό: http://blogs.technet.com/b/lobapps/archive/2012/08/28/how-do-i-work-with-trace-flags.aspx μπορεί να ενεργοποιηθεί με 2 τρόπους. Ποιόν προτείνεις;Κώστας

user-gravatar

Στις 14 Jan 2014 @ 11:49 AM o/η Antonios Chatzipavlis έγραψε:

Καλημέρα. Το συγκεκριμένο trace flag το βάζουμε global.

user-gravatar

Στις 14 Jan 2014 @ 12:56 PM o/η Kostas Backas έγραψε:

Οπότε στον SQL Server Configuration Manager window > SQL Server 2005 Services > SQL Server instance name > Properties > Advanced tab > Startup Parameters ;-T2371 μετά την τελευταία παράμετρο;Σε ποιό instance στο SQL Server Agent ή στο SQL Server;Κώστας

user-gravatar

Στις 14 Jan 2014 @ 11:19 PM o/η Antonios Chatzipavlis έγραψε:

Το ορίζεις στο SQL Service

user-gravatar

Στις 31 Jan 2014 @ 1:50 PM o/η Καρακασίδης Γιώργος έγραψε:

Τι θα λέγατε για εκτέλεση της sp_updatestats ?Θα βοηθούσε καθόλου;

user-gravatar

Στις 31 Jan 2014 @ 10:30 PM o/η Antonios Chatzipavlis έγραψε:

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

user-gravatar

Στις 01 Feb 2014 @ 11:47 AM o/η Καρακασίδης Γιώργος έγραψε:

Λοιπόν...η sp_updatestatistics μάλλον είναι ξεχασμένη από την Microsoft υπάρχει ένα bug το οποίο δεν έχει ακόμη λυθεί. Αν την τρέξει κάποιος sql user ακόμη και με db_owner rights το μύνυμα που βγαίνει είναι:User does not have permission to perform this action.Η απάντηση της Microsoft....http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=436562Dear Customer, Thanks for reporting this issue to us. Once we have more results from our investigation, I will send you an update. Thanks,YavorSenior Program ManagerSQL EngineΤελικά η λύση που βρήκα (έτοιμη) είναι το εξής query το οποίο λειτουργεί θαυμάσια είναι:SET NOCOUNT ON GO --Determine if you want to execute the script with FULLSCAN DECLARE @WithFullscan BIT SELECT @WithFullscan = 0 ------------------- --Begin script ------------------- DECLARE @StartTime DATETIME SELECT @StartTime = GETDATE() IF OBJECT_ID('tempdb..#TablesToUpdateStats') IS NOT NULL BEGIN DROP TABLE #TablesToUpdateStats END DECLARE @NumTables VARCHAR(20) SELECT s.[Name] AS SchemaName , t.[name] AS TableName , SUM(p.rows) AS RowsInTable INTO #TablesToUpdateStats FROM sys.schemas s LEFT JOIN sys.tables t ON s.schema_id = t.schema_id LEFT JOIN sys.partitions p ON t.object_id = p.object_id LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index AND p.rows IS NOT NULL AND a.type = 1 -- row-data only , not LOB GROUP BY s.[Name] , t.[name] SELECT @NumTables = @@ROWCOUNT DECLARE updatestats CURSOR FOR SELECT ROW_NUMBER() OVER ( ORDER BY ttus.RowsInTable ) , ttus.SchemaName , ttus.TableName , ttus.RowsInTable FROM #TablesToUpdateStats AS ttus ORDER BY ttus.RowsInTable OPEN updatestats DECLARE @TableNumber VARCHAR(20) DECLARE @SchemaName NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @RowsInTable VARCHAR(20) DECLARE @Statement NVARCHAR(300) DECLARE @Status NVARCHAR(300) DECLARE @FullScanSQL VARCHAR(20) IF @WithFullscan = 1 BEGIN SELECT @FullScanSQL = ' WITH FULLSCAN' END ELSE BEGIN --If @WithFullscan<>1 then set @FullScanSQL to empty string SELECT @FullScanSQL = '' END FETCH NEXT FROM updatestats INTO @TableNumber, @SchemaName, @tablename, @RowsInTable WHILE ( @@FETCH_STATUS = 0 ) BEGIN SET @Statement = 'UPDATE STATISTICS [' + @SchemaName + '].[' + @tablename + ']' + @FullScanSQL SET @Status = 'Table ' + @TableNumber + ' of ' + @NumTables + ': Running ''' + @Statement + ''' (' + @RowsInTable + ' rows)' RAISERROR (@Status, 0, 1) WITH NOWAIT --RAISERROR used to immediately output status EXEC sp_executesql @Statement FETCH NEXT FROM updatestats INTO @TableNumber, @SchemaName, @tablename, @RowsInTable END CLOSE updatestats DEALLOCATE updatestats DROP TABLE #TablesToUpdateStats PRINT 'Total Elapsed Time: ' + CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE())) + ' minutes' GOΘα ήθελα την γνώμη σας.Ευχαριστώ

user-gravatar

Στις 11 Feb 2014 @ 8:03 PM o/η Antonios Chatzipavlis έγραψε:

Χωρίς να θέλω να δικαιολογήσω την MS σχετικά με την sp_updatestats, μου έρχονται στο μυαλό περιπτώσεις που αν επιτρεπόνταν αυτό θα δημιουργούσε πρόβλημα για παράδειγμα ένα hosting provider που έχει ένα SQL Server για Ν αριθμό πελατών του και που όλοι είναι db_owners στην βάση τους, τι θα γινόνταν αν ο καθένας ήθελα να ενημερώσει statistics όποτε του έρχονταν; Το ίδιο ακριβώς σενάριο με τρόμο το σκέφτομαι σε ένα μεγάλο περιβάλλον στο οποίο αν ήμουν DBA δεν θα ήθελα να μου συμβεί και θα ήταν από τα πρώτα που θα έκοβα. Το script που αναφέρεις θα δουλέψει αλλά έχει νόημα να κάνεις μαζικά σε όλους τους πίνακες update statistics;

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS