execution plan cache

Question

question

cornpoppy asked Dec 9, '18

execution plan cache

Hi,

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 is too slowly and duration of this query is about 30 min ,but after that when i run it again it is about 3 sec ,how to solve it ?

set statistics IO on query and got this results :

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.

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.

sql-server dba performance-tuning execution-plan statistics

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cornpoppy commented · Dec 09, 2018 at 06:48 AM

i set statistics IO on query and got this results :

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.

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.

0 ·

Kev Riley ♦♦ commented · Dec 09, 2018 at 01:57 PM

Are you sure they are the right way round? I'd would have expected less physical reads on the second attempt

0 ·

cornpoppy commented · Dec 10, 2018 at 04:52 AM

I am really sorry, yes, you are right,and i will correct my question

0 ·

Answer 1 · 2018-12-10T05:18:46Z

Kev Riley answered Dec 10, '18

Your query is doing nearly 1,000,000 reads - each one of these is an 8KB page of data - so that's 7-8GB in total. All data access in SQL is done from memory, so if data is not in the data cache then it issues calls to read it from disk into memory, then it can be read from memory for your query.

The first time the query runs, it is having to go to disk to read all this data (hence the physical reads and read-ahead reads). The second time, most of this is already in memory, so the query is quicker.

So what can you do?

1. Reduce the amount of reads that the query needs to do. Is the query having to scan an entire table but only use a smaller amount of data - maybe there is a need for an index? Or a better index.

2. If the amount of data being read is needed, then look if there is anything you can do to make the reads from disk faster. Move data from slower disks to faster disks, for example.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cornpoppy commented · Dec 10, 2018 at 08:18 AM

Many thanks,I have to check my query indexes.

0 ·

question