Dina Ayoub's Technical Blog - Bing and SharePoint topics

SharePoint Diagnostic Studio (SPDiag) has shipped!

2011-04-29T19:39:00Z

SharePoint Diagnostic Studio (SPDiag) has Shipped, and along with it SharePoint Admin Toolkit (SPAT) v2 for SharePoint 2010.

This is a very exciting day for our on-premise customers managing their own SP installations. SPAT brings a crucial set of tools for managing and pinpointing issues with SharePoint farms. With the inclusion of SPDiag, the set of tools have become even more powerful.

New in SharePoint 2010 Administration Toolkit 2.0, the SharePoint Diagnostic Studio 2010 (SPDiag 3.0) provides SharePoint administrators with a unified interface that can be used to gather relevant information from a farm, display the results in a meaningful way, identify performance issues, and share or export the collected data and reports for analysis by Microsoft support personnel.

As someone who works on SharePoint performance and reliability by monitoring farms in production, this tool has been the most useful invention to me since sliced bread! You can use its nifty interface to connect to the logging database of a certain farm, see charts of the availability and latency percentiles for that farm, investigate issues such as extra slow pages, catch solutions that have design flaws that degrade performance, all within a friendly UI that will show you both the data from the logging database as well as the ULS logs. I was personally not involved in the creation of this tool, so I can honestly objectively say: it's brilliant! If there's any interest, I might post some blog entries about reports I like especially much, or think are useful. Let me know if you'd want to read more about this.

There's also a post with a little bit more info on the SharePoint team blog by Bill Baer:

http://sharepoint.microsoft.com/blog/Pages/BlogPost.aspx?pID=971

Filtering on an Indexed field in a large list counts recycle bin items

2010-09-20T22:07:00Z

This is a behavior I thought I'd point out, in case anybody is running into it without knowing the reason, as it may not be intuitive... :-)

If you have an indexed field and are filtering by it, but it doesn't work even though you are positive the list doesn't have more than 5k (or whatever you set your list view threshold to be) items that fit that filter, then you might want to check your recycle bin for items. If the total number of items that fit that filter on the indexed fields, including the recycle bin items, is greater than 5k then you are hitting the list view threshold and may get an incomplete set of results, or none at all.

Items in the recycle bin aren't really deleted from the database, so they still count when the pre-scan query runs to figure out how many items need to be queried to find the ones you're asking for.

Hope this helps!

2010 Large Lists Documentation: white papers and blog posts

2010-08-23T21:16:00Z

I thought I'd make one post where I link to all the SharePoint 2010 large list documentation.. Hope this is helpful:

Large Lists help topic explaining the basic thresholds: http://office2010.microsoft.com/en-us/sharepoint-server-help/manage-lists-and-libraries-with-many-items-HA010378155.aspx?redir=0

Designing Large Lists and Maximizing List Performance: http://download.microsoft.com/download/7/F/0/7F069D0B-B6BD-4692-868B-E8555BB72445/DesigningLargeListsMaximizingListPerformance.docx

Capacity planning for a large scale document repository: http://download.microsoft.com/download/7/F/0/7F069D0B-B6BD-4692-868B-E8555BB72445/LargeScaleDocRepositoryCapacityPlanningDoc.docx

Programmatically Using the Object Model Override for the List View Threshold: http://blogs.msdn.com/b/dinaayoub/archive/2010/04/27/programmatically-using-the-object-model-override-for-the-list-view-threshold.aspx

Changing the List View Threshold and Other Resource Throttling Settings: http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-change-the-list-view-threshold.aspx

Temporarily disabling List View Threshold on a large list: http://blogs.msdn.com/b/dinaayoub/archive/2010/04/19/temporarily-disabling-list-view-threshold-on-a-large-list.aspx

Working with Large Lists in SharePoint 2010: http://blogs.technet.com/b/speschka/archive/2009/10/27/working-with-large-lists-in-sharepoint-2010-list-throttling.aspx

Filtering on an indexed field in a large list counts recycle bin items: http://blogs.msdn.com/b/dinaayoub/archive/2010/09/20/filtering-on-an-indexed-field-in-a-large-list-counts-recycle-bin-items.aspx

Content database size (200 GB) custom health rule

2010-05-26T00:00:00Z

Great post by Steve, on how to create a health rule to check your content database sizes, to help you make sure they don't exceed the 200GB limit:

http://blogs.technet.com/b/speschka/archive/2010/05/22/writing-custom-health-rules-in-sharepoint-2010.aspx

More SharePoint 2010 Capacity Planning Goodness: Lab study of an Enterprise Collaboration Environment!

2010-05-13T20:14:00Z

This is another one of the papers I've worked on, and now it is available for download! Actually this is the one I've worked on the most, and I'd love some feedback on it :-)

