sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Do you have "Optimize for Ad Hoc Workloads" on SQL Server 2008 & R2 instances?

Antonios Chatzipavlis
Tuesday 30 August 2011

Πριν από ένα μήνα είχα αναρτήσει μια δημοσκόπηση στο site του SqlSchool.gr με την εξής ερώτηση:

Έχετε ενεργοποιήσει το "Optimize for Ad Hoc Workloads" στα instances του SQL Server 2008 & R2 που έχετε;

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

Τα αποτελέσματα μετά από ένα μήνα που ήταν ενεργή η συγκεκριμένη δημοσκόπηση ήταν τα παρακάτω

Ναί 23,08%
Όχι 7,69%
Εξαρτάται 0%
Δεν ξέρω τι είναι αυτό 69,23%

Η έκπληξη ήταν αρκετά μεγάλη βλέποντας το αποτέλεσμα και έτσι αποφάσισα να δω και τον αριθμό συμμετοχών στην δημοσκόπηση αυτή ώστε να εξασφαλίσω την εγκυρότητα του δείγματος. Ο αριθμός αυτό δεν ήταν καθόλου μικρός (173 μοναδικές συμμετοχές). Βλέποντας τον αριθμό και τα αποτελέσματα μου διαπίστωσα ότι καλά έκανα και μέτρησα το συγκεκριμένο θέμα.

Ας πάρουμε τα πράγματα όμως με την σειρά ώστε στο τέλος να έχουμε βγάλει μια άκρη σχετικά με το τι τελικά είναι το Optimize for Ad Hoc Workloads και αν πρέπει ή όχι να το έχουμε ενεργοποιημένο .

Τι είναι το Optimize for Ad Hoc Workloads;

Σήμερα όλο και περισσότερες εφαρμογές χρησιμοποιούν ORMs για να διαβάσουν δεδομένα από τις databases πχ Entity Framework , Linq to SQL, NHibernate κλπ. Είναι τα αγαπημένα εργαλεία των devs που θέλουν να γράφουν σε αυτό που ξέρουν πχ C# και object oriented programming. Γενικά είναι αγαπημένα εργαλεία σε αυτούς που θέλουν να βλέπουν την βάση σαν ένα κουβά (αυτό είναι κάτι το οποίο με κάνει να εξοργίζομαι). Μέσα από τα εργαλεία αυτά ο κάθε dev χωρίς να το βασανίσει ιδιαίτερα κάνει ad hoc queries χρησιμοποιώντας τα εργαλεία αυτά. Για κάθε ένα από αυτά ο SQL Server αποθηκεύει το execution plan του στην plan cache που είναι μέρος της buffer cache που είναι και στην ουσία αυτή που καταναλώνει memory στην μηχανή που είναι εγκατεστημένος ο SQL Server. Πολλά από αυτά το πιθανότερο είναι να μην χρησιμοποιηθούνε ποτέ ξανά, αυτό σημαίνει ότι μνήμη που έχει δεσμευτεί για τα execution plans τους παραμένει κατειλημμένη. Βέβαια το ίδιο ισχύει και για εφαρμογές που δεν είναι γραμμένες χρησιμοποιώντας τα εργαλεία αυτά αλλά είναι κακογραμμένες ή ο dev δεν έχει ανακαλύψει ακόμα την χρήση των stored procedure. Σημασία όμως έχει ότι είτε έτσι είτε αλλιώς έχω αυτό που ονομάζεται plan cache bloat όπως έχει καθιερωθεί να λέγεται τα φαινόμενο αυτό στην κοινότητα των SQL Server experts.

Plan Cache bloat means that much of your plan cache is wasted by execution plans that will never be used again.

Το φαινόμενο αυτό μπορεί να εξαλειφθεί όταν ενεργοποιήσω το option optimize for ad hoc workloads, το οποίο by default είναι off. Με ενεργοποιημένο το option αυτό ο SQL Server αντί να αποθηκεύσει το full execution plan του ad hoc query αποθηκεύει ένα μικρό compiled plan stub όπως λέγεται κατά την πρώτη του εκτέλεση. Το full execution plan θα αποθηκευτεί όταν θα υπάρχει και επόμενη εκτέλεση του ίδιου ad hoc query.

Πώς βρίσκω αν πρέπει να ενεργοποιήσω το Optimize for Ad Hoc Workloads;

Για να εντοπίσω, σαν DBA, αν έχω το φαινόμενο του plan cache bloat o τρόπος είναι ένας και αυτός δεν είναι άλλος από το να χρησιμοποιήσω την DMV sys.dm_exec_cached_plans.

Για καλύτερα όμως αποτελέσματα καλό είναι να εκτελέσετε το παρακάτω script στο οποίο γίνεται με την χρήση της sys.dm_exec_cached_plans

SQL Script

declare @TotalPlanCacheSizeInMBs decimal(18,2), @TotalSingleUsedPlansInMBs decimal(18,2) 
select  @TotalPlanCacheSizeInMBs = (sum(cast(size_in_bytes as decimal(18,2)))/1024)/1024 
        , @TotalSingleUsedPlansInMBs = (sum(cast((case when usecounts = 1 and objtype in ('Adhoc','Prepared') then size_in_bytes else 0 end) as decimal(12,2)))/1024)/1024 
from sys.dm_exec_cached_plans 
select  @TotalPlanCacheSizeInMBs AS [Total Plan Cache Size In MBs] 
        , @TotalSingleUsedPlansInMBs AS [Total Single Used Plans In MBs] 
        , cast((@TotalSingleUsedPlansInMBs * 100 ) / @TotalPlanCacheSizeInMBs as decimal(18,2)) AS [% of Wasted space from single used plans] 
go 

Αν το % of Wasted space from single used plans είναι πάνω από το 20% (αν και το ποσοστό αυτό μπορεί να αλλάξει ανάλογα με το περιβάλλον) τότε καλό είναι να ενεργοποιηθεί το option αν και βέβαια αυτή είναι μια εκτίμηση γίνεται σε βάση την σχέση του χαμένου χώρου και του συνολικό μέγεθος της plan cache.

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

SQL Script

DECLARE @ConfiguredMemory        decimal(19,5)
        , @PhysicalMemory        decimal(19,5)
        , @InUseMemory            decimal(19,5)
select @ConfiguredMemory = cast(value_in_use as decimal(19,5))  from sys.configurations
where configuration_id=1544; -- max server memory (MB)

SELECT @PhysicalMemory = (total_physical_memory_kb/1024) 
FROM sys.dm_os_sys_memory;

SELECT @InUseMemory = ( physical_memory_in_use_kb/1024 ) 
FROM sys.dm_os_process_memory;

declare @TotalSingleUsedPlansInMBs decimal(19,5)
select  @TotalSingleUsedPlansInMBs = (sum(cast((case 
                                                when usecounts = 1 and objtype in ('Adhoc','Prepared') 
                                                    then size_in_bytes else 0 end) as decimal(12,2)))/1024)/1024 
from sys.dm_exec_cached_plans;

select  @ConfiguredMemory AS [SQL Configured Memory (MB)]
        , @PhysicalMemory AS [Physical Memory (MB)]
        , @InUseMemory AS [Used Memory by SQL Server (MB) ]
        , cast((@InUseMemory * 100 ) / @ConfiguredMemory as decimal(18,5)) AS [% of SQL Server used memory on Configured Memory]
        , @TotalSingleUsedPlansInMBs AS [Total Single Used Plans In MBs]
        , cast((@TotalSingleUsedPlansInMBs * 100 ) / @ConfiguredMemory as decimal(18,5)) AS [% of Wasted space from single used plans on Configured Memory]
        , cast((@TotalSingleUsedPlansInMBs * 100 ) / @InUseMemory as decimal(18,5)) AS [% of Wasted space from single used plans on Used Memory]
go 

Εάν έχουμε [% of SQL Server used memory on Configured Memory] πάνω από το 50% και δούμε το [% of Wasted space from single used plans on Used Memory] να είναι πάνω από το 10-15% τότε είναι ένα καλό σημάδι να ενεργοποιήσουμε option.

Τέλος υπάρχει ακόμα και περίπτωση που αν θέλουμε μπορούμε να κοιτάξουμε σε σχέση όμως αυτή την φορά με την Plan Cache Pressure Limit . Για να το γίνει αυτό θα πρέπει να γνωρίζουμε πως δουλεύει το plan caching στον SQL Server. Για αυτό υπάρχει ένα εξαιρετικό white paper από τον Greg Low με τίτλο Plan Caching in SQL Server 2008 από το οποίο ιδιαίτερη μνεία θα πρέπει να δοθεί στην παράγραφο με τίτλο Memory Allocated To Plan Caching την οποία και μεταφέρω αυτούσια για την ευκολία στο διάβασμα του post.

Most memory used by SQL Server is allocated to the Buffer Pool, which is used to store data pages. SQL Server steals a proportion of this memory for use in caching query plans. The overall amount of memory available to SQL Server depends upon the amount of memory installed on the server, the architecture of the server, the version and edition of SQL Server and the amount of memory pressure being experienced by SQL Server. This pressure can be internal (SQL Server resources need memory) or external (operating system needs memory). SQL Server is designed to respond to memory pressure when necessary.

Four types of object are stored in the Plan Cache: Object Plans, SQL Plans, Bound Trees and Extended Stored Procedures. SQL Server decides the appropriate allocation of memory to the Plan Cache from the Buffer Pool. The algorithm used for this has been improved in successive service packs since SQL Server 2005 was introduced.

SQL Server Version

Cache Pressure Limit

SQL Server 2008 and SQL Server 2005 SP2

75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB

SQL Server 2005 RTM and SQL Server 2005 SP1

75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB

SQL Server 2000

SQL Server 2000 4GB upper cap on the plan cache

Table 1: Plan cache memory allocation by SQL Server version

While 32-bit systems may use AWE (Address Window Extensions) memory to extend the available memory beyond the 4G virtual address space limit of the 32-bit architecture, this additional memory can only be used for data pages in the Buffer Pool, not by pages in the Plan Cache. It is not considered visible memory. No such limitation applies to 64-bit systems.

Για την ευκολία σας θα αναφέρω το παρακάτω από το βιβλίο της Kalen Delaney SQL Server Internals όπου ορίζονται με εξαιρετικό τρόπο τα παρακάτω:

When discussing memory pressure, we refer to the term visible memory—that is, the directly addressable physical memory available to the SQL Server buffer pool. On a 32-bit SQL Server instance, the maximum value for visible memory is either 2GB or 3GB, depending on whether you have the /3GB flag set in your boot.ini file. Memory with addresses greater than 2GB or 3GB is available only indirectly, through AWE-mapped memory. On a 64-bit SQL Server instance, “visible” memory has no special meaning, as all the memory is directly addressable. If I refer to visible memory greater than 3GB, keep in mind that this is possible only on a 64-bit SQL Server system. The term target memory means the maximum amount of memory that can be committed to the SQL Server process. Target memory refers to the physical memory committed to the buffer pool and is the lesser of the values you’ve configured for “max server memory” and the total amount of physical memory available to the OS. Thus, visible target memory is the visible portion of the target memory. Query plans can be stored only in the non–AWE-mapped memory, which is why the concept of visible memory is important.

Από το ίδιο βιβλίο θα πάρω και ένα παράδειγμα

Assume you’re on SQL Server 2008 on a 64-bit SQL Server instance with 28GB of target memory. Per the formula in Table 1, the plan-cache pressure limit would be 75 percent of 4GB plus 10 percent of the target memory over 4GB (or 10 percent of 24GB)—that is, (.75 ? 4GB) + (.10 ? 24GB) = 3GB + 2.4GB = 5.4GB.

Αφού εφαρμόσουμε τον παραπάνω αλγόριθμο βρίσκουμε το plan cache pressure limit και το συσχετίζουμε με τον αποτελέσματα από τον πρώτο τρόπο. Αν το ποσοστό που θα βρούμε είναι μεγάλο τότε όχι μόνο θα πρέπει να ενεργοποιήσουμε το option αλλά να κάνουμε καθαρισμό της plan cache. Βέβαια αυτό θα πρέπει να γίνει και για τι άλλες περιπτώσεις που έχω αναφέρει παραπάνω.

Πώς καθαρίζω την Plan Cache

Ο εύκολος τρόπος για να γίνει αυτό είναι να χρησιμοποιήσω την

SQL Script

DBCC FREESYSTEMCACHE('SQL Plans') 

Το θέμα όμως με αυτή την σύνταξη είναι ότι καθαρίζει όλη την plan cache. Αυτό σημαίνει ότι θα φύγουν και αυτά που χρησιμοποιούνται πολλές φορές. Αν θέλω να καθαρίζω αυτά που έχουν χρησιμοποιηθεί μόνο μια φορά θα πρέπει να δράσω κάπως διαφορετικά όπως παρακάτω

SQL Script

select plan_handle from  sys.dm_exec_cached_plans
where usecounts=1
go

DECLARE clearplancache CURSOR
READ_ONLY
FOR select plan_handle from  sys.dm_exec_cached_plans

DECLARE @plan_handle varbinary(8000)
OPEN clearplancache

FETCH NEXT FROM clearplancache INTO @plan_handle
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        DBCC FREEPROCCACHE (@plan_handle)
    END
    FETCH NEXT FROM clearplancache INTO @plan_handle
END

CLOSE clearplancache
DEALLOCATE clearplancache
GO
select plan_handle from  sys.dm_exec_cached_plans
where usecounts=1
go

Πώς ενεργοποιώ το Optimize for Ad Hoc Workloads;

Για να ενεργοποιήσω το συγκεκριμένο option μπορώ να το κάνω με το εξής script

SQL Script

sp_configure 'show advanced options',1 
go 

reconfigure 
go 

sp_configure 'optimize for ad hoc workloads',1 
go 

reconfigure 
go

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.

Tip

What's New in SQL Server 2022 - Episodes

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-2024 All rights reserved

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