Practical App
Optimizing SharePoint Workspace Storage
You can still work with SharePoint even when you're working offline if you optimize your SharePoint Workspace storage architecture.
You can't always be connected to SharePoint. For those moments when you're not, SharePoint Workspace 2010 lets you work with SharePoint data -- even when you're working offline. The underlying storage architecture, however, has several performance problems. Here's a look at why these problems occur and how you can work around them.
SharePoint Workspace refers to the storage space used to store workspace data. The client software is also called SharePoint Workspace, so we'll call that SPW 2010. You can think of a SharePoint workspace as similar to a Microsoft Groove workspace.
The basic idea is that you can synchronize either a full or a partial SharePoint document library to a SharePoint workspace on your local computer. That way, you can still access the library's contents when you're working offline. You perform the actual synchronization with SPW 2010. This is the client component that lets you set up and maintain synchronizations with library content.
Why Optimize?
When you synchronize SharePoint documents, the documents themselves are stored in your local Office Document Cache. The related lists, InfoPath forms, schemas and views are stored in the actual SharePoint workspace. This architecture works well if you only need to synchronize a handful of documents. It can break down as the number of synchronized documents increases.
According to Microsoft, SPW 2010 functions best if you're synchronizing fewer than 500 documents. Once you try to synchronize more than 500 documents, you'll see a message warning you that SPW 2010 performance will gradually diminish as you increase the document synchronization workload.
If you try to synchronize 1,800 or more documents, SPW 2010 will give up trying to keep them all in sync. Instead, it will only download the document metadata to the SharePoint workspace. When you try to open one of those documents, it's synchronized on the fly.
So if it's more efficient for SPW 2010 to download document metadata instead of trying to synchronize an entire document, why doesn't SPW 2010 do this all the time? The whole point of SPW 2010 is to let you work on SharePoint data even when you're offline. It's hard to work on a document if all you have is the metadata.
Optimizing Workspace Storage
At the moment, there are really only two ways to work around these limitations. One is to create Groove workspaces instead of SharePoint workspaces. According to Microsoft, these synchronization limitations only apply to the Office Document Cache, and not to Groove workspaces (which you can create and access through SPW 2010).
The other option for working around the SPW 2010 synchronization limitations is to reduce the number of documents you're synchronizing. You have several different ways to do this.
One is to get rid of any unused workspaces. The efficiency of the synchronization process is based on the total number of documents across all workspaces. If you have 10 workspaces with 100 documents each, then SPW 2010 will have to synchronize 1,000 documents, even though each workspace falls well below the 500-document threshold.
SPW 2010 creates a new workspace for every connection to a library or folder within a library. Over time, you'll most likely accumulate a collection of workspaces you no longer use. Getting rid of these workspaces is a great way to reduce the number of documents SPW 2010 will have to synchronize.
You could also consider whether or not you really need caching for all your documents. Most of your mobile users would probably love to have offline access to SharePoint data when they're traveling. However, there's often a limited set of documents they really need. It's nice to have other documents available offline, but not truly necessary. Decreasing the number of cached documents can correct performance problems.
One more thing you can do to improve your users' experience is show them how to synchronize individual folders instead of entire SharePoint document libraries. When you create a SharePoint workspace, SPW 2010 asks for the URL associated with the document library you want to synchronize. You can actually append a file path to the URL.
For example, a server's Shared Documents library may contain folders named Finance, Marketing and HR. If you're a finance user, it wouldn't make sense to synchronize the entire document library because the other folders don't apply to you. In the real world, you may not even have access to these folders.
To create a workspace linked to the document library, use the following URL: http://SharePoint/Shared%20Documents/Finance. Doing so lets you create a workspace that includes only the Finance folder and anything beneath it. None of the parallel folders (HR and Marketing) would be included, nor would any documents residing within the Shared Documents folder.
When it comes to troubleshooting, begin your efforts on the client side. After all, because SPW 2010 experiences performance degradation after the 500-document threshold, there's a compelling case for client-side storage optimization. Optimizing the client side is only half the battle, though. Even though SPW 2010 is far more likely to experience performance issues, a SharePoint server can get bogged down with excessive synchronization requests.
Monitoring SQL Server
SharePoint stores site content within a SQL Server database. Therefore, if you want to optimize SharePoint storage architecture, you have to focus on SQL Server. Thankfully, Microsoft provides several Performance Monitors you can use with SQL Server.
These target values are specific to SQL Server 2008 or SQL Server 2008 R2 used by SharePoint 2010. The counter values do not necessarily apply to other types of SQL databases.
Microsoft recommends watching the General Statistics \ User Connections counter. This shows you the total number of user connections to SQL Server. The reason Microsoft tells you to watch this is because performance problems can occur if the counter increases to 500 percent of your baseline value.
Another counter is the Databases \ Transactions / Sec counter. Even though Microsoft doesn't provide any specific recommendations on what this counter should be, it does recommend you record its value while the server is in a healthy state. That way, if you experience performance problems, you can compare the number of transactions occurring each second to your baseline values.
Monitoring the server locks is one way to get a good feel for how well SQL Server is performing. There are several different lock-related counters you can monitor, but the most useful is probably Locks \ Lock Waits / Sec. This shows you how many locking requests are occurring each second that it can't immediately fulfill.
Monitor the Locks \ Average Wait Time (ms) counter if locks aren't occurring immediately. This will tell you how long the average wait time is for a lock to occur. If this value rises steadily, your SQL server is suffering from performance problems.
Just as monitoring locks can indicate how well your SQL database is performing, so too can monitoring latches. The latch-related counters you should watch are Latches \ Latch Waits / Sec and Latches \ Average Latch Wait Time. You're looking for the number of waits occurring each second to steadily increase.
You should also be on the lookout for excessive wait times. It's helpful to record a baseline value when the server is running well, so you'll have a basis of comparison in the event of future problems.
One last thing is to check out the speed with which SQL Server processes user queries. Look at the number of compilations and re-compilations occurring each second on the SQL Statistics \ Compilations / Sec and the SQL Statistics \ Re-Compilations / Sec counters.
These last two counters will be meaningless unless you use them to establish a baseline value when the server is functioning properly. After that, you can monitor the number of queries being compiled or recompiled. If these numbers decrease with no corresponding drop in user load, it means that your SQL server is having difficulty keeping pace with user requests.
The Performance Monitors are all generalized SQL Server counters you can use to gauge the overall performance of your SQL Server. If you discover any performance problems, there are also hardware-related counters you can use to further diagnose the issue.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.