Performance degradation

Question

There are 2 SP farms in different data centers, similar design:

DC1

• 1 SP WFE: 15.0.4569.1000
• MS SQL: 11.0.3128.0

DC2

• 1 SP WFE: 15.0.4849.1000
• MS SQL: 11.0.6020.0

Due to need to migrate to DC2, the content DB was migrated from DC1 (attach, mount, updated index statistics, upgrade-spcontentdb).

When requesting a customized display form, the response time of ~3s in DC1 increased to ~15s in DC2.

There a few things to mention about the site collection:

1. list view threshold increased to 50k
2. a couple of lists already have ~40k items
3. indexed columns are used
4. lookup column threshold increased to 20
5. at least one list has 25 lookup and people and groups columns
6. unique permissions at item level
7. identical custom WSPs deployed to both DC1 and DC2

Despite of all the above, in DC1 response time is as low as ~3s. Surprisingly.

Debugging side by side, there are differences:

DC1

DC2

See those highlighted SQL commands starting with the following?

SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_Url) WHERE...

They are all over the place, poping up in the DC2 SP Developer Dashboard. No trace of them in the identical URL request on DC1.

IO statistics show some heavy weight reading in these SQL commands:

:Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.:Table 'AllDocs'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.:Table 'AllUserData'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.:Table 'AllLists'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.:Table 'Perms'. Scan count 1, logical reads 407021, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Also, the whole request lifecycle seems to be totally different:

So, what could be the cause of this performance degradation? Why the extra SQL queries in DC2? And why unique permissions seems not to affect performance in DC1?

Answer 1

Is there a possibility that the call to DC1 is made by an admin to a list with individual permissions and the call to DC2 is made by a non-admin?

The SELECT statement is normally made if the list has unique permissions in it, and the current user isn't an admin.