Sitecore EventQueue strikes again!

To set the stage, I have been working on a Proof of Concept for a client that requires us to prove that Sitecore can handle a large number of items.  Essentially, there will be 5000+ micro sites with about 40 pages each.  Each page will have around 5 unique components and 5 unique images.  Once all is said and done, the item count reached around 700,000.

After publishing these 700,000 items, and restarting Sitecore, my site did not start at all.

After investigating a bit, I tried to get the row count on the EventQueue table in the web database.

select count(1) from eventqueue

This didn’t return, so I assumed that the count was rather large and it would take a long time to scan all of the rows to get the final count.

I wanted to know exactly how many row were in the EventQueue table.  On a past project, I had a similar problem and was able to hunt down a query that helped me out:

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID(‘eventqueue’)
AND (index_id=0 or index_id=1);

This query returned quickly and revealed that there were close to 1,500,000 rows in the EventQueue table.  WHOA!!

Clearly I would need to clear this table out because it was taking Sitecore an extremely long time to query this table.  This likely was the culprit of Sitecore not starting at all.

A simple truncate was not going to do it:

truncate table EventQueue

This again, wraps a transaction around the entire truncate operation and needs to load all of the rows into memory.  This would also take an extremely long time to return.  Surely there must be a way around this.  The answer lies in another query that essentially only deletes 1000 rows at a time.  It still takes a while to return, but it doesn’t lock up the database when it is running because it’s only creating transactions around 1000 rows at a time.  Here is the query:

SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP (1000)
FROM eventqueue
END

And finally, after clearing out the EventQueue table, sanity was restored and Sitecore was able to start.  The next time you have an extremeley large publish operation, you will likely have the same problem.  Hopefully you will be able to track down this post to help you out!

 

Advertisements

6 Responses to “Sitecore EventQueue strikes again!”

  1. jammykam Says:

    Also check the History and Publish queue. Might want to clear those out too.

    TRUNCATE TABLE [History];
    TRUNCATE TABLE [EventQueue];
    TRUNCATE TABLE [PublishQueue];

  2. Andy Cohen Says:

    Alternatively, I could have dropped and then recreated the EventQueue table, but I didn’t want to do that.

  3. jammykam Says:

    No, the truncate is better… we’ve been seeing some exploding tables and the clean up tasks either don’t seem to handle it or something else is going on (most likely). Haven’t had time to investigate yet. It might be worth wrapping your import in an EventDisabler().

    • Andy Cohen Says:

      In this situation, it wasn’t the import that was taking a long time, it was in fact the start up process of Sitecore itself which queries the EventQueue table.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: