Cardiff User Group Talk June 24 2014 Speaking Questions

Answer to Questions for Cardiff User Group Talk Jun 24 2014.

1. Backup Encryption in 2014 – Is this an Enterprise only feature?

SQL Server Express and SQL Server Web do not support encryption during backup. However restoring from an encrypted backup to an instance of SQL Server Express or SQL Server Web is supported.

https://msdn.microsoft.com/en-us/library/cc645993.aspx#High_availability

2. SSD Buffer Pool Extension – 32x vs 4x how much addition memory is used inside SQL Server?

There a 2 structures per page in the bufferpool, 1 ‘BUF’ header (~100 bytes,corresponds to sys.dm_os_buffer_descriptors which is a list of BUF structures for all hashed buffers). and the actual page itself.

For pages in the bufferpool extension file they only need a ‘BUF’ structure in memory which contains file_id,page_id and is_in_bpool_extension =1. This points to the actual page in the BPE file!

Therefore only additonal small ‘BUF’ header structures are needed for a large bufferpool file.

NOTE: Bufferpool memory can also be seen via sys.dm_os_memory_clerks type=’MEMORYCLERK_SQLBUFFERPOOL’.

select type,sum(pages_kb) pages_kb

from sys.dm_os_memory_clerks

group by type

having type in (‘MEMORYCLERK_BHF’,‘MEMORYCLERK_SQLBUFFERPOOL’)

order by type

3. SSD or fast disk?

SSD may well be quicker even than fast disk as it can be larger then the cache on a disk array.

Fast Disks will help with writes which SSDs do not.

Advertisement

---