After a reading about Always Encrypted, I was curious to see if there would be any performance impact by enabling it for any type of query.
Since the encryption/decryption are in the client side and that you can create indexes on deterministic encrypted columns, I wanted to know how that data would be saved into SQL pages and how an index would work in that case.
To encrypt the column you can follow many tutorials over the internet, I started with this one.
Checking the size, we can see a difference of around 17% between the encrypted and not encrypted table:
First I found the pages associated to the index on my encrypted column
SELECT * FROM sys.indexes WHERE object_id = object_id('Comments_encrypted')
DBCC IND ('StackOverflow2010', 'Comments_encrypted', -1)
Now for the given index, add the dbid, file id, page id and output format in DBCC PAGE command below for the two tables.
DBCC PAGE(6, 1, 1108888, 3) WITH TABLERESULTS DBCC PAGE(6, 1, 1141776, 3) WITH TABLERESULTS
Ps. the DBCC PAGE on the encrypted object will just work if you open a SSMS connection WITHOUT the parameter Column Encryption Setting=enabled. If you connect to SSMS with columns Encryption setting enabled, you will be able to SELECT from that column, but WILL NOT be able to run the DBCC PAGE and will get the following error:
Msg 0, Level 11, State 0, Line 42
Failed to decrypt column 'CreationDate (key)'.
Msg 0, Level 11, State 0, Line 42
Object reference not set to an instance of an object.
We can see above data encrypted also in the SQL pages and ordered by that hash (0x019FD….) whereas the non encrypted data is order by Datetime.
Now let’s see the query times. We know we can’t insert, delete or filter data on the encrypted table using SSMS, so we will use a simple Powershell app to do the job:
$SqlConn1= New-Object System.Data.SqlClient.SqlConnection
$SqlConn1.ConnectionString = "Server=LTDING213\RAFASQL19;Database=NorthWind;Integrated Security=SSPI; Column Encryption Setting=enabled;"
$SqlConn1.Open()
$SqlCmd1= New-Object System.Data.SqlClient.SqlCommand
#$sqlcmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd1.Connection = $SqlConn1
$SqlCmd1.CommandText = "AddCustomer @CustomerID, @CompanyName ,@ContactName ,@ContactTitle,@Address ,@City ,@Region ,@PostalCode ,@Country ,@Phone ,@Fax ,@dateOfBirth "
$SqlCmd1.Parameters.Add("@CustomerID",[system.data.SqlDbType]::VarChar) #|out-Null
$SqlCmd1.Parameters['@CustomerID'].Direction =[system.data.ParameterDirection]::Input
$SqlCmd1.Parameters['@CustomerID'].SQLvalue = 'RAFDON'
#$SqlCmd1.Parameters.AddWithValue("@CustomerID", 'RAFDON') |out-Null
$SqlCmd1.Parameters.AddWithValue("@CompanyName", 'Rafas') |out-Null
$SqlCmd1.Parameters.AddWithValue("@ContactName", 'Rafael') |out-Null
$SqlCmd1.Parameters.AddWithValue("@ContactTitle", 'DBA') |out-Null
$SqlCmd1.Parameters.AddWithValue("@Address", 'Rua Franscisco, 348') |out-Null
$SqlCmd1.Parameters.AddWithValue("@City", 'Sao Paulo') |out-Null
$SqlCmd1.Parameters.AddWithValue("@Region", 'SP') |out-Null
$SqlCmd1.Parameters.AddWithValue("@PostalCode", '04200-000') |out-Null
$SqlCmd1.Parameters.AddWithValue("@Country", 'Brasil') |out-Null
$SqlCmd1.Parameters.AddWithValue("@Phone", 'NULL') |out-Null
$SqlCmd1.Parameters.AddWithValue("@Fax", 'NULL') |out-Null
$DoB = New-Object -TypeName System.Data.SqlClient.SqlParameter
$DoB.ParameterName = "@DateOfBirth"
$DoB.SqlDbType = [System.Data.SqlDbType]::Datetime
#$DoB.Size = 25
$DoB.Direction = [System.Data.ParameterDirection]::Input
$DoB.Value = '1945-04-10 20:22'
$SqlCmd1.Parameters.Add($DoB) |out-Null;
$SqlCmd1.ExecuteNonQuery();
$SqlConn1.Close()
But then I found Aaron Bertrand had already posted about the performance impact of encryption:
https://sqlperformance.com/2015/08/sql-server-2016/always-encrypted-performance-follow-up