question

cornpoppy avatar image
cornpoppy asked

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-serverdbaperformance-tuningexecution-planstatistics
3 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·

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

0 Likes 0 ·

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

0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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 comment
10 |1200 characters needed characters left characters exceeded

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

Many thanks,I have to check my query indexes.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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