go backarticles

Articles of SQLschool.gr Team

Update Statistics and Trace Flag 2371

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*/


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.


Comments

user-gravatar

On 13 Jan 2014 @ 7:59 PM Kostas Backas wrote:

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

user-gravatar

On 14 Jan 2014 @ 11:49 AM Antonios Chatzipavlis wrote:

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

user-gravatar

On 14 Jan 2014 @ 12:56 PM Kostas Backas wrote:

Οπότε στον 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

On 14 Jan 2014 @ 11:19 PM Antonios Chatzipavlis wrote:

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

user-gravatar

On 31 Jan 2014 @ 1:50 PM Καρακασίδης Γιώργος wrote:

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

user-gravatar

On 31 Jan 2014 @ 10:30 PM Antonios Chatzipavlis wrote:

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

user-gravatar

On 01 Feb 2014 @ 11:47 AM Καρακασίδης Γιώργος wrote:

Λοιπόν...η 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

On 11 Feb 2014 @ 8:03 PM Antonios Chatzipavlis wrote:

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

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.