This is a real story.
Yesterday shaun and I decided to look into reports that this site has been a bit slow over the last two weeks. As usual my first reaction was to check the Exception Logs in the Event Viewer, as frequent exceptions are often a sign of major issues on the site, especially Database timeouts.
One frequent error was a message that indicated a problem with the "BlogFeed" page, in particular the URL used to return the consolidated feed. This error was being logged about once every 10 secs. When I saw that, I remembered that recently my RSS Reader (I use the Wizz RSS Extension for Firefox) had quite frequently been returning errors.
The Consolidated RSS feed makes a call to the Database for each request (I have discussed this with Antonio and recommended that this should be cached on a high priority). This request calls a stored procedure that returns the 20 most recent posts (this is configurable in Module options but this site had the value set to 20).
When I used SQL Management Studio to analyse this stored procedure, this stored procedure took between 20 and 40 seconds to run. Since we use the default SqlCommand timeout of 30 secs this means that quite a few of these databse requests were timing out. I started using the Execution Plan and the Database Performance Tuning Wizard to try and figure out a better performing query. Both of these tools basically had no recommendations short of removing information from the returned set of columns, so the clever guy in me said - If 20 requests take ~30 secs then 10 requests will take ~15 secs.
Was I wrong - reducing the number to 10 made the query take 1-2 secs. Wow. why such a huge difference. I investigated further and found that 12 requests took 1-2 secs but 13 took 30-40 secs.
What was special about the 13th Blog. This Blog was Phil's blog with a table on the different CMS systems. The problem with this blog is that Phil copied a fairly large Excel Spreadsheet and pasted it into the HTML Editor. Unfortunately MS Office products when they "create" html versions of their text add a huuuuge ammount of style tags. This huge ammount of text is being returned as part of the query used for the RSS Feed and obviously takes about 30 secs.
So the solution I used was to reduce the setting on this site to return 10 blogs so that this blog would not be included. Instead of seeing an error (due to DB timeouts) every 5-10 secs we now see maybe 1 every 15 mins.
Please: Don't get me wrong. I have used Phil's blog as an example because it tells a story. I found the content of the Blog very instersting and I know he struggled with how to publish the information.
The moral of this story is that simple (and unexpected things) can be impacting site performance. Because of this large Blog, a query that is called more than 10 times a minute, takes about 30 secs to run, and will seriously impact site performance as other queries get less CPU time in the database. A second moral of this story is that this content should have been cached - it is a classic example of content that is fairly static - Blogs are infrequently updated - in the order of hrs and days not minutes.