Add BufferPoolExtension to scripts

AlexTitenko · AlexTitenko · commit 7e85b2e4cbba · 2017-06-13T09:36:30.000+03:00
diff --git a/Scripts/BufferPoolExtension.sql b/Scripts/BufferPoolExtension.sql
@@ -0,0 +1,61 @@
+/*
+Author:Ryan Adams
+Original link: http://www.ryanjadams.com/2017/05/implementing-buffer-pool-extension
+*/
+
+--Review current BPE configuration
+SELECT [path], state_description, current_size_in_kb,
+CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
+FROM sys.dm_os_buffer_pool_extension_configuration;
+ 
+--Reduce SQL Server Max memory to restrict the BP and force the use of BPE
+EXEC sys.sp_configure 'show advanced options', '1'  RECONFIGURE WITH OVERRIDE;
+GO
+EXEC sys.sp_configure 'max server memory (MB)', '2000';
+GO
+RECONFIGURE WITH OVERRIDE;
+GO
+EXEC sys.sp_configure 'show advanced options', '0'  RECONFIGURE WITH OVERRIDE;
+GO
+/*********** WE HAVE TO RESTART SQL SERVER FOR BPE TO SEE THE RAM CHANGE ***************/
+ 
+--Enable BPE
+--Go look at the file size on disk right after you run this
+ALTER SERVER CONFIGURATION
+SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\Temp\BP_Extension.BPE', SIZE = 2 GB);
+ 
+--Now that it is enabled we'll inspect the configuration again
+SELECT [path], state_description, current_size_in_kb,
+CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
+FROM sys.dm_os_buffer_pool_extension_configuration;
+ 
+--Try to read enough data to fill BP and start using BPE
+USE AdventureworksDW2016CTP3;
+GO
+SELECT * FROM dbo.FactResellerSalesXL_PageCompressed;
+--If the above didn't do the trick then query this table as well
+--SELECT * FROM dbo.FactResellerSalesXL_CCI;
+ 
+--Let's see what went to BPE.  If there are no results then go query more data.
+SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
+CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
+AVG(read_microsec) AS [Avg Read Time (microseconds)]
+FROM sys.dm_os_buffer_descriptors
+WHERE database_id <> 32767
+AND is_in_bpool_extension = 1
+GROUP BY DB_NAME(database_id)
+ORDER BY [Buffer size(MB)] DESC;
+ 
+--Turn BPE off. Go look in c:\temp to see what happens to the physical data file
+ALTER SERVER CONFIGURATION
+SET BUFFER POOL EXTENSION OFF;
+ 
+--Put Max Server Memory back where it was
+EXEC sys.sp_configure 'show advanced options', '1'  RECONFIGURE WITH OVERRIDE;
+GO
+EXEC sys.sp_configure 'max server memory (MB)', '3500';
+GO
+RECONFIGURE WITH OVERRIDE;
+GO
+EXEC sys.sp_configure 'show advanced options', '0'  RECONFIGURE WITH OVERRIDE;
+GO
