Why does it take so long to calculate an Execution Plan?

Question

I have a problem ,when rebuild or update statistics is complete for some tables after that at first time when i run my query(select query) ,it runs too slowly and duration of this query is about 30 min ,but after that when i run it again duration is about 3 sec ,how to solve it ?

i set statistics IO on query and got this results :

--Second Time

Table 'table1'. Scan count 42, logical reads 963118, physical reads 0, read-ahead reads 274, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--First Time

Table 'table1'. Scan count 42, logical reads 977019, physical reads 1143, read-ahead reads 946797, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

30 minutes is a bit extreme for just the plan, so it's quite likely that it's actually choosing a suboptimal plan, but doesn't know until the second time. Might there be a table that hasn't been updated? Possibly index hinting in the query would help, or adding a slightly different index. We need more information to be able to tell, however. Also, how long between runs does this happen? — Clockwork-Muse

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2018-12-09T14:42:02

Why does it take so long to calculate an Execution Plan?

Why do you think it's calculating the execution plan?

The "First Time" it's reading from disk: read-ahead reads 946797.

A read-ahead read may be as big as 512bytes, so 946,797 read-ahead reads in 30min is an average of 500 IO/sec and as much as 260MB/sec, either one could be as much as your IO system can deliver. So those read-ahead reads might account for the full 30min execution.

The second time the data is almost all in memory.

Why does it take so long to calculate an Execution Plan?

1 Answers