WITH ENCRYPTION can slowdown performance of SP?

Question

Hi All I am using WITH ENCRYPTION in stored procedure to encrypt whole SP text. is there any performance issue generated because of that? I am working in product based company and we have recently migrate SQL server 2014 from SQL server 2008. now we are facing some performance related issues. so i want to know whether using with encryption can slow down performance in SQL server 2014? is algorithm used in SQL server 2008 and SQL server 2014 are different?

Vishal Patel · Answer

But in our case it's happening. we have upgrade our SQL Server from 2008 R2 to 2014 by using upgrade advisor and also we have update all statistics. but we have not performed Sp_recompile and Sp_refreshview on whole Database and also database compatibility level set 2008-R2. so shall these factors impact on performance ?

ketan borsdiya · Answer

Hi Vishal I don't think so encrypted Stored Procedure affect to performance issue. Because we don't need to decrypt stored procedure to run it. And I have one table with 10 Columns and 1.5 millions rows. I have run SP with select query with and without encryption that take same time in SQL 2014.