Table variable performance on SQL 2008 R2 server with TDE enabled

Question

I have the following situation: I have a SQL 2008 R2 Enterprise edition where I have enabled TDE encryption in one of the databases. One of the stored procedures from the encrypted database is using a table variable (@t1), table that gets populated with almost 600K records. Then there is a select statement that uses a join between this table and another table from the encrypted database (t2), on t2 tables I have around 20 mil rows. This join takes forever to complete( last time took almost 4h). If I use instead of the table variable a tempoarary table (#t3) and do the same join the result is instant. Also if I run join between these 2 tables in another server where I do not have TDE encryption ( same SQL 2008 R2) , the join finish in seconds So did anybody encounter similar problems with table variables and encrypted databases using TDE? This is how I encrypted the database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AASFA234234234as234#234#$##$'

CREATE CERTIFICATE SQLCertificate
WITH SUBJECT = 'SQL Certificate',
EXPIRY_DATE = '10/31/2020';

USE DBTest
go

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SQLCertificate;

ALTER DATABASE DBTest
SET ENCRYPTION ON
And this is the script that I used where _rptHousehold is a table that has 18mil records. The script never gets to the PRINT '3 ' + CONVERT(VARCHAR,GETDATE(),121), hangs on the select count(*) from @tt
PRINT '1 ' + CONVERT(VARCHAR,GETDATE(),121)

IF object_id('tempdb..#tt') IS NOT NULL 
    DROP TABLE #tt


declare  @tt table
(   [id] int        IDENTITY(1,1), 
    TableID         DECIMAL(11,0),
    AdvisorID       INT,
    idBuild         INT,
    Tablename       sysname,
    tCreatedate     datetime,
    ColumnName      varchar(100),
    Column_ID       int,
    qtyValue        decimal(25,9),
    tModifiedDate   datetime
)

INSERT INTO @tt
(TableID , AdvisorID , idBuild,Tablename,   tCreatedate,ColumnName, Column_ID,qtyValue )            
select  TOP 600000 
    t.object_ID
    ,AdvisorID
    ,1635
    ,t.NAME
    ,t.Create_date
    ,c.Name
    ,c.object_ID    
    ,CAST(RAND()* 100000 AS DECIMAL(25,9))
FROM sys.tables t CROSS JOIN sys.columns c  
    CROSS JOIN (SELECT DISTINCT idAdvisor AS AdvisorID FROM dbo._rptHousehold WHERE idBuild = 1635) ac              


PRINT '2 ' + CONVERT(VARCHAR,GETDATE(),121)

SELECT COUNT(*) FROM @tt 
PRINT '3 ' + CONVERT(VARCHAR,GETDATE(),121)

UPDATE tt
    SET 
        qtyValue = rp.qtyAvgPAAssets
FROM @tt tt 
    JOIN _rptHousehold rp
        ON rp.idAdvisor= tt.AdvisorID
            AND rp.idBuild= tt.idBuild

PRINT '4 ' + CONVERT(VARCHAR,GETDATE(),121)

Answer 1

Well i don't think its directly connected with TDE, as TDE encrypts data when they are written to disk and decrypts them when they are read from disk and overhead is said to be not so big (<10%).

• maybe on new server data is not cached in RAM, so they have to be read from disk (of course in that case TDE makes it a bit slower).
• it is recommender to use temporary tables instead of table variables, if you have many rows to work with.
• many other reasons why its not work as good as before- i would start with checking execution plan..