Troubleshooting high disk read - buffer cache query I have a table tbl_Table1 that is about 5 GB in size and has millions of rows.

It has these columns:

ID, ContractID, Person, Address1, Address2, and many more.

One clustered index on the PK, ID.

I then have a query like this:

SELECT * FROM tbl_Table1 WHERE ContractID = 2000


So this query isn't going to be using an index, so will do a full table scan. 

Will this table then be kept in the buffer cache after this query is executed? Or will it continually read from disk?

I understand the answer is to add an index, but I want to know what happens in terms of buffer cache.

Thank you
So this query isn't going to be using an index, so will do a full table scan


Yes it will definitely do table scan if there is no index.


  Will this table then be kept in the buffer cache after this query is executed? Or will it continually read from disk?


No whole table might or might not be kept into buffer cache depending on size and whether SQL Server finds all such pages during read ahead reads. SQL Server would try to bring as much page as possible which it can do with read ahead reads. The amount of read ahead pages SQL Server can read in enterprise edition is much more and efficient than standard edition. So its quite possible that SQL Server would read not only page that holds row but more such pages so it might not do any Physical reads but since most pages which belong to table are bought into memory (by read ahead) and since index is not present SQL Server will do some good amount of logical reads to find the row

Example: 

I run a query like below on Developer edition which is same like Enterprise edition. Table T1 has no index

set statistics IO ON
set statistics TIME ON
select * from T1 where col1 =2


you would see following in message section of output


  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time =
  0 ms.
  
  (1 row(s) affected) Table 't1'. Scan count 1, logical reads 164,
  physical reads 0, read-ahead reads 170, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.
  
  SQL Server Execution Times:    CPU time = 15 ms,  elapsed time = 134
  ms.


The table structure is almost same as your table and query as well. So you can see SQL Server did 170 read aheads and 164 logical reads. 170 read ahead brought all the pages that were associated with table now since all pages were in memory SQL Server scanned all pages to locate the row doing 164 logical reads.

Remember every read is logical read, physical reads are just there to show that page was brought into memory using physical I/O and read ahead reads do not count under physical reads.