Skip to Content
0
Jun 22, 2017 at 11:31 AM

Select on BSAD significantly longer fetch time in Prod than Pre-Prod

141 Views

Hi there,

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:

Prod

Operation = open, duration = 805,379

Pre-prod

Operation = open, duration = 5,202

Both are using index BSAD~5 (index seek)

BSAD~0 (clustered index seek)

DB = MSSQL 11.00.6579.00.