top of page

Clustered Index

 

 

 

 

 

 

 

 

 

 

 

Logical reads (논리적 읽기)
The number of 8K pages we read
ex) logical reads가 7405이면 -> 7405 * 8KB = 59MB

Using WHERE without a matching index means scanning all the data (and there are some extra reads when queries go parallel)

아래 사진과 처럼 execution plan을 읽을 땐 Right to Left, Top to Bottom.

Scan entire Clustered Index. It's estimation that was 96 percent of the work. And Parallelism work.

노란색 안에 화살표 두개: Parallelized the work. broken up across multiple people because sql server saw that this is an expensive query, lot of work to do.

 

 

 

 

 

 

 

 

 

 

 

 

Estimated subtree cost is a rough measure of CPU and IO work required for a query

(general measure of CPU and i/o work required in order to perform a query.
Higher that cost go, the longer that queries will take)

 

 

 

 

 

 

 

 

 

 

 

 

 

ORDER BY를 쓰게 되면
1. Shuffle through all the pages, writing down fields ___ for each record, if their LastAccessDate > '2014/07/01'.
2. Sort the matching records by LastAccessDate.

ORDER BY

Cost is up about 2x
We needed space to write down our results, so we got a memory grant

Sorting data is expensive, and more fiels make it worse

SQL Server caches data pages, not query output

from https://www.youtube.com/watch?v=ACzguQ-AT-c&list=PLGyh3dhppvKoEFryHIKmpOn_bwoIDSk0h&index=1

 

bottom of page