Buffer pool extension gotcha

Don’t get caught out by this one.

We just got a big new server for our upgrade to SQL 2014, and I specially added a pair of SSDs to the spec so we would have a super-fast RAID 1 partition for the tempdb files and so we could use the new buffer pool extension feature. The server vendor insisted we had to use their approved, server-grade SSDs to be properly supported. These disks are far from cheap so we got the 120GB drives.

All was going well until I tried to enable the buffer pool extension:

Msg 868, Level 16, State 1, Line 2
Buffer pool extension size must be larger than the current memory allocation threshold 117894 MB. Buffer pool extension is not enabled.

I didn’t see that in any of the material announcing this feature. It’s in the (really) small print in Books Online so technically we have been warned. We have plenty of RAM so we would need to buy bigger SSDs to use buffer pool extensions.

Still, at least anything that spills to disk in tempdb is going to go way faster, which is better than nothing.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s