http://www.microsoft.com/downloads/details.aspx?familyid=9CF4FA6B-4C9C-4FCA-B9C9-4A4F724DF448&displaylang=en

The lab study should be read in conjunction with the technical study that it mimics. The files you should look at together are :

Production environment (technical case study): SPServer2010CaseStudyEnterpriseIntranetCollabEnvironment.docx
This document details the production environment at Microsoft that showcases a typical enterprise intranet collaboration environment. The document just contians information about the environment, but does not include conclusions or how you should project this onto your own data - it is there to serve as an example mainly.

Lab study: SPServer2010LabStudyEnterpriseIntranetCollabEnvironment.docx
Now this is where it gets interesting. This environment mimics the production environment above, with plenty of differences that are all listed in the document. Since it's a lab study, we were able to change an aspect of the environment while keeping other things constant to figure out things like: how do the web servers scale out? What about scaling up the web servers? And scaling out the database servers? How does SharePoint 2010 compare to SharePoint 2007 in terms of throughput for the web servers and database server(s)? This document attempts to answer these critical questions and provides some conclusions based on our observations, that might help you extrapolate what your farm may need to look like, from the other technical case study.

Thanks for reading!

Dina Ayoub

Program Manager on SharePoint, MSFT

Programmatically Using the Object Model Override for the List View Threshold

2010-04-27T17:50:00Z

In this post titled "How to change the List View Threshold and Other Resource Throttling Settings", I described why you might want to use the "List View Threshold for Auditors and Administrators", which is 20,000 by default. This limit is higher than the default 5,000 List View Threshold that gets applied to all regular users. If you decide you do want to use this higher limit for some custom code or application, then the following conditions must all be met:

The Object Model Override must be set to On.
The code being run must be run as a user with "Full Control" or "Full Read" permissions.

The code must explicitly request the override when using SPQuery, by setting the SPQueryThrottleMode.

If any of those conditions are not met, then the query will be subject to the regular List View Threshold, rather than the Auditor/Administrator one which is typically higher.

In this post, I'll describe how to go about doing this:

