{"id":1032,"date":"2016-01-09T14:36:08","date_gmt":"2016-01-09T22:36:08","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1032"},"modified":"2020-01-11T12:51:52","modified_gmt":"2020-01-11T20:51:52","slug":"sql-server-diagnostic-information-queries-detailed-day-9","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 9"},"content":{"rendered":"

For Day 9 of this series, we start out with Query #19<\/strong>, which is BPE Configuration.  This query retrieves information about your buffer pool extension (BPE) configuration from the sys.dm_os_buffer_pool_extension_configuration<\/a> dynamic management view. Query #19 is shown in Figure 1. <\/p>\n

\n
\n
   1:<\/span> -- See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> [path<\/span>], state_description, current_size_in_kb, <\/pre>\n
<\/p>\n
   3:<\/span> CAST<\/span>(current_size_in_kb\/1048576.0 AS<\/span> DECIMAL<\/span>(10,2)) AS<\/span> [Size<\/span> (GB)]<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.dm_os_buffer_pool_extension_configuration WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   5:<\/span>  <\/pre>\n
<\/p>\n
   6:<\/span> -- BPE is available in both Standard Edition and Enterprise Edition<\/span><\/pre>\n
<\/p>\n
   7:<\/span> -- It is a more interesting feature for Standard Edition<\/span><\/pre>\n
<\/p>\n
   8:<\/span>  <\/pre>\n
<\/p>\n
   9:<\/span> -- Buffer Pool Extension to SSDs in SQL Server 2014<\/span><\/pre>\n
<\/p>\n
  10:<\/span> -- http:\/\/blogs.technet.com\/b\/dataplatforminsider\/archive\/2013\/07\/25\/buffer-pool-extension-to<\/span>-ssds-in<\/span>-sql<\/span>-server-2014.aspx<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #19 BPE Configuration<\/strong><\/p>\n
BPE was a new feature that was added to SQL Server 2014. The idea behind it is that you can set aside some space in your file system for a read-only cache file of clean buffer pool pages, that makes it look like your buffer pool is larger than it actually is. SQL Server will look in the actual buffer pool first, and then in the BPE file, and finally in your storage subsystem to find the data that it needs. The ideal scenario for this to be helpful is if you have a read-intensive, OLTP workload that does a lot of random reads from your data files (but you don\u2019t have enough RAM to fit the data that is being read into the actual buffer pool). If you are using magnetic storage that has relatively poor random read I\/O performance, and if your BPE file is on fast, local flash storage, then, you might see a performance improvement from using BPE.<\/p>\n
The problem with this in real life is that there is typically a lot of write activity to the BPE file to keep it up to date with the data that it is trying to cache. Some types of less expensive flash storage has performance issues when it is under sustained write pressure. It is also hard to drive an OLTP workload hard enough to cause it to actually use the BPE file in most scenarios, unless you set max server memory artificially low. The BPE feature is mainly interesting if you are using SQL Server 2014 Standard Edition, where you are restricted to using 128GB of RAM per instance for the Database Engine. <\/p>\n
 <\/p>\n
Query #20<\/strong> is BPE Usage. This query retrieves data from the sys.dm_os_buffer_descriptors<\/a> dynamic management view. Query #20 is shown in Figure 2. <\/p>\n
\n
\n
   1:<\/span> -- Look at buffer descriptors to see BPE usage by database (Query 20) (BPE Usage) <\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> DB_NAME(database_id) AS<\/span> [Database<\/span> Name], COUNT<\/span>(page_id) AS<\/span> [Page Count<\/span>],<\/pre>\n
<\/p>\n
   3:<\/span> CAST<\/span>(COUNT<\/span>(*)\/128.0 AS<\/span> DECIMAL<\/span>(10, 2)) AS<\/span> [Buffer size<\/span>(MB)], <\/pre>\n
<\/p>\n
   4:<\/span> AVG<\/span>(read_microsec) AS<\/span> [Avg<\/span> Read<\/span> Time<\/span> (microseconds)]<\/pre>\n
<\/p>\n
   5:<\/span> FROM<\/span> sys.dm_os_buffer_descriptors WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   6:<\/span> WHERE<\/span> database_id <> 32767<\/pre>\n
<\/p>\n
   7:<\/span> AND<\/span> is_in_bpool_extension = 1<\/pre>\n
<\/p>\n
   8:<\/span> GROUP<\/span> BY<\/span> DB_NAME(database_id) <\/pre>\n
<\/p>\n
   9:<\/span> ORDER<\/span> BY<\/span> [Buffer size<\/span>(MB)] DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  10:<\/span>  <\/pre>\n
<\/p>\n
  11:<\/span> -- You will see no<\/span> results if<\/span> BPE is<\/span> not<\/span> enabled or<\/span> if<\/span> there is<\/span> no<\/span> BPE usage<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #20 BPE Usage<\/strong><\/p>\n
This query (which usually takes a few seconds to run), will show you which databases are actually using your BPE file. I have not seen too many people using the BPE feature so far, which I think is a shame. If you have the right kind of workload, it may help performance. It is certainly worth some testing to try it out.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 9 of this series, we start out with Query #19, which is BPE Configuration.  This query retrieves information about your buffer pool extension (BPE) configuration from the sys.dm_os_buffer_pool_extension_configuration dynamic management view. Query #19 is shown in Figure 1. 1: — See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration) 2: […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[270],"class_list":["post-1032","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 9 - Glenn Berry<\/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\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 9 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 9 of this series, we start out with Query #19, which is BPE Configuration.  This query retrieves information about your buffer pool extension (BPE) configuration from the sys.dm_os_buffer_pool_extension_configuration dynamic management view. Query #19 is shown in Figure 1. 1: -- See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration) 2: […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-09T22:36:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-11T20:51:52+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 9 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-09T22:36:08+00:00\",\"dateModified\":\"2020-01-11T20:51:52+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 9\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?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\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 9 - Glenn Berry","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\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 9 - Glenn Berry","og_description":"For Day 9 of this series, we start out with Query #19, which is BPE Configuration.  This query retrieves information about your buffer pool extension (BPE) configuration from the sys.dm_os_buffer_pool_extension_configuration dynamic management view. Query #19 is shown in Figure 1. 1: -- See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration) 2: […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-09T22:36:08+00:00","article_modified_time":"2020-01-11T20:51:52+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 9 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-09T22:36:08+00:00","dateModified":"2020-01-11T20:51:52+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-9\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 9"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?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\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1032","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1032"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1032\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1032"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1032"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}