0

We have a SharePoint application which works fine in the beginning. Now it takes more time to load the page and i am getting error page manytimes. When i check on that error, i found the reason as SQL timeout error. We used Stored Procedure to get data from AllUserData table from content database. Now the size of database is 2 GB. How can i increase performance and avoid getting error page?

  • 1
    What are you doing that you needed to write a SP against a SharePoint DB? There are other more supported ways to get data out than hacking away at the DBs (which is a no-no). – Eric Alexander Aug 23 '13 at 5:15
  • we tried using spquery. since that methods was slow, we created views and used stored procedure to get value from the views. – Suren Aug 23 '13 at 5:44
  • On the content on your site, are you using a lot of unique permissions on items and have a lot of them? – Robban1980 Aug 23 '13 at 6:29
  • No.. we are using only groups and handle user permissions in code.. – Suren Aug 23 '13 at 6:49
  • 1
    A DB size of 2GB is nothing, SharePoint can handle that with ease, event at 2TB it can still perform well. Use best practice guideline about how to configure and don't add custom code unless its normal SharePoint solutions accessing the object model. If you have custom t-sql for permissions you might well be introducing table locks that are causing deadlock and timeouts. – Paul Leigh Aug 23 '13 at 10:34
1

I would hazard a guess that your SQL server is critically under-spec'd for your environment. Please ensure it has enough RAM on the server itself, and that the database engine is configured correctly for it's memory usage.

As you haven't stated what version of SharePoint you are using, please locate the TechNet documentation for hardware and software requirements for SQL Server for the version of SharePoint you are running and validate the recommended production system requirements against your environment.

0

As another test to see what is happening on page load, you could enable the Developers Console. You can find instructions on how to enable it here:

http://www.topsharepoint.com/enable-developer-dashboard-in-sharepoint-2010

I recommend using the On Demand setting. This will give Admin's an icon in the upper right of the content of your pages. When you click the icon, it reloads the page with your Console Appearing below the content of your page. You can see if there are custom web parts or other objects that are causing your problems.

Hopefully this helps!

Cheers!

  • Developer dashboard does not show information regarding custom stored procedure execution. I am getting sql exception timeout error issue only when executing custom stored procedure. – Suren Aug 24 '13 at 7:36
-1

I wrote a blog series on bad access patterns to the SharePoint content database: http://apmblog.compuware.com/2010/03/18/how-to-avoid-the-top-5-sharepoint-performance-mistakes/ - we keep seeing the same mistakes from developers - assuming that SharePoint's List Model allows high transaction volume with high performance. Well - unfortunately thats not what SharePoint was designed and architect-ed for.

I also recorded a video on how I analyze SharePoint Performance Hotspots with free available tools. The video is linked in that blog above + you can find more of my SharePoint related blog posts on the same blog - just search for SharePoint

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.