SQL Server 2014 Memory Pressure, NO Wait! We can fix it.
Are you seeing SQL Server memory pressure and do not have budget to add extra memory or upgrade current memory, then listen we can work around to solve performance slowness while you are upgrading server memory. Of course, you must have SQL Server 2014. In SQL Server 2014, new feature in SQL 2014 come with Buffer Pool Extension, which allow you to gain performance by place large amount of data in either SSD or fusion io disk. Configuring Buffer Pool Extension is very simple and requires no or little downtime. No other needs to be done on application site. FYI, if you already have In-Memory table configure then this might not work well or see much performance gain. Few requirements besides having Enterprise Edition of SQL Server 2014 is that your SSD drive space should be same size as your RAM and max can be 32X of your RAM. For example, imagine you have 32 GB RAM then your buffer pool extension must be minimum of 32 GB , and max can be 32 times of your RAM, so in this case maximum of your BPE can be as large as 1.0 TB. Data which is in BPE is called clean page and there is no dirty data in BPE.
Recently, I have implemented BPE in production, literally it took only 5 mins all together. So try it in your environment.
Here is the t-sql script you will need in order to configure it.
-- 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
RECONFIRUE WITH OVERRIDE;
GO
EXEC SYS.SP_CONFIGURE 'SHOW ADVANCED OPTIONS', '0' RECONFIGURE WITH OVERRIDE;
GO
-- SOMETIME WE HAVE TO RESTART SQL SERVER AFTER 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 = 'D:\BPE\BP_EXTENSION.BPE', SIZE = 3 GB);
-- NOW THAT BPE IS ENABLED WE SHOULD 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 ADVENTUREWORKS2014;
go
select * from dbo.factresellersalesxl_pagecompressed;
select * from dbo.factresellersalesXL_ccI
-- let's monitor what went to bpe, if there are not resu
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 OFF BPE,
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
Stay Tuned and visit. www.sqlgem.com