{"id":563,"date":"2011-06-01T10:13:00","date_gmt":"2011-06-01T10:13:00","guid":{"rendered":"\/blogs\/paul\/post\/Performance-issues-from-wasted-buffer-pool-memory.aspx"},"modified":"2017-04-13T09:50:51","modified_gmt":"2017-04-13T16:50:51","slug":"performance-issues-from-wasted-buffer-pool-memory","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/","title":{"rendered":"Performance issues from wasted buffer pool memory"},"content":{"rendered":"<p>(Check out my Pluralsight online training course: <em><a href=\"http:\/\/www.pluralsight.com\/courses\/sqlserver-index-fragmentation-internals-analysis-solutions\" target=\"_blank\">SQL Server: Index\u00a0Fragmentation Internals, Analysis, and Solutions<\/a>.)<\/em><\/p>\n<p>Back in April I kicked off a <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/survey-how-much-server-memory-is-being-wasted-code-to-run\/\" target=\"_blank\">survey<\/a> where I asked you all to send me some information about your buffer pools &#8211; how much memory is being used for data file pages and how much of that memory is storing empty space. I got back data from 1394 servers around the world &#8211; thanks!<\/p>\n<p>The reason I&#8217;m interested in this, and you should be too, is that memory is one of the most important resources that SQL Server uses. If you don&#8217;t have enough memory, your workload will suffer because:<\/p>\n<ul>\n<li>You&#8217;ll be driving more read I\/Os because more of the workload can&#8217;t fit in the buffer pool.<\/li>\n<li>You&#8217;ll be driving more write I\/Os because the lazywriter will have to be tossing dirty pages from the buffer pool.<\/li>\n<li>You may encounter <em>RESOURCE_SEMAPHORE<\/em> waits because queries can&#8217;t get the query execution memory grants that they need.<\/li>\n<li>You may cause excessive plan recompilations if the plan cache is too constrained.<\/li>\n<\/ul>\n<p>And a bunch of other things.<\/p>\n<p>One of the memory problems that Kimberly discussed in depth last year (and teaches in depth in our <a href=\"https:\/\/www.sqlskills.com\/t_immersionperformancetuning.asp\" target=\"_blank\">Performance Tuning classes<\/a>)\u00a0is single-use plan cache bloat &#8211; where a large proportion of the plan cache is filled with single-use plans that don&#8217;t ever get used again. You can read about it in the three blog posts in her\u00a0<a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/category\/plan-cache\/\" target=\"_blank\">Plan Cache category<\/a>, along with how to identify plan cache bloat and what you can do about it.<\/p>\n<p>This post is about the memory the buffer pool is using to store data file pages, and whether good use is being made from it.<\/p>\n<h2>Tracking data density<\/h2>\n<p>The <em>sys.dm_os_buffer_descriptors<\/em> DMV gives the information stored by the buffer pool for each data file page in memory (called a <em>BUF<\/em> structure in the code). One of the things that this structure keeps track of is the <em>free_space_in_bytes<\/em> for each page. This metric is updated in real-time as changes are made to the page in memory (you can easily prove this for yourself) and so is a reliable view of the\u00a0data density of the used portion of the buffer pool.<\/p>\n<p>Data density? Think of this as how packed full or data, index, or LOB rows a data file page is. The more free space on the page, the lower the data density.<\/p>\n<p>Low data density pages are caused by:<\/p>\n<ul>\n<li>Very wide data rows (e.g. a table with a 5000-byte fixed-size row will only ever fit one row per page, wasting roughly 3000 bytes per page).<\/li>\n<li>Page splits, from random inserts into full pages or updates to rows on full pages. These kind of page splits result in logical fragmentation that affects range scan performance, low data density in data\/index pages, and increased transaction log overhead (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-expensive-are-page-splits-in-terms-of-transaction-log\/\" target=\"_blank\">How expensive are page splits in terms of transaction log?<\/a>).<\/li>\n<li>Row deletions where the space freed up by the deleted row will not be reused because of the insert pattern into the table\/index.<\/li>\n<\/ul>\n<p>Low data density pages can be detrimental to SQL Server performance, because the lower the density of records on the pages in a table:<\/p>\n<ul>\n<li>The higher the amount of disk space necessary to store the data (and back it up).<\/li>\n<li>The more I\/Os are needed to read the data into memory.<\/li>\n<li>The higher the amount of buffer pool memory needed to store the extra pages in the buffer pool.<\/li>\n<\/ul>\n<h2>Survey results<\/h2>\n<p>From the survey results I took all the SQL Servers that were using at least\u00a0one GB of buffer pool memory for data file page storage (900 servers) and plotted that amount of memory against the percentage of that memory that was storing free space in the data file pages.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg\" alt=\"\" \/><\/p>\n<p>Wow! That&#8217;s a lot of servers with a lot of buffer pool memory storing nothing useful.<\/p>\n<h2>Low data-density solutions<\/h2>\n<p>So what can you do about it? There are a number of solutions to low page density including:<\/p>\n<ul>\n<li>Change the table schema (e.g. vertical partitioning, using smaller data types).<\/li>\n<li>Change the index key columns (usually only applicable to clustered indexes &#8211; e.g. changing the leading cluster key from a random value like a non-sequential GUID to a sequential GUID or identity column).&gt;<\/li>\n<li>Use index <em>FILLFACTOR<\/em> to reduce page splits, and&#8230;<\/li>\n<li>Periodically rebuild problem indexes.<\/li>\n<li>Consider enabling data compression on some tables and indexes.<\/li>\n<\/ul>\n<p>From the graph above, bear in mind that some of the &#8216;wasted&#8217; space on these servers could be from proper index management where data and index pages have a low <em>FILLFACTOR<\/em> set to alleviate page splits. But I suspect that only accounts for a small portion of what we&#8217;re seeing in this data.<\/p>\n<p>The purpose of my survey and this post is not to explain how to make all the changes to reduce the amount of free space being stored in memory, but to educate you that this is a problem. Very often <em>PAGEIOLATCH<\/em> waits are prevalent on systems because more I\/O than necessary is being driven to the I\/O subsystem because of things like bad plans causing table scans or low data density. If you can figure out that it&#8217;s not an I\/O subsystem problem, then you as the DBA can do something about it.<\/p>\n<h2>Helpful code to run<\/h2>\n<p>Below is a script to analyze the buffer pool and break down by database the amount of space being taken up in the buffer pool and how much of that space is empty space. For systems with a 100s of GB of memory in use, this query may take a while to run:<\/p>\n<pre class=\"brush: sql; gutter: true; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT\r\n    (CASE WHEN (&#x5B;database_id] = 32767)\r\n        THEN N'Resource Database'\r\n        ELSE DB_NAME (&#x5B;database_id]) END) AS &#x5B;DatabaseName],\r\n    COUNT (*) * 8 \/ 1024 AS &#x5B;MBUsed],\r\n    SUM (CAST (&#x5B;free_space_in_bytes] AS BIGINT)) \/ (1024 * 1024) AS &#x5B;MBEmpty]\r\nFROM sys.dm_os_buffer_descriptors\r\nGROUP BY &#x5B;database_id];\r\nGO\r\n<\/pre>\n<p>And here&#8217;s some sample output from a client system (made anonymous,\u00a0of course):<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDatabaseName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MBUsed\u00a0\u00a0 MBEmpty\r\n------------------- -------- ---------\r\nResource Database\u00a0\u00a0 51\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11\r\nProdDB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 71287\u00a0\u00a0\u00a0 9779\r\nmaster\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nmsdb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 481\u00a0\u00a0\u00a0\u00a0\u00a0 72\r\nProdDB2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 106\u00a0\u00a0\u00a0\u00a0\u00a0 17\r\nmodel\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\ntempdb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2226\u00a0\u00a0\u00a0\u00a0 140\r\n<\/pre>\n<p>Below is a script that will break things down by table and index across all databases that are using space in the buffer pool. I&#8217;m filtering out system objects plus indexes where the amount of space used in the buffer pool is less than 100MB. You can use this to identify tables and indexes that need some work on them to allow your buffer pool memory to be used more effectively by SQL Server and increase your workload performance.<\/p>\n<pre class=\"brush: sql; gutter: true; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nEXEC sp_MSforeachdb\r\n    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME (&#x5B;database_id]) AS &#x5B;name]\r\n    FROM sys.dm_os_buffer_descriptors) AS names WHERE &#x5B;name] = ''?'')\r\nBEGIN\r\nUSE &#x5B;?]\r\nSELECT\r\n    ''?'' AS &#x5B;Database],\r\n    OBJECT_NAME (p.&#x5B;object_id]) AS &#x5B;Object],\r\n    p.&#x5B;index_id],\r\n    i.&#x5B;name] AS &#x5B;Index],\r\n    i.&#x5B;type_desc] AS &#x5B;Type],\r\n    --au.&#x5B;type_desc] AS &#x5B;AUType],\r\n    --DPCount AS &#x5B;DirtyPageCount],\r\n    --CPCount AS &#x5B;CleanPageCount],\r\n    --DPCount * 8 \/ 1024 AS &#x5B;DirtyPageMB],\r\n    --CPCount * 8 \/ 1024 AS &#x5B;CleanPageMB],\r\n    (DPCount + CPCount) * 8 \/ 1024 AS &#x5B;TotalMB],\r\n    --DPFreeSpace \/ 1024 \/ 1024 AS &#x5B;DirtyPageFreeSpace],\r\n    --CPFreeSpace \/ 1024 \/ 1024 AS &#x5B;CleanPageFreeSpace],\r\n    (&#x5B;DPFreeSpace] + &#x5B;CPFreeSpace]) \/ 1024 \/ 1024 AS &#x5B;FreeSpaceMB],\r\n    CAST (ROUND (100.0 * ((&#x5B;DPFreeSpace] + &#x5B;CPFreeSpace]) \/ 1024) \/ ((&#x5B;DPCount] + &#x5B;CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS &#x5B;FreeSpacePC]\r\nFROM\r\n    (SELECT\r\n        allocation_unit_id,\r\n        SUM (CASE WHEN (&#x5B;is_modified] = 1)\r\n            THEN 1 ELSE 0 END) AS &#x5B;DPCount],\r\n        SUM (CASE WHEN (&#x5B;is_modified] = 1)\r\n            THEN 0 ELSE 1 END) AS &#x5B;CPCount],\r\n        SUM (CASE WHEN (&#x5B;is_modified] = 1)\r\n            THEN CAST (&#x5B;free_space_in_bytes] AS BIGINT) ELSE 0 END) AS &#x5B;DPFreeSpace],\r\n        SUM (CASE WHEN (&#x5B;is_modified] = 1)\r\n            THEN 0 ELSE CAST (&#x5B;free_space_in_bytes] AS BIGINT) END) AS &#x5B;CPFreeSpace]\r\n    FROM sys.dm_os_buffer_descriptors\r\n    WHERE &#x5B;database_id] = DB_ID (''?'')\r\n    GROUP BY &#x5B;allocation_unit_id]) AS buffers\r\nINNER JOIN sys.allocation_units AS au\r\n    ON au.&#x5B;allocation_unit_id] = buffers.&#x5B;allocation_unit_id]\r\nINNER JOIN sys.partitions AS p\r\n    ON au.&#x5B;container_id] = p.&#x5B;partition_id]\r\nINNER JOIN sys.indexes AS i\r\n    ON i.&#x5B;index_id] = p.&#x5B;index_id] AND p.&#x5B;object_id] = i.&#x5B;object_id]\r\nWHERE p.&#x5B;object_id] &gt; 100 AND (&#x5B;DPCount] + &#x5B;CPCount]) &gt; 12800 -- Taking up more than 100MB\r\nORDER BY &#x5B;FreeSpacePC] DESC;\r\nEND';\r\n<\/pre>\n<p>And here&#8217;s some sample output from the same client system with the more comprehensive script:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDatabase Object index_id Index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TotalMB FreeSpaceMB FreeSpacePC\r\n-------- ------ -------- ------------ ------------ ------- ----------- -----------\r\nProdDB\u00a0\u00a0 TableG\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableG_IX_1\u00a0 CLUSTERED\u00a0\u00a0\u00a0 531\u00a0\u00a0\u00a0\u00a0 130\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 24.5\r\nProdDB\u00a0\u00a0 TableI\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableI_IX_1\u00a0 CLUSTERED\u00a0\u00a0\u00a0 217\u00a0\u00a0\u00a0\u00a0 48\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22.2\r\nProdDB\u00a0\u00a0 TableG\u00a02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableG_IX_2\u00a0 NONCLUSTERED 127\u00a0\u00a0\u00a0\u00a0 27\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 21.8\r\nProdDB\u00a0\u00a0 TableC\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableC_IX_1\u00a0 CLUSTERED\u00a0\u00a0\u00a0 224\u00a0\u00a0\u00a0\u00a0 47\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 21.4\r\nProdDB\u00a0\u00a0 TableD\u00a03\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableD_IX_3\u00a0 NONCLUSTERED 1932\u00a0\u00a0\u00a0 393\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20.4\r\nProdDB\u00a0\u00a0 TableH\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableH_IX_1\u00a0 CLUSTERED\u00a0\u00a0\u00a0 162\u00a0\u00a0\u00a0\u00a0 33\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20.4\r\nProdDB\u00a0\u00a0 TableF\u00a05\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableF_IX_5\u00a0 NONCLUSTERED 3128\u00a0\u00a0\u00a0 616\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19.7\r\nProdDB\u00a0\u00a0 TableG\u00a09\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableG_IX_9\u00a0 NONCLUSTERED 149\u00a0\u00a0\u00a0\u00a0 28\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19.1\r\nProdDB\u00a0\u00a0 TableO\u00a010\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableO_IX_10 NONCLUSTERED 1003\u00a0\u00a0\u00a0 190\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19\r\nProdDB\u00a0\u00a0 TableF\u00a06\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TableF_IX_6\u00a0 NONCLUSTERED 3677\u00a0\u00a0\u00a0 692\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.8\r\n.\r\n.\r\n<\/pre>\n<p>This is cool because it&#8217;s a lot less intrusive way to figure out which tables and indexes have data density issues than running <em>sys.dm_db_index_physical_stats<\/em> (see <a style=\"line-height: 23px;\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-sys-dm_db_index_physical_stats\/\" target=\"_blank\">this post<\/a> for an in depth view of this DMV). You can mess around with the script to make it create a table to hold all the data for all databases and slice+dice however you want. I have a much more comprehensive script that I use on client systems but this one will provide you with lots of relevant data.<\/p>\n<p>Have fun! Let me know if this is useful!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Check out my Pluralsight online training course: SQL Server: Index\u00a0Fragmentation Internals, Analysis, and Solutions.) Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools &#8211; how much memory is being used for data file pages and how much of that memory is storing [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,31,38,42,53,60,62,66,91],"tags":[],"class_list":["post-563","post","type-post","status-publish","format-standard","hentry","category-buffer-pool","category-database-maintenance","category-example-scripts","category-fragmentation","category-io-subsystems","category-memory","category-on-disk-structures","category-performance-tuning","category-surveys"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Performance issues from wasted buffer pool memory - Paul S. Randal<\/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:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Performance issues from wasted buffer pool memory - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Check out my Pluralsight online training course: SQL Server: Index\u00a0Fragmentation Internals, Analysis, and Solutions.) Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools &#8211; how much memory is being used for data file pages and how much of that memory is storing [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-06-01T10:13:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:50:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/\",\"name\":\"Performance issues from wasted buffer pool memory - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg\",\"datePublished\":\"2011-06-01T10:13:00+00:00\",\"dateModified\":\"2017-04-13T16:50:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance issues from wasted buffer pool memory\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Performance issues from wasted buffer pool memory - Paul S. Randal","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:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/","og_locale":"en_US","og_type":"article","og_title":"Performance issues from wasted buffer pool memory - Paul S. Randal","og_description":"(Check out my Pluralsight online training course: SQL Server: Index\u00a0Fragmentation Internals, Analysis, and Solutions.) Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools &#8211; how much memory is being used for data file pages and how much of that memory is storing [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/","og_site_name":"Paul S. Randal","article_published_time":"2011-06-01T10:13:00+00:00","article_modified_time":"2017-04-13T16:50:51+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/","name":"Performance issues from wasted buffer pool memory - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg","datePublished":"2011-06-01T10:13:00+00:00","dateModified":"2017-04-13T16:50:51+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/6\/bufpool.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-issues-from-wasted-buffer-pool-memory\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Performance issues from wasted buffer pool memory"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/563","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=563"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/563\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}