Content Deployment, CQWP, Cross List Queries, Object Cache, and SharePoint 2007 Performance

Thursday, 10 April 2008 21:17 by RanjanBanerji

Oh what tangled webs we weave and in this case without an intent to deceive.

In the past few months I have been introduced to SharePoint 2007 and WSS 3.0 and as my blog shows I have had my fair share of frustrations.  It started with performance on the Content Query Web Part, led to my learning about Cross List Queries and the Object Cache.  I then switched to Content deployment and all its complexities.

Little did I know that the two will team up and raise new levels of hell for me.  Rather dramatic isn't it?

So we know that the CQWP uses cross list queries to get data.  Cross List Queries in turn utilize the object cache.  For best CQWP performance you want to make sure that your object cache is optimally configured.   I talked about this issue here.

In another galaxy far far away completely unrelated from CQWPs and Cross List Queries is the world of Content Deployment.  I have written several posts on Content Deployment.  One of them focuses on a Content Deployment approach that recommends creating several smaller jobs in order to get content from a large database across to another farm.

So what on Earth, I mean what on the galaxy far far away is the relationship between content deployment and content query web parts (CQWP) and Cross list queries?

Well it all started long long ago in a galaxy....  well you get the point.  I was working on an application that was deploying content using 8 small jobs every 30 minutes.  The 8 small jobs were created to break the deployment tasks of the whole application into manageable chunks of data as the network we were dealing with was full of problems.  The jobs would kick off on the hour and 30 past the hour.  During development everything went fine.  However, Once the application went live with hundreds of users hitting it the story changed.  The performance of the application was horrible.  Well not exactly.  It was horrible for about 5 minutes every 30 minutes on the hour and 30 past.

Coincidence?  At first I thought so.  But the correlation was too high.  So I turned off content deployment and performance improved.  Everything was back to normal.  But what was it about content deployment that made the application perform so poorly?  When I mean poorly I mean a page that loads in 1 second was taking 45 seconds to a few minutes.  So something was killing the application.

Well not really.  You see its not the entire application that was slow.  Only a few pages that loaded so slow.  The rest of the site acted absolutely normal.  So what was special about the slow pages?  Well they all had one or more content query web parts on them.  Hmmmm! so why were pages with CQWPs performing so poorly when a content deployment took place. 

My first step was to eliminate any kind of database transactions that may be occurring during content deployment.  So I ran content deployment with zero objects to deploy.  Zero exported and zero imported.  Yet, just as content deployment completed, performance of pages with a CQWP on it tanked.

Then it hit me.  I recalled my own post about optimizing content query web part performance and the importance of the object cache.  So I did the following to watch what happens when content deployment occurs/completes.

  • I turned on perfmon and watched the SharePoint cache hit ratio when the site was being hit by many users.  The hit ratio was consistently above 99%.  This was good.
  • I turned on SQL profiler during the same time and watched no queries being executed.  Well none that were being issued by the CQWP/Cross List Queries.
  • I kept watching as a content deployment job started and then completed. Ahaaa! SQL Server got slammed with 100s of queries all coming from CQWPs.

So content deployment wiped my object cache?  Why? Why? Why? 

So on the one hand we know how important the object cache is and on the other we have in the past talked about how you can break content deployment into several smaller jobs.  Unfortunately the two strategies do not work well together.  Apparently Microsoft has a patch out for fixing this problem or is about to release one.  Until then be prepared to lose performance if you have an application that has frequent content deployments. 

Steps you can take to mitigate the problem are:

  • Reduce the number of jobs running at any given point in time.  Imagine if you have 5 jobs running every hour on the hour.  Not all 5 will complete at the same time.  Lets say they finish between 5 to 8 minutes after they start, i.e., the hour.  So in 5 to 8 minutes past the hour your object cache will be wiped clean 5 times.  You really do not want that happening.
  • Don't run jobs too frequently.
  • Wait for Microsoft to get their patch out.

 

Tags:  
Categories:   SharePoint
Actions:   E-mail | Permalink | Comments (1) | Comment RSSRSS comment feed

Comments

May 30. 2014 16:53

trackback

Content Deployment - Getting it Right

Content Deployment - Getting it Right

Ranjan Banerji

Add comment


(Will show your Gravatar icon)

  Country flag

biuquote
  • Comment
  • Preview
Loading