We've had a specific issue with a custom report that intermittently runs slower in production than pre-prod. The issue is a specific select statement on table BSAD. There is around x1.8 more data in prod than pre-prod, but the select statement shows x141 slower in prod than pre-prod.
The SQL trace appears to show the same indexes being used in both system and this is an intermittent issue. We don't see any link to wider system load, in fact one occasion was during a public holiday with virtually no users in the system.
My question is primarily whether the answer is there in the SQL trace results and we don't have the knowledge to spot it, or there is something else we can check - any additional logging at the basis layer for example that may give further clues?
The SQL trace shows:
Operation = open, duration = 805,379
Operation = open, duration = 5,202
Both are using index BSAD~5 (index seek)
BSAD~0 (clustered index seek)
DB = MSSQL 11.00.6579.00.