Performance Considerations

When an existing database is being encrypted, it is still kept online. Take performance issues into consideration to mitigate the impact on user access to the database, as well as general SAP ASE response time.

Factors to take into account for good database encryption performance include:

The number of SAP ASE engines on a multiprocessor machine
The number of disks the database is stored across
The buffer pool size associated with the database

Specifying the parallel degree value in alter database for encryption or, decryption, essentially tells SAP ASE how many worker threads to initiate when executing the operation. Since worker threads run concurrently, it is better when they are distributed across multiple CPUs. At the same time, it is better to avoid overwhelming CPU resources, since this could reduce the general response time from SAP ASE. For this reason, take the number of SAP ASE engines into consideration when deciding on the parallel degree value.

Device I/O is a major bottleneck during database encryption. SAP ASE can tackle this from two angles:

If every separate device is assigned a worker thread, device I/O can be carried out independently and concurrently for best throughput. Therefore parallel degree should consider the number of disks the database is stored across.
Performance will benefit if a big chunk of pages can be processed for every device read/write. The database must be online while the encryption/decryption is in progress. For this reason, instead of allocating a proprietary buffer, existing buffer manager mechanism has to be leveraged to solve synchronization problem. In this respect, you can create sufficient buffer cache and large I/O size of buffer pool to help SAP ASE improve its encryption performance.

This example shows how to configure both the buffer cache and pool size to fully encrypt a database called demodb, which has its data and log distributed across 11 devices:

> select dbid, segmap, lstart, size, vstart, vdevno from sysusages where dbid=db_id('demodb')

dbid   segmap lstart     size       vstart      vdevno
------ ------ ---------- ---------- ----------- -----------
   4        3          0      92160           0           1
   4        4      92160      30720           0           2
   4        3     122880     184320       92160           1
   4        4     307200      61440       30720           2
   4        3     368640     419840      276480           1
   4        4     788480      61440       92160           2
   4        3     849920     122880      696320           1
   4        4     972800     153600      153600           2
   4        3    1126400     819200      819200           1
   4        3    1945600    1638400           0           3
   4        3    3584000    1638400           0           4
   4        3    5222400    1638400           0           5
   4        3    6860800    1638400           0           6
   4        3    8499200    1638400           0           7
   4        3   10137600    1638400           0           8
   4        3   11776000    1638400           0           9
   4        3   13414400    1638400           0          10
   4        3   15052800    1638400           0          11
   4        4   16691200     204800      307200           2

Configure buffer cache and buffer pool size:
1. Create a specific data cache for demodb:
```
sp_cacheconfig demodb_cache, '100M'
```
  This creates a buffer cache named demodb_cache that has 100MB of space for database pages.
2. Create the specific size of buffer pool, where the buffer pool size is 8 times the size of the database page size:
```
sp_poolconfig demodb_cache, '100M' , '16k'
```
  Since the default database page size is 2K, the buffer pool size should be 8 X 2 = 16KB.
  
  This creates a 100MB buffer pool with 16K buffers in the named cache demodb_cache.
3. Bind the database to the buffer cache:
```
sp_bindcache demodb_cache, demo_db
```
  This binds the database demo_db to the created buffer cache demodb_cache.
Determine what parallel degree to use. In this example, there are 8 SAP ASE engines configured:
```
[Thread Pool:syb_default_pool]
```
Number of threads = 8

The maximum number of worker thread should not exceed 8.

In the meantime, with SAP ASE using 11 database devices, it needs, at most, 11 worker threads to perform device I/O in parallel. Since 11 worker threads would strain the eight engines, the parallel degree should be set to 8. However to allow SAP ASE to maintain its response time and perform other operations, avoid occupying all of its CPU resources by selecting a parallel degree of 6.
1. Make sure sufficient worker threads are configured:
```
sp_configure 'number of worker processes', 6
```
2. Alter database demodb for encryption:
```
alter database demodb encrypt with dbkey parallel degree 6
```
  sp_who shows 6 worker threads:
```
>sp_who
fid    spid    status    loginame    origname
    hostname    blk_spid    dbname
  tempdbname    cmd
        block_xloid        threadpool
------ -------- ---------------- ---------
……
  0     16    sleeping   NULL       NULL     NULL              0   master
   master DB    ENCRYPTION CONTROLLER        0                   NULL
  16    1     sleeping   NULL       NULL     NULL              0   master
   master       WORKER PROCESS               0                   NULL
  16    17    sleeping   NULL       NULL     NULL              0   master
   master      WORKER PROCESS                0                   NULL
……
```
  sp_helpdb can report the encryption progress and status:
```
1> sp_helpdb demodb
2> go
name     db_size    owner  dbid  created       durability lobcomplvl inrowlen status
-------- ---------- ------ ----- ------------- ---------- ---------- -------- ----------------
demodb   33000.0 MB sa       4   Sept 27, 2013 full                0 NULL     encryption in progress: 18%
```
  You can also use the dbencryption_status function to get encryption status and progress:
```
1> select dbencryption_status("status", db_id('demodb'))
2> go
-----------
2
1> select dbencryption_status("progress", db_id('demodb'))
2> go
-----------
21
```
  This shows that 21 percent of database pages has been encrypted.
  You can also use dbencryption_status to find the progress on a specific fragment:
```
1> select dbencryption_status("progress", db_id('demodb'), 92160)
2> go
-----------
83
```
  This shows that 83 percent of pages in the fragment with a logical page start of 92160 has been encrypted.

Encrypted databases consume more buffers for encryption and decryption than nonencrypted databases. If clean buffers are unavailable because of encryption and decryption:

Increase the buffer pool size and the buffer pool wash
Configure housekeeper free write percent to a value that allows the housekeeper task to wash buffers more frequently