Monday, October 15, 2018

How much affects encryption to your database performance?

Transparent data encryption (TDE) helps you to secure your data at rest, this means the data files and related backups are encrypted, securing your data in case your media is stolen.
This technology works by implementing real-time I/O encryption and decryption, so this implementation is transparent for your applications and users.

However, this type of implementation could lead to some performance degradation since more resources must be allocated in order to perform the encrypt/decrypt operations.

On this post we will compare how much longer take some of the most common DB operations, so in case you are planning to implement it on your database, you can have an idea on what to expect from different operations.

Setup of our example


For this example we will use the Wide World Importers sample database, and restore it 2 times, one decrypted, and the other one encrypted. Please note that the restored database is around 3.2 GB size, a relatively small one.

To encrypt one of the databases we use the following T-SQL (more info about how TDE works here):

USE master;
GO


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Y0uR_P@$$worD_123';
GO


CREATE CERTIFICATE MyServerCert
	WITH SUBJECT = 'WWI Encryption';
GO


USE [WideWorldImporters_encrypted];
GO


CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO


ALTER DATABASE [WideWorldImporters_encrypted]


SET ENCRYPTION ON;
GO


After the above T-SQL has been executed, now we have the 2 databases ready for our example:


So it is just a matter of testing and measuring different operations and compare them (in no particular order):

DBCC CHECKDB


We run a dbcc checkdb on each database with the following T-SQL:

SET STATISTICS TIME ON;

DBCC CHECKDB([WideWorldImporters]) WITH NO_INFOMSGS

DBCC CHECKDB([WideWorldImporters_encrypted]) WITH NO_INFOMSGS

SET STATISTICS TIME OFF;

we can see the results, it took around 6% more to complete:



BACKUP DATABASE


Before we start this one, read this post about issues found on database backups with TDE enabled, so it is strongly recommended to patch your instances before using it.

SET STATISTICS TIME ON;

BACKUP DATABASE [WideWorldImporters] TO  
DISK = N'D:\DBAWork\WideWorldImporters-Full.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'WideWorldImporters-Full', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 100

BACKUP DATABASE [WideWorldImporters_encrypted] TO  
DISK = N'D:\DBAWork\WideWorldImporters_encrypted-Full.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'WideWorldImporters_encrypted-Full', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 100

SET STATISTICS TIME OFF;

Surprisingly, this one took less time with encryption enabled, around 15% less time... but wait! this is because of the number of pages processed by each backup operation, because if you check the average speed, you can see the encrypted backup operation is in fact, slower (around 33%), so in the case of this operation, it can vary for your environment:



Restore Database


If you plan to restore an encrypted database to a different server, remember that you must back up your master key and certificate and restore it on the new server in order to do it, otherwise you will not able to recover encrypted data.
Since now we are on the same instance, master key and certificate are already there, so we just run the simple RESTORE command:

SET STATISTICS TIME ON;

USE [master]
RESTORE DATABASE [WideWorldImporters] FROM  
DISK = N'D:\DBAWork\WideWorldImporters-Full.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 100

RESTORE DATABASE [WideWorldImporters_encrypted] FROM  
DISK = N'D:\DBAWork\WideWorldImporters_encrypted-Full.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 100

SET STATISTICS TIME OFF;

As with the backup operation, the restore took less time for the encrypted database, but you can see that is because of the number of pages processed, because if you check the average restore speed, for the encrypted database is slower (167% slower), so also check this one for your own databases:



Rebuild indexes


We execute Rebuild index statements for random tables with the following T-SQL:

SET STATISTICS TIME ON;

USE [WideWorldImporters]
GO

Print '--- Normal DB ---'

ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)

ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [PK_Application_People] ON [Application].[People] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Print '--- END Normal DB ---'

USE [WideWorldImporters_encrypted]
GO

Print '--- Encrypted DB ---'


ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)

ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [PK_Application_People] ON [Application].[People] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Print '--- END Encrypted DB ---'

SET STATISTICS TIME OFF;

You can see the relevant execution results:




The encrypted database took around 24% more time (on average for all the operations) to complete.


Update Statistics

 

Now we execute update statistics with full scan on various tables with the following T-SQL:

SET STATISTICS TIME ON;

USE [WideWorldImporters]
GO

Print '--- Normal DB ---'

UPDATE STATISTICS [Warehouse].[StockItemTransactions] 
WITH FULLSCAN

UPDATE STATISTICS [Sales].[OrderLines] 
WITH FULLSCAN

UPDATE STATISTICS [Warehouse].[StockItems] 
WITH FULLSCAN

UPDATE STATISTICS [Application].[People] 
WITH FULLSCAN

UPDATE STATISTICS [Purchasing].[PurchaseOrderLines] 
WITH FULLSCAN

Print '--- END Normal DB ---'

USE [WideWorldImporters_encrypted]
GO

Print '--- Encrypted DB ---'

UPDATE STATISTICS [Warehouse].[StockItemTransactions] 
WITH FULLSCAN

UPDATE STATISTICS [Sales].[OrderLines] 
WITH FULLSCAN

UPDATE STATISTICS [Warehouse].[StockItems] 
WITH FULLSCAN

UPDATE STATISTICS [Application].[People] 
WITH FULLSCAN

UPDATE STATISTICS [Purchasing].[PurchaseOrderLines] 
WITH FULLSCAN

Print '--- END Encrypted DB ---'

SET STATISTICS TIME OFF;

And these are the results:




Even when some of the tables took less time, on average the encrypted database took around 15% more time to complete the statistics update. This should be other to have into account to check for your own database.


For this post we will not test any DML or SELECT statement, but you are free to do your own tests and determine if TDE implementation suits for you.

As we stated earlier, we have demonstrated that additional workload is put on TDE enabled databases in order to support the encrypt/decrypt operations. Now is is your turn to perform your own validations to see how much is impacted your own databases with this, since this can vary on different storage types (for example on fast SSD storages, it could be barely noticed).

Wednesday, October 3, 2018

Query to determine XE Session status

On a previous post, we discussed what is an extended event (XE) and how to create one.

With the following T-SQL you can quickly check what are the currently running Extended Events sessions:



SELECT 
 ES.name,
 iif(RS.name IS NULL, 0, 1) AS running
FROM sys.dm_xe_sessions RS
RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name
--WHERE es.name = '<YOUR XE SESSION NAME>'



The column running will show 0 if the session is currently stopped and 1 if it is currently running.
In the commented WHERE clause, you can filter for a specific session.

Hope this helps you to monitor your XE sessions!

how to embed a PowerBI report into HTML code

PowerBI is a powerful reporting solution for Business Intelligence for analytical reports, it is not meant to replace SSRS, the focus of this tool is to be able to visualize data interactively from different data sources in a collaborative way.

On this post I will show you a quick way to embed your PowerBI reports into HTML code, that can help you to publish reports to websites.


For this, you will need a PowerBI Pro subscription (you can try it by free for 60 days)
Once you are logged to your PowerBI Pro account, select any published report you want to embed, as follows (for this example I will use a test report I have with imported dataset)

Generating HTML code for the report


Click on File and select Publish to web



A message to create an embed code will appear, click on it



A warning will also appear since your data will be public, click on publish



And that is all, now you have your HTML code to share it wherever you want.




What if you want to stop sharing it?


Maybe you should not reveal all the data from a report, or you want to stop sharing it, you must delete the embedded code to achieve it.

In your PowerBI site, click on Settings> Manage Embed codes



In the next page, click on (...) and click on delete




Published report


This is the final result of the published report: