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?
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.
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:
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!
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