Strange SharePoint 2010 and SQL Server problem

We have a client who has just recently acquired their first SharePoint 2010 system. They have two servers, one running SQL Server 2008 R2, and the other one is used to run SharePoint 2010. When I restore a backup of the site collection from our development environment the server with running SharePoint, the site works fine for about a day. After that day, when we try to connect to the home page of the site, the site battles to connect. The browser just says “Connecting…” until the page eventually times out. But you can still access the “backend” pages, like the View all Site Content page (http:///_layouts/viewlsts.aspx) and the site settings page (http:///_layouts/settings.aspx).

Here’s a little info about the web parts that we are running on the home page. The web parts checks if the cache is empty, and if the cache is empty it populates the cache with all the items in a specific list. The list contains a lot of items, +/- 4000 items. So it’s obvious that SharePoint will be retrieving a lot of data from the SQL Server.

If I delete the web applications (that includes deleting the content database and the IIS web site) and I re-deploy the site collection using the backup I’ve made on our development environment the site works fine again for about a day.

After running into these problems we started monitoring the resources use on the SQL Server (Using the Resource Monitor). If you filter the network traffic to only display the network usage of the sqlserver.exe process, it shows that it’s only communicating at +/- 30KB/s. This is incredibly slow! When you copy a 390MB file from the SharePoint Server to the SQL (to test the connection speed between the two servers) it copies the file in 2 seconds.

This is a very st开发者_开发技巧range problem that raises a couple questions. First of all our development environment is almost exactly the same as their environment (in fact, we have less RAM) so why don’t we have any problems like this in our development environment? Secondly, why if you deploy the site from scratch, does the site work for a day, and only the start causing problems later? And the final question: why is the communication speed between the SharePoint and SQL so slow, but the connection speed between the two servers is very quick?

I really hope someone can help us with this, or give us a couple of things we can troubleshoot.

Thanks in advance!

After a very long struggle we found a solution.

We found this post:

http://trycatch.be/blogs/tom/archive/2009/04/22/never-turn-off-quot-auto-create-amp-auto-update-statistics-quot.aspx

We tested it and it worked!!!

So all we had to do was switch "Auto create statistics" and "Auto update statistics" to true, and to problem was solved

Thanks for all the replys

"why is the communication speed between the SharePoint and SQL so slow, but the connection speed between the two servers is very quick?"

You've shown its not the network with the file copy test so that would indicate that either a) SQL server is overloaded and can't keep up or b) the WFE is overloaded and can't keep up.

Have you performed basic performance troubleshoting looking at both servers for things like CPU/Memory/Disk swapping etc etc

Also there are lots of specific steps to troubleshoot SQL performance. This reference is for 2005 but all the same principals apply.