By now you’ve heard that lists are more scalable. You’ve may have heard that you no longer have to worry about the 2000 item limits. What is the reality of this situation?
There are a number of improvements that you should understand that make these lists more scalable, but I find statements like “No more 2000 items per view limits” can be easily misunderstood. While it’s understandable that both the new schema for the content database around lists is more scalable, the throttling and query limits are part of the solution to scale. One side of the equation in scale is better performing queries, and the other is the safety controls around making sure people don’t shoot themselves in the foot by running queries that are slow or can impact the system.
The first option listed is to limit the maximum query size “list query size threshold.” This is where the user will get a message telling them to come back later if they want to run that query.
The second option is the warning level, which let’s them know they are getting close to the maximum.
What you’ll find is the throttling is in place to prevent users from doing “bad” things. Bad as in impacting SQL or impacting the performance of the rest of the system.
Essentially, while it’s true that scale is greatly improved, and the throttling is the hero, it still is possible to create poorly performing lists. The default 5000 query size is really a good number to work with as a maximum. If you find that is too slow, bring it down a couple thousand.
I am a fan of the Dev dashboard in cases like this which would help you determine if the query is the issue or if it is a webpart of the web server itself. Note in the screenshot below the database queries section that helps you identify the actual stored procedures and how long it takes to run.
You can easily turn on the developer dashboard (performance dashboard) by running a simple stsadm command:
There are 3 states: on, off or ondemand here’s the syntax.
stsadm -o setproperty -pn developer-dashboard -pv ondemand
stsadm -o setproperty -pn developer-dashboard -pv on
stsadm -o setproperty -pn developer-dashboard -pv off
You can use powershell as well, but I’ve found this to be a example or where it is actually pretty straight forward in stsadm.