Hello~
I administer a database application (Azure SQL Server Back End/MS Access Front End)
At the moment, we have only one server, and we recently encrypted a few sensitive fields in our db. After a week of user testing with encryption, everything seemed fine, but of course, when we went live, things went terribly wrong -- our database became unusably slow with CPU usage hitting 100. We tried restarting the server, and that seemed to solve the problem temporarily, but again CPU usage hit 100 and brought everything to a halt.
We then upgraded our server, doubling our CPU and our RAM to 16GB. So far, this seems to have *fingers crossed* solved the problem.
It appears that encryption puts a strain on our server that we did not anticipate. We are going to soon add more servers, a proper development server, a mirroring server etc., and my question is -- is there a proper guide for me to determine what the specs for our server should be based on size, usage, number of users, fields encrypted, etc. -- the minimum requirements spelled out by Microsoft seemed inadequate in our case, and of course I would rather have more than enough capacity than have this situation arise again.
The price you pay for encryption is the usage of CPU, every time you lookup the encrypted columns the CPU has to go through the process of decrypting the data and serving it to the user.
You might also want to look for queries that you can improve.
See wait types and queues, see which one is the one your getting, wait types might pinpoint the issue you have. CPU, Memory, I/O Issues.
You can try setting sp_whoisactive from Adam Machanic and see the most common wait type, capturing the data and loading into a table.
Remember that what you did maybe was the only thing you needed to adequate your server to your needs and that you might find no issues after upgrading the CPU count and memory.
You might have fixed the issue at all, try to capture which waits you get the most, if you don't find any waits that are harmful to the server what you did at the end fixed the issue, don't hit your head so much.
If you don't know about waits and queues welcome to the world of waits and queues in SQL Server.
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
Regards,
April 20, 2021 at 2:52 pm
Thanks so much -- that info is all very helpful.
April 21, 2021 at 11:03 am
I'm a little confused. You keep talking about servers and server specs. However, you also said you were using Azure SQL Database. That doesn't involve servers. So... which is it?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply