SharePoint

Tuning SQL Server for SharePoint Performance

Updated on May 23, 2013

I had an opportunity to present on SQL Server User Group Indonesia monthly meeting on Oct 27, 2011 about SQL performance tuning from SharePoint perspective. Most of DBAs might be familiar with SQL stuff, but not on what SharePoint expects from SQL Server. Understanding how SharePoint works is really important in tuning SQL Server.

Most of the SharePoint contents are stored in SQL Server. Documents, picture, pages, Lists, and its configuration. Document and picture are stored as binary in SQL, so do not expect to find the physical files in SharePoint server.

Most of physical files that are not stored in database located under 12 hive (14 hive in SharePoint 2010). This folder contains customization files such as CSS, master pages, features, ASPX page. There are some occasion that developer prefer to store master page and layout in the library so it is actually in your SQL tables. So please communicate closely with the development team to understand exactly on file location. However, rest assure that 90% of SharePoint contents are inside SQL Server.

Below are some guidance to configure SQL Server for SharePoint performance:

Configure instance level index fill factor to 70%. Most of veteran DBAs now that this setting is optimized for read operation with moderate insert and update. You may think to optimize fill factor on table and index level, but this is not possible in SharePoint database. Microsoft does not recommend to alter table and index of SharePoint DBs. Default fill factor is 0 so you need to alter this setting. The 70% is not a magic bullet, do continuous monitoring and adjust fill factor accordingly.

Set MAXDOP to 1; contrary to popular belief, SharePoint does not benefit much from parallelism. There are lots of cases when SharePoint produces ad-hoc queries that SQL Server may struggle find the most optimal path. Setting the value to 1 means forcing SQL Server to not create parallel plan. More discussion and testing about this are here:

http://blogs.msdn.com/b/grahamk/archive/2009/06/15/troubleshooting-sql-server-deadlocks-and-performance-when-running-sharepoint.aspx

http://blogs.msdn.com/b/rmeure/archive/2011/12/23/optimizing-sql-for-sharepoint.aspx

 

Turn OFF update statistics; and configure proper schedule to refresh it. Make sure to set proper schedule for this to avoid outdated statistics.

Plan your data and log file growth. The default data file growth in SQL Server is 1 MB so it does not make any senses for SharePoint system. Imagine somebody is uploading 5 MB file and there are 100 uploads per day. It means your data file will grow 500 times/day; this will create bottleneck in IO system and usually escalated to CPU. I usually prefer to do capacity planning to forecast database size for 3-6 months. So if you think that your data growth will be 50 GB in 6 months, then set the data file size to 50 GB and configure its growth rate to something around 500 MB. Under previous scenario, your database only needs to grow once per day to accommodate 500 uploads. This reduce IO bottleneck significantly and speed up SharePoint performance.

Plan your tempdb; this can be a very long discussion and deserve separate post. There is a reference from Paul Randall about tempdb optimization here:

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

 

There is a long whitepaper from Microsoft about this:

http://www.microsoft.com/download/en/details.aspx?id=24282

 

Sharepoint – Reporting Services Integration

I did presentation for MaxIT event on May 29, 2009 and one of the interesting discussion was SharePoint integration with Reporting Services. I know that SharePoint is hot here in Singapore. I also did demo on some new features of SSRS 2008 such as tablix and charting. My presentation slide can be downloaded here:

There are several advantages of integrating SSRS with SharePoint:

  • Seamless UI for end user. They do not need to jump from one screen to another to open report and SharePoint dashboard at the same time.
  • All reports are deployed as document library, so it follows all SharePoint library setting. It saves lots of time instead of managing report in separate report manager.
  • All reports are under SharePoint security setting, giving better consolidated administrative from SharePoint perspective
  • Render reports inside web part. It makes building SharePoint dashboard faster and easier.

What do we need to configure this:

  • SharePoint Services 3.0 or MOSS 2007, It’s better if upgraded to SP1 or SP2.
  • SQL Server 2005 Reporting Services with SP2, or Reporting Services 2008.
  • Reporting Services add-in for SharePoint. There are 2 versions of add-in: SSRS 2005 and SSRS 2008.

Configuration can be simple (all in one box) or scale up to separate boxes. Below is the logical architecture if SSRS and SharePoint are installed on separate boxes:

ssrs-sharepoint

Assuming that SharePoint already installed, below are the explanations to do integration.

Install SSRS 2005 + SP2 in the SharePoint box, or separate machine. Just make sure to select “Install Report Server but do not configure” when doing this. The default configuration is native mode instead of SharePoint integrated, so please take note. Once SSRS installed, upgrade to SP2 (minimum) or directly to SP3.

In the case of SSRS 2008, SharePoint integration feature is already built in. Upgrading to SQL 2008 SP1 is recommended but not compulsory. Another improvement of SSRS 2008 is guided installation wizard. Once the installation CD is plugged in, there will be a menu to install SSRS in SharePoint mode instead of native stand alone.

Configure SSRS to integrate with SharePoint. Open SSRS configuration manager and connect to SSRS instance then create a new virtual directory for ReportServer. This should be the address of existing IIS website where the report server to be configured.

reportserversharepoint1

Configuring Windows Service and Web Service identity is straight forward. Use Network Service as identity account if it is a single box installation. In the case of scale up scenario, it needs to provide a dedicated SSRS service account from AD and use it as SSRS identity.

reportserversharepoint2

The next step is to create a new database for ReportServerDB and make sure to specify database as SharePoint integrated mode. It has to connect to existing SQL Server database instance.

reportserversharepoint3

It is not necessary to configure IIS website for Report Manager because SharePoint is our report manager now. Email and execution account configuration can be ignored in the meantime, specially if we do not use automated report delivery by email.

Another part of configuration is inside SharePoint central admin. Install SharePointRS.msi (SharePointRS_X64.msi for 64 bit) in SharePoint box. This package needs to be installed at every front end web server if SharePoint is configured as web farm. By the way, this file is Reporting Services add-in for SharePoint I mentioned earlier. Once installation finished, do IISRESET and open SharePoint Central Admin. A new Reporting Services configuration menu is available.

cassrs0

SharePoint has to know the address of Report Server which is under IIS virtual directory. Security configuration should be in windows integrated mode. This can be done under “Manage integration setting” link.

cassrs1

SharePoint also has to connect to ReportServerDB that already created earlier. Specify the name of SQL Server instance and click OK and specify windows account to connect to SQL Server.

cassrs2

There is another note for multiple boxes scenario: Reporting Services need to have local access to SharePoint object model in order to communicate with SharePoint. It means SharePoint binary has to be installed in SSRS machine, but left it empty without web application. It just needs to join existing SharePoint farm during installation, it is not necessary to create a site collection.

That’s all configuration needed. Now time to test it. Create a new library in SharePoint with the type of Report Library, for example “Sales Report”. Open existing SSRS source code (with Visual Studio of course) and deploy them to SharePoint. The tricky part is now the deployment location has changed. Remember that SharePoint is our report manager now. Below is the deployment configuration in Visual Studio:

vsdeployrs2sharepoint

What is the difference? Now it connects to SharePoint site as report server instead of http://myserver/reportserver. Data source and report folder location should point to address of “Sales Report” which is created earlier. The Sales Report library should display all reports after the deployment completed successfully.

reportdeployed

Clicking “Product Line Sales” bring up SSRS report under SharePoint, nice :).

ssrsinsharepoint

I don’t like full screen report, can we render them as web part?

Yes, report can be rendered as web part, and with all SharePoint flexibility, just drag and drop to any zones to display them. This web part is actually part of SharePointRS.msi installed previously. The name is Reporting Services viewer web part.

addssrswebpart

One page may render multiple web part and display them in a nice dashboard as follow:

reportwebpart

Managing the report is easy. Everything can be done under SharePoint. Click one of the report and java script popup tells us what all related reporting services setting can be done.

sharepointreportadmin

Nice feature eh? Now we have another more reason to implement BI under SharePoint :).

One more thing, Microsoft does provide a comprehensive guide to achieve this integration here.

Running SharePoint on Windows 7

I know that running SharePoint on Vista or Windows 7 is not for production, but as a developer I need a handy tool for coding and testing instead of VPC or VMWare. Well, installing SharePoint on Windows 7 is not officially supported, but if you are a geek enthusiast, here are the steps:

First, prepare IIS 7 for ASP.NET component. I configured the following components in my Win 7: 

prepareIIS71 prepareIIS7 

 

Download SharePoint with SP1, in my case I use WSS 3.0 SP1 for testing purpose. Get it from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=4191a531-a2e9-45e4-b71e-5b0b17108bd2&displaylang=en

Install SQL Server, I use SQL 2008 Express for testing purpose. Download from here:

http://www.microsoft.com/express/sql/default.aspx

The next step is the most important thing. Thanks to Bamboo solution who created a small install launcher to make WSS work on Vista. Get WssOnVista helper and install it on Win 7 box: http://community.bamboosolutions.com/media/p/193.aspx

Run the WssVista helper and point to WSS 3 SP1 binary, then follow standard SharePoint installation procedure. One important thing is to choose Advanced mode instead of Basic, and proceed with Web front end configuration. This is because the wizard cannot detect SQL 2008 Express automatically in my machine. Well, I never try it with SQL05 Express which is usually managed to detect SQL Express instance under Basic instalation mode.

wssvista1

wssvista2

wssvista7

 

After several clicks and Next, here is my WSS on Windows 7, peace :)

wssvista8

Slide Deck and Demo on SharePoint + Silverlight

Technorati Tags: ,

This is a quick post for my presentation slide and demos at SharePoint Pro Singapore User Group Meeting last week. I discussed about configuration on SharePoint box to make it Silverlight enabled, and also giving some demos to write Silverlight video player then deploy it to SharePoint.

image

The PPT and demos are available for download from my Skydrive. Enjoy!

The Best Books on SharePoint 2007 Development

One of the FAQ that I received when teaching SharePoint Development class was: What’s the best book on SharePoint development? Well, It can be easy and difficult at once. SharePoint is a very huge product with tons of feature inside. Every book has their own approach, and some of them only cover specific topics in development. SharePoint can be used for content management, record management, BI dashboard, business process automation, workflow, and so on. Finally, there is no single book that able to cover all of those features.

I red some SharePoint development books, and here are the quick reviews on it. Take it as a consideration, not the recommendation. Here are the lists:

  1. Building Office 2007 Solutions in C# 2005 (Apress), written by SharePoint MVP Scott Hiller. This is the book if you are novice in SharePoint. Doing development in it requires fundamental knowledge on SharePoint Out of the Box. Some students want to jump directly, but ended with confusion.  Almost half of this book covers fundamental SharePoint concept. The rest of the chapters explore the development features in SharePoint such as web part, custom master page, BI, etc. The technical coverage is not so deep, so it’s a good start for somebody new in the topic. It covers almost every aspects of SharePoint development.
  2. Microsoft.SharePoint.2007.Development.Unleashed  (SAMS), written by Kevin Hoffman and Robert Foster. This is definitely not for SharePoint novice. The first chapter starts directly with SharePoint Object Model! The authors made a good job on API and custom web part coverage. If you are looking for a deep exploration of every API classes, this is the best book I’ve red.
  3. Pro SharePoint Solution Development (Apress), by Ed Hild and Susie Adams. This is also not for novice. It gives very good exploration on how all of those Microsoft Office products work together with SharePoint. The idea is how to utilize the Word, Excel, PowerPoint, and Outlook (oh, of course 2007 version) as a client tools and connect to SharePoint as a portal. I like to read chapter 14 about custom workflow. Really shows the power of WF, InfoPath, Visual Studio and SharePoint.
  4. Workflow in 2007 Office System (Apress) by David Mann
    The book is specializing in Sharepoint Workflow. This is definitely the best book in the subject, maybe because it’s the only one that available now :). It covers everything about developing workflow in SharePoint 2007. Starting with Out of the Box workflow, SharePoint Designer, and custom workflow with Visual Studio 2005. Another good point is there are some chapters on Windows Workflow Foundation itself. All scenario in workflow implementation are covered, even how to create .aspx page as a workflow form inside SharePoint.
  5. This book is still in printing out now, but and I’m sure it’s the best book in SharePoint content management. Written by SharePoint MVP Andrew Connell, he’s finally made the book happen. I’m waiting the one available here in Singapore in couple of month The book is already available in Singapore, check it at Compbook Funan mall. Read his announcement here and review the table of contents.
  6. Real World SharePoint 2007 (a.k.a SharePoint MVP books). I haven’t read this book, but the title and the authors look promising. I skimmed the book in the bookshop several months back, and looks very in depth on every topics. It’s a level 300-400 book, for both developer and administrator. There are a lot of tips and real world tricks inside. Hopefully I can pick one copy and start reading.