Category Archives: Database

Sitecore client and logon is very slow (properties table AGAIN)

The problem

I was at a customer to help identify why their Sitecore 7.2 client and logon was so slow!

Investigation

There are a number of things I look at when the Sitecore client starts to run slowly:

  1. Slow Event handlers (save, rename, create, etc)
  2. Slow pipeline processors (usually don’t check that it is a sitecore specific requests)
  3. History, Publish queue or Event queue have to many entries, see my blog on how to fix this.
  4. Property change events flooding the Event Queue in the Core database, see my blog on how to fix this

But after ensuring all the previous issues were not the problem, I found a new issue with the properties table.

Properties Table flooded with SC_Ticket entries

The properties table in the core database had over 500000 entries, it was filled with SC_TICKET_xxx entries.

tickets

Unfortunately the properties table does not have a created date column, so I could not write an SQL script to purge all the entries that where more than X days old.

I noticed that in the value column there was a time-stamp embedded in the Value field. My initial solution was to could create an sitecore agent to do the following:

  1. iterate over all the entries in the properties table
  2. Parse the value for SC_Ticket entries
  3. Remove all the entries that were older than X days.

I knew Sitecore must have a class, which had created all these entries. So using DotPeek I started my search and found the TicketManager class. The TicketManager even had a IsTicketExpired function.

is expired function

Solution

I found that there is already a Sitecore agent that checks for any tickets that are expired and removes them. It is called the CleanupAuthenticationTicketsAgent for some reason this was not in the web.config, but it is easy enough to add see below.

agent

But the important step is to reduce the number of days to keep the tickets as the default is 180. The Authentication.ClientPersistentLoginDuration setting is responsible for determining how long before the ticket should expire (see the IsTicketExpired function in the image above).

I set Authentication.ClientPersistentLoginDuration to 5 and it reduced the number of entries in the properties table to around 500, and then sitecore client and logon was much faster.

Prior to writing this post I wasn’t aware but the is a blog about how sitecore sessions can expire.

How to kill Sitecore with a Database connection

Well it’s been a while since my last blog due to the summer holidays and the fact that we have been very busy at Pentia.

Anyway here is my next installment in my “How to kill Sitecore” trilogy.

We recently took over a website that was having a lot of performance issues, and when I looked in the log files I noticed that the database connections were timing out.

So initially investigated if there was an issue with the SQL server, but after profiling and monitoring the database it appeared to have no performance issues, but I noticed that some of the exceptions were coming from the Heartbeat task.

What I believe the heartbeat tasks does among other things is to establish a connection for all the connections defined in the connections section, so that when the first request comes in the database connection is ready to retrieve the data from the SQL server.

The solution had a lot of include files some of which defined connections to databases that no longer existed.

So what happens when you have 4 connection strings that contain a reference to a databases that does not exists, well the hearth beat runs every minute and therefore creates 4 new connections every minute.

The connections were setup to timeout after 20 minutes which meant that after 20 minutes the heartbeat task had created and was waiting for 80 connections.

The problem is that the default max number of connections is 100, which left only 20 connections for the actually data providers that were configured correctly and when the site was busy this was not enough so they started to timeout,  which in turn caused the site to run very slowly 😦

To be honest it would of helped and been a lot quicker to find this error if .NET cast an CONNECTION POOL HAS REACHED IT MAX!!

But once this issue was found and I removed the unused connections, the site ran a LOT faster.

How to kill Sitecore whilst getting the languages for an item

I was reviewing a solution (not developed by Pentia) for a customer as they were concerned about the performance and stability of their site.

It didn’t take me long to find the following bit of code which calls the SQL server to find the languages for a given item:

languages

I was shocked that anyone would not use the Languages property on the Item class, which would have replaced the entire function.

I think in all my years of Sitecore development this is the worst bit of code I have ever seen, it shows  a complete lack of respect for the API and if  the site was upgraded it is possible that the database schema could change and therefore the code would break!

On average the SQL query took 400mS (which is quite slow, but then again the SQL server was getting hit by 4 front end servers for every request).

The same call to the Item.languages took 0.1mS, this simple change made every request 400mS quicker and reduced the load on the SQL server considerably.