Has anyone seen any performance problems with Sitecore SQL Session Provider?
We are noticing some problems with blocking queries under load, specifically the GetItemExclusive stored proc.
Sitecore Stack Exchange is a question and answer site for developers and end users of the Sitecore CMS and multichannel marketing software. It only takes a minute to sign up.Sign up to join this community
You've seen the "Optimize SQL Server Performance" section (Not Applicable to Azure SQL) at https://doc.sitecore.net/sitecore_experience_platform/setting_up__maintaining/xdb/session_state/walkthrough_configure_a_shared_session_state_database_using_the_sql_server_provider as it relates to Session state? While no silver bullet, that approach can improve session management perf with SQL Server and we've been successful with that on a few occasions. Know that TempDB is recreated at service restart and the permissions [sql login to db user mapping] will be gone, so a better implementation involves scripting these permissions as an extension to SQL restart.
We've also been known to isolate Private and Shared session state into their own SQL Server databases. That could help you isolate your issue, perhaps?
More generally to your question, theoretically a load of significant size could raise perf problems with any aspect of Sitecore. I know that doesn't specifically help you, but using SQL Profiler or an APM tool (AppDynamics or NewRelic?) can assist in diagnosing this. You've probably done that and found the GetItemExclusive bottleneck, so I'm curious about underlying SQL Server architecture.
I had the exact same issue, and Sitecore Support confirmed a connection leaking bug in the SessionStateStoreProvider that causes unnecessary load on SQL server making it unresponsive.
It is fixed in Sitecore 8.2 update-2. Prior versions will require a ticket to request the patch.
Sitecore Support registered the issue as bug #98800 and my support ticket number was #487192.