2007-12-13

Workflows will fail if you have a task list with over 2000 items

Såg detta inlägg på msdn-forumen, vet inte värdet av inlägget då det är lite svårt att kontrollera källan men det kan i alla fall vara värt att läsa.

Själva inlägget handlar om samma område som jag bloggat om tidigare

Kritisk info angående sharepoint workflows och loggning

Kritisk info angående sharepoint workflows och loggning (del 2)

Enligt skribenten är följande från Microsoft Customer Service & Support - SharePoint

"I investigated more into the queries of having more than 2000 items in the list & the associated issues, basically these point to having performance issue that affects responsiveness of our Sharepoint box.

Basically the explanation goes like this

1. To enumerate all the items in a list you basically do a select * from userdata where list = infopath
2. To enumerate a view such as allitems.aspx which is paged you have to do select top 100 * from userdata where list = infopath order by some column.
The first degrades linearly with the number of items because obviously more items are selected as part of the query and also becomes a network throughput issue.
The second, which is presumably going to be executed far more commonly also degrades linearly but uses comparatively more of the SQL CPU (as compared to the network or disk) because the query requires SQL to sort and then filter the items.
A final comment is that there is no hard limit and depending on the usage model of the users and the hardware you throw at it, you can likely go far higher than a couple thousand items. It becomes a balancing act. If you set up a server with a decent SQL box (e.g. a 4-way with lots of memory) and perfmon shows that the SQL CPU is fairly idle because of the usage pattern then having more items in a list, especially if that list isn’t? viewed/enumerated very often, isn’t going to hurt you much (because you have CPU cycles to spare). However in practical scenario you would see usage pattern the SQL CPU is already at or near being the bottleneck then adding more items to a list that is viewed frequently is going to put more pressure on the SQL CPU and cause problem

Our suggested value of 2000 comes as much from not wanting transactions which enumerate a given list to use a disproportionate amount of the SQL CPU such that other transactions which are well within suggested limits might not respond well because of a SQL bottleneck. Measured in isolation you will find that enumerating a list with thousands of items can be reasonably fast and even under load can maintain a decent throughput rate up to a large number of users. But, under such load, the SQL CPU would be flat out pegged so there is no room on the box to do anything else and that is the problem we want to avoid by suggesting/recommending the above value."

1 kommentar:

Mattias Lindberg sa...

Den bästa artikeln kring detta ämne är Working with large lists in Office SharePoint Server 2007 (http://go.microsoft.com/fwlink/?LinkId=95450&clcid=0x409).

Sedan tycker jag kanske inte att ditt (eller den andra killens) rubrik är helt korrekt. Det är möjligt att problem uppstår pga tabellåsningar vid 2000 items (jag har inget konkret information själv), men jag tycker inte att det finns något stöd för detta påstående i texten.