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?
5
-
1What 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 AlexanderCommented Aug 23, 2013 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.– SurenCommented Aug 23, 2013 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?– Robban1980Commented Aug 23, 2013 at 6:29
-
No.. we are using only groups and handle user permissions in code..– SurenCommented Aug 23, 2013 at 6:49
-
1A 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 LeighCommented Aug 23, 2013 at 10:34
Add a comment
|
3 Answers
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.
answered Aug 24, 2013 at 9:53
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.– SurenCommented Aug 24, 2013 at 7:36
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