First, you have to make sure your Object Model Override is enabled (It is ON by default, so unless you've changed it, you are fine).

Next, you'll need to grant sufficient privileges to the account that will perform these queries ("Full Control" or "Full Read"). Here's how to do that:

In Central Admin:

- Click “Application Management”

- Click “Managed Web Applications”

- After selecting a specific Web Application, click “User Policy” in the Ribbon

- Click “Add Users”

- Select Zone (default: All Zones)

- Enter Permission level (either of these will suffice)

o Full Control

o Full Read

Optionally, you may want to consider changing the List View Threshold for Auditors or Administrators to a different number. For example, if you know you don't need all 20,000, then it would be a good idea to lower that number to make sure custom code does not accidentally get written inefficiently without being noticed.

Now you're ready to use the List View Threshold for Auditors and Administrators. To do so, running as that user will "Full Control" or "Full Read", you will need to run code that looks something like this:

SPQuery q1 = new SPQuery();

q1.QueryThrottleMode = SPQueryThrottleOption.Override;

rootweb.Lists["Announcements"].GetItems(q1)…

There are 3 different options for SPQueryThrottleOption:

Default – Normal behavior where all users who are not web server box administrators will be subject to the List View Threshold, including users with "Full Read" or "Full Control" permissions. This is the default behavior unless otherwise specified.
Override – if user has “Full Control” or "Full Read" permissions, the List View Threshold for Auditors and Administrators will apply to this SPQuery, and List View Lookup Threshold will not be applied. For more info on what the List View Lookup Threshold does, read this post.
Strict – List View threshold will apply for everyone, including web server box administrators. You can use this option to make sure that your code does not cause server stress even if it is being run as the box administrator on one of the web servers, since box administrators are not subject to the thresholds so may inadvertently slow down the servers.

Thanks for reading, please let me know if you have any questions.

Dina Ayoub

Program Manager on SharePoint, MSFT.

SharePoint 2010: How to Create a View

2010-04-23T06:36:00Z

If you're new to SharePoint, maybe this will help smooth that along:

1- Go to the List or Library that you want to create a view in.

2- In the ribbon at the top pick "Library" or "List"

3- Click the "Create View" button

4- Now you can pick what type of view you'd like. You can copy an existing view that you just want to tweak without changing the original view, or you can create one of these formats:

Standard View

Calendar View

Access View

Datasheet View

Gantt View

Custom View in SharePoint Designer

Let's say for example, that you just want a standard view, so you click on Standard view. Now you need to think about some things:

What do you want to name your view?

Do you want it to be a personal view (only you can see it) or a public view (everybody can see it, and you need to have certain permissions to be able to do this

What columns do you want to see on the view? One common mistake is to pick everything, but matter of the fact is: usually you'll only ever really look at a few, so you should think about this carefully both during creating a view as well as after you've created it and are using it. Don't hesitate to come back to edit the view after you start using it when you realize that there are these 2 columns on it that you never really look at.
Make sure your view includes the minimal number of lookup columns (fields of type Lookup, Person/Group, or Workflow Status), because each lookup you add to your view incurs a cost, and once you go beyond 8 (default, your farm administrator may have changed this number) your view will just stop working and require that you change it to get rid of one or more of these fields.

Do you want to group your view by a certain column? For example, if you were to create a view on a list of all your tasks, you may want to group them by due date.

Do you need to sort the view in a particular way? If so, how, and is it necessary? If the list is large, this will have a performance implication.
Do you need to display any totals on your view, and if yes - how many items are in the list? If it's more than a couple of thousand, you have to think about whether that is necessary, because it will impact your experience negatively as the view will become significantly slower. For more information on how or why this happens, you can read this blog post Consider performance when using totals in SharePoint views (http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=295)

How many items do you want your view to return, and should it page them or do you just want to see the top X? If you don't think you'll ever go to that 2nd or 3rd page, then it's best to limit the returned set as much as possible.

Do I need to see the items inside folders, or do I just want everything bubbled up into my view?

Will I need to use this view on the go? If so, you want to make sure you think about the Mobile settings for whether to enable the view for mobile access, and how many items it should display in a list view web part.

Now that you know all the things to think about when creating a view, here's an example:

Task: Let's say that the default view with all items is not good enough for my department, and I need to make a view that everybody will find useful, instead of expecting each person to create a personal view, or filtering on the default view each time. I need a view to show all the items in this task list that were "Assigned To" whoever is using that view. I want it sorted by "Due Date", soonest first, and I want to see 100 items in a page instead of the default 30, and there are a total of roughly 500 items in the list but less than 100 of them belong to any particular person at a time. I also want to group these documents by 'Client Name', and I want to see a total on the page for "Amount Due". Here's what I'd do:

I'd Create a View, which I'll call "My Assignments"

I'd make it a public view, and I will also make it the default view.

I'll choose the following columns to display: Task, Contact Person (Lookup), Contact Number, Due Date, Amount Due

I'll specify that the view should group by 'Client Name'

I'll specify that the view should include a total on Amount Due
I'll add a filter for "Assigned To = [Me]"

I don't have folders, so doesn't matter if I set folders to show up or not.

I'll sort by Due Date, ascending.

I'll set Item Limit to 100, and disable paging. I just want to see the top 100.

I want to be able to see this view on the go, so I'll leave Mobile enabled

All done! Test it out.. works, get somebody else to try out the new default view.

Dina Ayoub

Program Manager on SharePoint, MSFT

SharePoint 2010: How to Change the List View Threshold and Other Resource Throttling Settings

2010-04-23T06:13:00Z

One of the major reasons that this List View Threshold (LVT) feature was created is to protect the server from unintentional load that may either bring it down, or at least cause other users higher latency or failures. Changing this limit (default 5000) is quite simple, but I wouldn't recommend it unless you are positive that it will not negatively affect your system. One valid example of when you might want to do this is if you are using your farm to serve heavily cached content, that only gets updated once a day, and do not want the limit to apply for that. Even in that case, I'd recommend that you test this thoroughly before changing it. There's an awesome white paper out there that describes in full details what effects this has on the server, with a lot of pretty graphs and such to depict the performance implications. Here it is: Designing Large Lists and Maximizing List Performance (http://technet.microsoft.com/en-us/library/ff608068(office.14).aspx). Also here's a link to the help topic that explains the basic limits and what they mean: http://office2010.microsoft.com/en-us/sharepoint-server-help/manage-lists-and-libraries-with-many-items-HA010378155.aspx?redir=0

If you've got your mind set on changing the LVT or another resource throttling setting, here's how to do it:

1- Login to Central Admin

2- Go to Application Management -> Manage Web Applications

3- Pick the Web application for which you want to change the LVT (If you only have 1 web app plus the central admin one, the one you want to pick is the 1 web app; changing this for the central admin does you no good)

4- In the ribbon above, click General Settings. That will bring down a menu, from which you should pick Resource Throttling

5- Change the LVT (first item in this list) to another value and press OK, but please try to keep it to a reasonable number!

Following those steps will take you to the page where you can also edit a bunch of other settings. Here's a list of them, and a brief description of what they do and best practices or recommendations on how to set them:

- List View Threshold for Auditors and Administrators: This is by7 default a "higher limit". Queries that are run by an auditor or administrator that specifically (programmatically) request to override the LVT will be subject to this limit instead. It's 20,000 by default as opposed to the 5,000 for the LVT. I wouldn't raise this past 20,000 for the same reasons of not raising the LVT. If you'd like to read more about how to use this, take a look at this post.

- Object Model Override: If you commonly use custom code on your deployment, and have a need for overriding the LVT to a higher limit, then it may be a good idea to allow the object model override, and give auditor or administrator permissions to the application that will perform the queries. This setting is on by default, but you may disable it if you do not need it. A good example of when you might want to use this is if you've implemented some code that will perform caching of a larger set of results that are accessed often for, say, several minutes. If you are not planning on caching the content, and are planning on running these queries often, then I wouldn't recommend using this method to get around the LVT as it will adversely affect your server's performance. In short: "tread lightly". If you'd like to read more about how to use this, take a look at this post.

- List View Lookup Threshold: This feature limits the number of joins that a query can perform. By number of joins, I mean the number of Lookup, Person/Group, or Workflow Status fields that are included in the query. So for example, if you have a view that displays 6 lookup columns, and filters on another 3 distinct lookup columns then by default that view won't work, since the List View Lookup Threshold is 8, and the view is attempting to use 9 lookups. I would recommend that you do not increase this number beyond 8, because through thorough testing we've observed that there's a serious non-gradual performance degradation that shows up above 8 joins. Not only does the throughput that the server can handle drop significantly at that point, but the query ends up using a disproportionately large amount of the SQL Server's resources, which negatively affects everybody else using that same database. If you'd like to read more about this, take a look at the "Lookup columns and list views" section of this white paper: http://technet.microsoft.com/en-us/library/ff608068(office.14).aspx

- Daily Time Window for Large Queries: This feature allows you to set a time every day where users can 'go wild'. Some people call it "happy hour", but I really think it would be a very unhappy hour for the server so I avoid that terminology :-). There are a few things that you should carefully consider before deciding what time to set this to:

It should be an off-peak hour, or at least a time during which you expect the least load, so as to affect the least number of individuals. If you pick the time to be in the middle of the work day for the majority of your users, then even those who are not using the large list may be affected negatively.
Try to keep it to a reasonable timeframe such that people can actually use it to fix their lists, rather than bug the farm admin (possibly you!) about it. If, for example, you set it to be "2-3 am", then it's unlikely that the users will be very happy about that. They won't want to wake up at 2 am just to delete this large list they no longer need, so they're more tempted to ask the farm admin to handle it for them.

Remember that operations started during the window won't just abort once the window ends.. So if your window lasts till 9am, and at 9 you need the server to be crisp and clear because you get a huge load spike, people who started their list delete at 8:59 may negatively affect that experience.

Consider different time zones. This is especially important if your organization or customers (if you're hosting SharePoint for others) are heavily geographically distributed. Setting it to 6pm may seem like a good idea for your own location, but would not be great in say, Sydney, Australia.

- List Unique Permissions Threshold: This is the number of unique permissions allowed per list. If you have a folder that you break inheritance on for permissions, and set some permissions for it (and all the items inside it), then that counts as 1 against your List Unique Permissions Threshold. Unlike the LVT and other settings, this threshold is not triggered by viewing the content or performing some other operation on it, but explicitly when changing permissions. If you can afford to, then I would recommend reducing this number. It defaults to 50,000 and that is a lot of unique permissions! Your list is very likely to encounter problems with permissions before it reaches this number, so preemptively tweaking it to what might work in your environment is a good idea.

Thanks for reading.

Dina Ayoub

Program Manager on SharePoint, MSFT

SharePoint 2010 Capacity Planning and Capacity Management

2010-04-22T23:36:00Z

The capacity planning and management center is up and running. It has a ton of great documents on it, it's very exciting! Check it out:

http://technet.microsoft.com/en-us/sharepoint/ff601870.aspx

Here's a quick look at the documents I found most useful:

A great overview document describing the process you should go through the planning exercise for your SharePoint 2010 environment, whether that is a brand new environment or an upgrade, or just if you need to learn how to evaluate if sharepoint is the right solution for you.

The software boundaries and limits document, which lists all the limits that you might care about when considering SharePoint 2010 as the solution for your organization.

Technical case studies - these are case studies that document a lot of information about the live environments that use SharePoint 2010 within Microsoft.

Storage and SQL Server capacity planning document - this is a great place to go if you want to learn how to plan for and manager your SQL Server with SharePoint 2010

If you share your feedback on the documents with me, I'll make sure to pass it along to the authors, so please feel free to comment :-)

Dina

Temporarily disabling List View Threshold on a large list

2010-04-19T23:02:00Z

I made a post on the official blog of the Microsoft SharePoint Product Group about how to temporarily disable the large list "List View Threshold" feature on a large list. The post talks about your options for performing throttled operations on large lists, and why you might need to (for example - right after upgrade). Please go take a look at it and feel free to leave me feedback either here or there. Here it is:

http://blogs.msdn.com/sharepoint/archive/2010/04/23/temporarily-disabling-list-view-threshold-on-a-large-list.aspx

Dina