9

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.

| improve this question | |
  • 2
    Can you get a bit more specific and maybe include some logs, the load you are experiencing? That will help getting a better answer – Bas Lijten Oct 7 '16 at 11:33
  • I've seen this issue. It got band-aided by scaling the SQL server...a lot. Not helpful, I know :| – Kam Figy Oct 7 '16 at 16:03
  • which version of SQL are you using? – ASura Oct 7 '16 at 17:13
  • Also you should be using single session for CD server as recommended by Sitecore. Another test you can make is that you switch to MongoDB provider and see if there is any performance gain. This can give us some direction if there is something with SQL provider. – Rohit Chopra Oct 8 '16 at 12:50
  • Is your Session on Azure SQL or Stand alone SQL server? The Sitecore Session performance scripts cannot be run in SQL Azure. I believe you would potentially need a high performance subscription simply for this session requirement. – Mohit Dharmadhikari Jun 20 '17 at 0:00
5

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.

| improve this answer | |
2

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.

| improve this answer | |
  • 2
    Could you provide a reference number from your chat with Sitecore Support? – Zachary Kniebel Jun 19 '17 at 21:10
  • Sure thing. It was registered as a bug #98800. If you need it, my specific ticket was #487192. – Martin English Jun 20 '17 at 21:33

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.