sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Update Statistics and Trace Flag 2371

Antonios Chatzipavlis
Monday 13 January 2014

Σαν 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*/

Comments

13 Jan 2014 @ 7:59 PM

user-gravatar

Kostas Backas

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

14 Jan 2014 @ 11:49 AM

user-gravatar

Antonios Chatzipavlis

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

14 Jan 2014 @ 12:56 PM

user-gravatar

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;Κώστας

14 Jan 2014 @ 11:19 PM

user-gravatar

Antonios Chatzipavlis

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

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

31 Jan 2014 @ 10:30 PM

user-gravatar

Antonios Chatzipavlis

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

01 Feb 2014 @ 11:47 AM

user-gravatar

Καρακασίδης Γιώργος

Λοιπόν...η 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Θα ήθελα την γνώμη σας.Ευχαριστώ

11 Feb 2014 @ 8:03 PM

user-gravatar

Antonios Chatzipavlis

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

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

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