temp

 --Step 1 

use MASTER

IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SoftwareSmithBufferPoolExtensions')
CREATE DATABASE SoftwareSmithBufferPoolExtensions;
GO
USE SoftwareSmithBufferPoolExtensions
--drop table testtable1
 
CREATE TABLE TestTable1
(col1 INT IDENTITY PRIMARY KEY, 
col2 INT,
col3 nvarchar (4000),
col4 nvarchar (4000),
col5 nvarchar (4000),
col6 nvarchar (4000),
col7 nvarchar (4000),
col8 nvarchar (4000),
col9 nvarchar (4000),
col10 nvarchar (4000)
 ) 
GO 
SET NOCOUNT ON
DECLARE @i int = 0
WHILE @i < 10000000
BEGIN
    SET @i = @i + 1
INSERT INTO TestTable1 (col2, col3, col4, col5, col6, col7, col8, col9, col10)
VALUES (RAND() * 2147483647,
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647)
)
END
 
CHECKPOINT -- writes dirty pages to disk, cleans the buffers
DBCC DROPCLEANBUFFERS -- removes all buffers
 
--Step 2 
 
 --check MEMORYCLERK_SQLBUFFERPOOL allocation
SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) / 1024 DESC

SET STATISTICS IO ON
SELECT * FROM TestTable1 where col3 != col10

DBCC CHECKDB ('SoftwareSmithBufferPoolExtensions') WITH PHYSICAL_ONLY;

-- Housekeeping 
use master

IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SoftwareSmithBufferPoolExtensions')

DROP DATABASE SoftwareSmithBufferPoolExtensions;
go