2

Is there any documentation, test data, or other evidence to suggest that the Cost Threshold for Parallelism (CTP) setting might impact performance when TDE is enabled on a database? Without going into all of the details, we have seen some significant performance degradation with benchmark tests after enabling TDE on a database, and we are looking for any settings that might have an impact. CTP is currently set to the default of 5, which I know is not ideal, so please resist the urge chastise me for it.

We will also add CPUs and test further, but if we can get better performance by changing CTP or another setting, that would of course be better than adding CPUs (due to the cost of the hardware, as well as SQL Server licensing per CPU).

I am pushing to change the CTP and test again, but I was hoping to see if anyone has already tested it so that we can prioritize the changes we want to test accordingly.

Thanks!

  • I don't believe cost threshold for parallelism has anything, directly, to do with TDE performance. If you have a Premier Support or MSDN subscription, I would suggest reaching out to MS directly as there's not much information publicly available to my knowledge for tuning TDE performance sans throwing more hardware at the issue. – John Eisbrener Jun 6 '17 at 20:57
  • Start with what Brent suggested below. CTP doesn't do anything for/to TDE. You're likely getting stressed on CPU and possibly IO during initial encryption of all pages. Once that is done, there will still be some overhead but it's typically pretty low. If you haven't already done so, capture before, during and after data on perf, resource usage and waits. There is direct correlation between workload pattern and TDE impact. – SQLmojoe Jun 13 '17 at 22:32
  • Thanks for the comments. We are planning to throw some more CPUs on the system, just because it's an easy thing to try on VMs, and I'm checking with our security team to see what the bitness requirement is. It was initially encrypted at 256-bit, so if they will allow 128 or 192, I suspect that will have more of an impact. – Tony Hinkle Jun 14 '17 at 12:53
5

Whenever you've got an overall general performance question like this, start by asking, "What's the server's primary wait type?"

My favorite way to get that is with sp_BlitzFirst @SinceStartup = 1. (Disclaimer: I'm one of the authors of that open source script.) The first section of that will return your top waits since startup. If you post that output here, you'll get a lot better diagnostic answers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.