Optimize for ad hoc workloads för Azure SQL Database

Nu finns ”optimize for ad hoc workloads” även för Azure SQL Database. Konfigurationen som har funnits sedan SQL Server 2008 men då på servernivå.
Om du har dynamiska SQL-anrop riskerar cachen att bli full av planer som inte kan återanvändas eftersom anropen inte är exakt lika. Då är den här parametern i regel positiv för prestandan. Den gör att optimizern kompilerar och spar en reducerad plan i cachen i stället för en full. När sedan ett nytt ad hoc anrop kommer och den reducerade planen kan användas skapas en riktig exekveringsplan. Man vinner alltså dels utrymme och dels att det finns en plan i cachen som har större chans att kunna användas i stället för att kompilera om från scratch varje gång.

För att se om konfiguration är satt:

SELECT configuration_id, name, value
FROM sys.database_scoped_configurations
WHERE name = N'OPTIMIZE_FOR_AD_HOC_WORKLOADS';

För att sätta konfiguration:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

För att stänga av konfiguration:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = OFF;

OBS: konfiguration gäller bara framtida kompileringar. Om du vill att den ska gälla omedelbara måste SQL Servern startas om eller köra rensning av procedurcachen.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

För att se hur många och hur mycket utrymme som Adhoc Compiled Plan Stub tar i cachen:

SELECT objtype, cacheobjtype,
AVG(usecounts) AS Avg_UseCount,
SUM(refcounts) AS AllRefObjects,
SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;