onsdag den 19. november 2014

SQL Server 2008 R2 Express bad performance

SQL Server 2008 R2 is suffering from very bad performance if there are lots of logon requests. The symptom is that the LSASS.exe will eat up to 100% of one core, blocking the system. 

The main reason for this is, that Microsoft increased security on the SQL Server 2008, and are using the PBKDF2 hash for passwords hashes and validation. This is very good, and the recommended hash to use for passwords.This hash is, by design, very slow to calculate, and it is even recommended to calculate it many times on its own output. This would make it very difficult for hackers to crack password given a hash.

Microsoft has a hotfix supposed to help on the performance, but it did not matter much for me.

I was using a 3rd party app that gave me these performance issues, and caused LSASS.EXE to be the system bottleneck.

There are three obvious solutions. And only the first will be an option for most people. And since extended support for SQL 2005 ends in April 2016, it is only a time limited solution, until the App vendor fixes their issues.

  1. Switch back to SQL Server 2005 Express. This solved the issue completely for me, and now the other processes can use all the cores, not being blocked waiting for SQL Authentication.
  2. Do like Microsoft has recommended for more than 10 years, switch to Windows integrated authentication. Problem is, that many apps do not support that.
  3. If you own the source code of the app, recoe it to keep a connection pool, and reuse SQL connections to minimize the number of logons. We can only expect logons to become more expensive.

Ingen kommentarer:

Send en kommentar