{"id":5509,"date":"2016-05-18T16:44:44","date_gmt":"2016-05-18T15:44:44","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=5509"},"modified":"2022-11-21T16:25:37","modified_gmt":"2022-11-21T15:25:37","slug":"vers-un-direct_path_read-sur-sql-server","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/","title":{"rendered":"Vers un DIRECT_PATH_READ sur SQL Server ?"},"content":{"rendered":"<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F5509&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F5509&#038;title=Vers%20un%20DIRECT_PATH_READ%20sur%20SQL%20Server%20%3F\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Vers%20un%20DIRECT_PATH_READ%20sur%20SQL%20Server%20%3F&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F5509\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a><p>A l&#8217;origine de ce post une constatation simple : le SQL Server que j&#8217;ai sous les yeux dispose de 36Gb de max server memory, pourtant il n&#8217;en consomme que 28Gb et fait du PAGEIOLATCH \u00e0 tours de bras.<\/p>\n<div>\u00a0Paul <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/buffer-pool-disfavoring\/\">l&#8217;avait signal\u00e9<\/a> il y a quelques ann\u00e9es avec la confirmation d&#8217;<a href=\"https:\/\/hal2020.com\/about\/\">Hal Berenson<\/a> en personne dans les commentaires, il existe un m\u00e9canisme appel\u00e9 <em>buffer pool disfavoring<\/em> au niveau du moteur de stockage de SQL Server qui permet de ne pas monter en m\u00e9moire les pages d&#8217;un scan qui d\u00e9passerait les 10% de la taille du buffer pool. Plus r\u00e9cemment cette ann\u00e9e,\u00a0<a href=\"https:\/\/blog.dbi-services.com\/author\/david-barbarin\/\">David Barbarin<\/a> (<a href=\"https:\/\/twitter.com\/mikedavem\">@mikedavem<\/a>) a\u00a0<a href=\"https:\/\/blog.dbi-services.com\/page-life-expectancy-value-has-just-dropped-but-dont-panic-yet\/\">r\u00e9dig\u00e9<\/a> tout un article consacr\u00e9 \u00e0 ce comportement.<\/div>\n<p>Un xevent <em>leaf_page_disfavored<\/em> est venu s&#8217;ajouter en SQL Server 2012 \u00e0 la panoplie existante pour tracer ce ph\u00e9nom\u00e8ne. Manque de bol, je suis en SQL Server 2008 R2 SP1.<\/p>\n<p>Donc aujourd&#8217;hui on va s&#8217;int\u00e9resser \u00e0 la pr\u00e9diction, c&#8217;est \u00e0 dire d\u00e9terminer quels sont les plans sur mon instance qui sont susceptibles de g\u00e9n\u00e9rer un BP disfavoring.<\/p>\n<p><strong>Etape1<\/strong> : Constituer la liste des objets (tables \/ indexes) dont la taille d\u00e9passe 10% du BP:<\/p>\n<pre name=\"code\" class=\"sql\">IF (object_id('tempdb..#temp') is not null)\r\n    DROP TABLE #temp ;\r\nCREATE TABLE #temp(\r\n    DatabaseName sysname NULL\r\n    ,TableName sysname NULL\r\n    ,type_desc varchar(24) NULL\r\n    ,idxname sysname NULL\r\n    ,indid bigint NULL\r\n    ,sizemb bigint NULL\r\n    ,bppct int NULL\r\n) ;\r\n\r\nDECLARE @dbname sysname, @sqlstmt varchar(max);\r\nDECLARE csr_db CURSOR READ_ONLY FOR SELECT name FROM sys.databases ;\r\nOPEN csr_db ;\r\nFETCH NEXT FROM csr_db INTO @dbname ;\r\nWHILE (@@FETCH_STATUS = 0)\r\nBEGIN\r\n    SET @sqlstmt = 'SELECT\r\n    '''+@dbname+'''\r\n    ,t.name\r\n    ,i.type_desc\r\n    ,i.name\r\n    ,i.index_id\r\n    ,SUM(a.used_pages)\/128\r\n    ,(SUM(a.used_pages)\/128) * 100 \/ (select value from sys.sysconfigures where comment = ''Maximum size of server memory (MB)'')\r\n    FROM ['+@dbname+'].sys.indexes AS i\r\n    JOIN ['+@dbname+'].sys.tables AS t on t.object_id = i.object_id\r\n    JOIN ['+@dbname+'].sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id\r\n    JOIN ['+@dbname+'].sys.allocation_units AS a ON a.container_id = p.partition_id\r\n    GROUP BY t.name,i.type_desc,i.index_id,i.name\r\n    HAVING SUM(a.used_pages)\/128 &gt;= (select value*10\/100 from sys.sysconfigures where comment = ''Maximum size of server memory (MB)'')\r\n    ORDER BY SUM(a.used_pages)\/128 desc' ;\r\n\r\n    INSERT INTO #temp execute (@sqlstmt);\r\n\r\n    FETCH NEXT FROM csr_db INTO @dbname ;\r\nEND ;\r\nCLOSE csr_db ;\r\nDEALLOCATE csr_db ;<\/pre>\n<p><strong>Etape 2:<\/strong> R\u00e9cup\u00e9rer les plans qui embarquent au moins un des trois op\u00e9rateurs Table scan , Index Scan , Clustered Index Scan:<\/p>\n<p>Et l\u00e0 j&#8217;avoue, je ne suis pas un XML shredder, donc gr\u00e2ce soit rendue \u00e0 <a href=\"https:\/\/sqlfascination.com\/2010\/03\/10\/locating-table-scans-within-the-query-cache\/\">Andrew Hogg<\/a> \u00e0 qui j&#8217;emprunte le code de r\u00e9cup\u00e9ration. J&#8217;ai juste bricol\u00e9 un peu pour ajouter la notion de co\u00fbt (<em>SubTreeCost<\/em>) et ne remonter que les 10 premiers plans.\u00a0<strong><\/strong>Attention, comme toute XML Query, co\u00fbt astronomique et temps d&#8217;ex\u00e9cution prohibitif,\u00a0donc \u00e0 ne pas lancer toutes les 5 minutes. Dans mon cas, 6&#8217;30&#8221; pour ins\u00e9rer 10 lignes et un co\u00fbt qui fait mal \u00e0 la t\u00eate:<\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/bigsubtreecost1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-5522\" title=\"bigsubtreecost\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/bigsubtreecost1.png\" alt=\"\" width=\"342\" height=\"250\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/bigsubtreecost1.png 342w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/bigsubtreecost1-300x219.png 300w\" sizes=\"auto, (max-width: 342px) 100vw, 342px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Le code:<\/p>\n<pre name=\"code\" class=\"sql\">IF (object_id('tempdb..#tempplans') is not null)\r\n\tDROP TABLE #tempplans ;\r\n\r\nCREATE TABLE #tempplans(\r\n\t\tDatabaseName sysname NULL\r\n\t\t,SchemaName sysname NULL\r\n\t\t,Object_name sysname NULL\r\n\t\t,PhysicalOperator varchar(64) NULL\r\n\t\t,LogicalOperator varchar(64) NULL\r\n\t\t,QueryText varchar(max) NULL\r\n\t\t,CacheObjectType varchar(64) NULL\r\n\t\t,ObjectType varchar(64) NULL\r\n\t\t,queryplan xml NULL\r\n\t\t,subtreecost bigint NULL ) ;\r\n\r\nWITH XMLNAMESPACES(DEFAULT N'https:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan'),\r\nCachedPlans\r\n(DatabaseName,SchemaName,ObjectName,PhysicalOperator, LogicalOperator, QueryText,QueryPlan, CacheObjectType, ObjectType, SubTreeCost)\r\nAS\r\n(\r\nSELECT\r\nCoalesce(RelOp.op.value(N'TableScan[1]\/Object[1]\/@Database', N'varchar(50)') , \r\nRelOp.op.value(N'OutputList[1]\/ColumnReference[1]\/@Database', N'varchar(50)') ,\r\nRelOp.op.value(N'IndexScan[1]\/Object[1]\/@Database', N'varchar(50)') ,\r\n'Unknown'\r\n)\r\nas DatabaseName,\r\nCoalesce(\r\nRelOp.op.value(N'TableScan[1]\/Object[1]\/@Schema', N'varchar(50)') ,\r\nRelOp.op.value(N'OutputList[1]\/ColumnReference[1]\/@Schema', N'varchar(50)') ,\r\nRelOp.op.value(N'IndexScan[1]\/Object[1]\/@Schema', N'varchar(50)') ,\r\n'Unknown'\r\n)\r\nas SchemaName,\r\nCoalesce(\r\nRelOp.op.value(N'TableScan[1]\/Object[1]\/@Table', N'varchar(50)') ,\r\nRelOp.op.value(N'OutputList[1]\/ColumnReference[1]\/@Table', N'varchar(50)') ,\r\nRelOp.op.value(N'IndexScan[1]\/Object[1]\/@Table', N'varchar(50)') ,\r\n'Unknown'\r\n)\r\nas ObjectName,\r\nRelOp.op.value(N'@PhysicalOp', N'varchar(50)') as PhysicalOperator,\r\nRelOp.op.value(N'@LogicalOp', N'varchar(50)') as LogicalOperator,\r\nst.text as QueryText,\r\nqp.query_plan as QueryPlan,\r\ncp.cacheobjtype as CacheObjectType,\r\ncp.objtype as ObjectType,\r\nISNULL(CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') as float),0) AS SubTreeCost\r\nFROM\r\nsys.dm_exec_cached_plans cp\r\nCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st\r\nCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp\r\nCROSS APPLY qp.query_plan.nodes(N'\/\/RelOp') RelOp (op)\r\nCROSS APPLY qp.query_plan.nodes ('\/ShowPlanXML\/BatchSequence\/Batch\/Statements\/StmtSimple') AS qn ( n )\r\n)\r\nINSERT INTO #tempplans\r\nSELECT top (10)\r\nCachedPlans.DatabaseName,SchemaName,ObjectName,PhysicalOperator\r\n, LogicalOperator, QueryText,CacheObjectType, ObjectType, queryplan, SubTreeCost\r\nFROM\r\nCachedPlans\r\nWHERE\r\nCacheObjectType = N'Compiled Plan'\r\nand\r\n(\r\nPhysicalOperator = 'Clustered Index Scan' or PhysicalOperator = 'Table Scan' or\r\nPhysicalOperator = 'Index Scan')\r\nORDER BY SubTreeCost desc ;<\/pre>\n<p>&nbsp;<\/p>\n<p><strong>Etape 3:<\/strong> La r\u00e9conciliation des deux sources de donn\u00e9es #temp et #tempplans:<\/p>\n<pre name=\"code\" class=\"sql\">select \r\n\tP.DatabaseName, P.Object_Name, P.PhysicalOperator\r\n\t, P.LogicalOperator, P.QueryText, P.CacheObjectType\r\n\t, P.ObjectType, P.queryplan, P.subtreecost\r\n\t, BT.sizemb, BT.bppct\r\nfrom #tempplans P\r\njoin #temp BT on BT.DatabaseName = replace(replace(P.DatabaseName,'[',''),']','') and BT.TableName = replace(replace(P.Object_Name,'[',''),']','') ;<\/pre>\n<p>Vous me pardonnerez la sale jointure mais on parle de 112 lignes en NLJ avec 10 lignes, donc bon. Egalement, autre d\u00e9faut je ne g\u00e8re pas les sch\u00e9mas. L&#8217;important est de r\u00e9cup\u00e9rer ce qui m&#8217;int\u00e9resse (nom de base + objet masqu\u00e9s):<\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/largeplansbpd.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-large wp-image-5537\" title=\"largeplansbpd\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/largeplansbpd-1024x130.png\" alt=\"\" width=\"1024\" height=\"130\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/largeplansbpd-1024x130.png 1024w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/largeplansbpd-300x38.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/largeplansbpd-768x98.png 768w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/largeplansbpd.png 1453w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Notez les co\u00fbts, la taille des objets et le pourcentage par rapport aux 36Gb.<\/p>\n<p><strong>Conclusion<\/strong> : et donc pourquoi la r\u00e9f\u00e9rence au direct_path_read dans le titre au fait ? Les oracliens auront sans doute compris.<\/p>\n<p>Aujourd&#8217;hui il n&#8217;existe pas de diff\u00e9renciation 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 \u00eatre un petit connect \u00e0 soumettre m\u00eame si bon, on sait bien que c&#8217;est juste pour la forme, sans parler de la faisabilit\u00e9, hein. Il est probable que le PAGEIOLATCH soit coll\u00e9 avant m\u00eame de savoir si la page sera <em>disfavored<\/em> ou non.<\/p>\n<p>N&#8217;emp\u00eache des fois au risque de me prendre des fl\u00eaches, il y a des trucs d&#8217;Oracle que j&#8217;aimerais bien voir dans SQL Server.<\/p>\n<p>A+<\/p>\n<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F5509&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F5509&#038;title=Vers%20un%20DIRECT_PATH_READ%20sur%20SQL%20Server%20%3F\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Vers%20un%20DIRECT_PATH_READ%20sur%20SQL%20Server%20%3F&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F5509\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a>","protected":false},"excerpt":{"rendered":"<p>A l&#8217;origine de ce post une constatation simple : le SQL Server que j&#8217;ai sous les yeux dispose de 36Gb de max server memory, pourtant il n&#8217;en consomme que 28Gb et fait du PAGEIOLATCH \u00e0 tours de bras. \u00a0Paul l&#8217;avait&hellip; <a href=\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\" class=\"more-link\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":5521,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-5509","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Vers un DIRECT_PATH_READ sur SQL Server ? - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Vers un DIRECT_PATH_READ sur SQL Server ? - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"A l&#8217;origine de ce post une constatation simple : le SQL Server que j&#8217;ai sous les yeux dispose de 36Gb de max server memory, pourtant il n&#8217;en consomme que 28Gb et fait du PAGEIOLATCH \u00e0 tours de bras. \u00a0Paul l&#8217;avait&hellip; Continuer la lecture &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-18T15:44:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-11-21T15:25:37+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/bigsubtreecost.png\" \/>\n\t<meta property=\"og:image:width\" content=\"342\" \/>\n\t<meta property=\"og:image:height\" content=\"250\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"David Baffaleuf\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"David Baffaleuf\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\"},\"author\":{\"name\":\"David Baffaleuf\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\"},\"headline\":\"Vers un DIRECT_PATH_READ sur SQL Server ?\",\"datePublished\":\"2016-05-18T15:44:44+00:00\",\"dateModified\":\"2022-11-21T15:25:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\"},\"wordCount\":521,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\",\"name\":\"Vers un DIRECT_PATH_READ sur SQL Server ? - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2016-05-18T15:44:44+00:00\",\"dateModified\":\"2022-11-21T15:25:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Vers un DIRECT_PATH_READ sur SQL Server ?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\",\"name\":\"David Baffaleuf\",\"sameAs\":[\"http:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Vers un DIRECT_PATH_READ sur SQL Server ? - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/","og_locale":"fr_FR","og_type":"article","og_title":"Vers un DIRECT_PATH_READ sur SQL Server ? - Capdata TECH BLOG","og_description":"A l&#8217;origine de ce post une constatation simple : le SQL Server que j&#8217;ai sous les yeux dispose de 36Gb de max server memory, pourtant il n&#8217;en consomme que 28Gb et fait du PAGEIOLATCH \u00e0 tours de bras. \u00a0Paul l&#8217;avait&hellip; Continuer la lecture &rarr;","og_url":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2016-05-18T15:44:44+00:00","article_modified_time":"2022-11-21T15:25:37+00:00","og_image":[{"width":342,"height":250,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/05\/bigsubtreecost.png","type":"image\/png"}],"author":"David Baffaleuf","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"David Baffaleuf","Dur\u00e9e de lecture estim\u00e9e":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/"},"author":{"name":"David Baffaleuf","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf"},"headline":"Vers un DIRECT_PATH_READ sur SQL Server ?","datePublished":"2016-05-18T15:44:44+00:00","dateModified":"2022-11-21T15:25:37+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/"},"wordCount":521,"commentCount":1,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"articleSection":["SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/","url":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/","name":"Vers un DIRECT_PATH_READ sur SQL Server ? - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2016-05-18T15:44:44+00:00","dateModified":"2022-11-21T15:25:37+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/vers-un-direct_path_read-sur-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Vers un DIRECT_PATH_READ sur SQL Server ?"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf","name":"David Baffaleuf","sameAs":["http:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/5509","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=5509"}],"version-history":[{"count":48,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/5509\/revisions"}],"predecessor-version":[{"id":9502,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/5509\/revisions\/9502"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/5521"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=5509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=5509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=5509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}