Vers un DIRECT_PATH_READ sur SQL Server ?

A l’origine de ce post une constatation simple : le SQL Server que j’ai sous les yeux dispose de 36Gb de max server memory, pourtant il n’en consomme que 28Gb et fait du PAGEIOLATCH à tours de bras.

Un xevent leaf_page_disfavored est venu s’ajouter en SQL Server 2012 à la panoplie existante pour tracer ce phénomène. Manque de bol, je suis en SQL Server 2008 R2 SP1.

Donc aujourd’hui on va s’intéresser à la prédiction, c’est à dire déterminer quels sont les plans sur mon instance qui sont susceptibles de générer un BP disfavoring.

Etape1 : Constituer la liste des objets (tables / indexes) dont la taille dépasse 10% du BP:

IF (object_id('tempdb..#temp') is not null)
    DROP TABLE #temp ;
CREATE TABLE #temp(
    DatabaseName sysname NULL
    ,TableName sysname NULL
    ,type_desc varchar(24) NULL
    ,idxname sysname NULL
    ,indid bigint NULL
    ,sizemb bigint NULL
    ,bppct int NULL
) ;

DECLARE @dbname sysname, @sqlstmt varchar(max);
DECLARE csr_db CURSOR READ_ONLY FOR SELECT name FROM sys.databases ;
OPEN csr_db ;
FETCH NEXT FROM csr_db INTO @dbname ;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sqlstmt = 'SELECT
    '''+@dbname+'''
    ,t.name
    ,i.type_desc
    ,i.name
    ,i.index_id
    ,SUM(a.used_pages)/128
    ,(SUM(a.used_pages)/128) * 100 / (select value from sys.sysconfigures where comment = ''Maximum size of server memory (MB)'')
    FROM ['+@dbname+'].sys.indexes AS i
    JOIN ['+@dbname+'].sys.tables AS t on t.object_id = i.object_id
    JOIN ['+@dbname+'].sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
    JOIN ['+@dbname+'].sys.allocation_units AS a ON a.container_id = p.partition_id
    GROUP BY t.name,i.type_desc,i.index_id,i.name
    HAVING SUM(a.used_pages)/128 >= (select value*10/100 from sys.sysconfigures where comment = ''Maximum size of server memory (MB)'')
    ORDER BY SUM(a.used_pages)/128 desc' ;

    INSERT INTO #temp execute (@sqlstmt);

    FETCH NEXT FROM csr_db INTO @dbname ;
END ;
CLOSE csr_db ;
DEALLOCATE csr_db ;

Etape 2: Récupérer les plans qui embarquent au moins un des trois opérateurs Table scan , Index Scan , Clustered Index Scan:

Et là j’avoue, je ne suis pas un XML shredder, donc grâce soit rendue à Andrew Hogg à qui j’emprunte le code de récupération. J’ai juste bricolé un peu pour ajouter la notion de coût (SubTreeCost) et ne remonter que les 10 premiers plans. Attention, comme toute XML Query, coût astronomique et temps d’exécution prohibitif, donc à ne pas lancer toutes les 5 minutes. Dans mon cas, 6’30” pour insérer 10 lignes et un coût qui fait mal à la tête:

Le code:

IF (object_id('tempdb..#tempplans') is not null)
	DROP TABLE #tempplans ;

CREATE TABLE #tempplans(
		DatabaseName sysname NULL
		,SchemaName sysname NULL
		,Object_name sysname NULL
		,PhysicalOperator varchar(64) NULL
		,LogicalOperator varchar(64) NULL
		,QueryText varchar(max) NULL
		,CacheObjectType varchar(64) NULL
		,ObjectType varchar(64) NULL
		,queryplan xml NULL
		,subtreecost bigint NULL ) ;

WITH XMLNAMESPACES(DEFAULT N'https://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(DatabaseName,SchemaName,ObjectName,PhysicalOperator, LogicalOperator, QueryText,QueryPlan, CacheObjectType, ObjectType, SubTreeCost)
AS
(
SELECT
Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Database', N'varchar(50)') , 
RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Database', N'varchar(50)') ,
RelOp.op.value(N'IndexScan[1]/Object[1]/@Database', N'varchar(50)') ,
'Unknown'
)
as DatabaseName,
Coalesce(
RelOp.op.value(N'TableScan[1]/Object[1]/@Schema', N'varchar(50)') ,
RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Schema', N'varchar(50)') ,
RelOp.op.value(N'IndexScan[1]/Object[1]/@Schema', N'varchar(50)') ,
'Unknown'
)
as SchemaName,
Coalesce(
RelOp.op.value(N'TableScan[1]/Object[1]/@Table', N'varchar(50)') ,
RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Table', N'varchar(50)') ,
RelOp.op.value(N'IndexScan[1]/Object[1]/@Table', N'varchar(50)') ,
'Unknown'
)
as ObjectName,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') as PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') as LogicalOperator,
st.text as QueryText,
qp.query_plan as QueryPlan,
cp.cacheobjtype as CacheObjectType,
cp.objtype as ObjectType,
ISNULL(CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') as float),0) AS SubTreeCost
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
CROSS APPLY qp.query_plan.nodes ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn ( n )
)
INSERT INTO #tempplans
SELECT top (10)
CachedPlans.DatabaseName,SchemaName,ObjectName,PhysicalOperator
, LogicalOperator, QueryText,CacheObjectType, ObjectType, queryplan, SubTreeCost
FROM
CachedPlans
WHERE
CacheObjectType = N'Compiled Plan'
and
(
PhysicalOperator = 'Clustered Index Scan' or PhysicalOperator = 'Table Scan' or
PhysicalOperator = 'Index Scan')
ORDER BY SubTreeCost desc ;

Etape 3: La réconciliation des deux sources de données #temp et #tempplans:

select 
	P.DatabaseName, P.Object_Name, P.PhysicalOperator
	, P.LogicalOperator, P.QueryText, P.CacheObjectType
	, P.ObjectType, P.queryplan, P.subtreecost
	, BT.sizemb, BT.bppct
from #tempplans P
join #temp BT on BT.DatabaseName = replace(replace(P.DatabaseName,'[',''),']','') and BT.TableName = replace(replace(P.Object_Name,'[',''),']','') ;

Vous me pardonnerez la sale jointure mais on parle de 112 lignes en NLJ avec 10 lignes, donc bon. Egalement, autre défaut je ne gère pas les schémas. L’important est de récupérer ce qui m’intéresse (nom de base + objet masqués):

Notez les coûts, la taille des objets et le pourcentage par rapport aux 36Gb.

Conclusion : et donc pourquoi la référence au direct_path_read dans le titre au fait ? Les oracliens auront sans doute compris.

Aujourd’hui il n’existe pas de différenciation des attentes entre une page lue depuis le disque vers le BP (PAGEIOLATCH) et une page lue depuis le disque mais non mise en cache (ce sera PAGEIOLATCH aussi). Il y a peut être un petit connect à soumettre même si bon, on sait bien que c’est juste pour la forme, sans parler de la faisabilité, hein. Il est probable que le PAGEIOLATCH soit collé avant même de savoir si la page sera disfavored ou non.

N’empêche des fois au risque de me prendre des flêches, il y a des trucs d’Oracle que j’aimerais bien voir dans SQL Server.

A+

Continuez votre lecture sur le blog :

• DMV et problème de tri (David Baffaleuf) [SQL Server]
• Alter table rebuild (Benjamin VESAN) [SQL Server]
• Sessions consommatrices dans tempdb (David Baffaleuf) [SQL ServerVintage]
• Regénérer le DDL des indexes FULL TEXT (David Baffaleuf) [SQL Server]
• Fragmentation sur des tables stockées en S-GAM (David Baffaleuf) [SQL Server]