A detailed Guide on sql query Optimization This article was published as a part of the

Completion time: The exact time at which the query returned the result is termed Completion time.
By analyzing these times, we can get a clear picture of whether the query is performing up to the mark or
not.
2. Statistics IO: 
IO is the major time spend accessing the memory buffers for reading operations in case of query. It
provides insights into the latency and other bottlenecks for executing the query. By setting STATISTICS IO

---

ON,  we get the number of physical and logical reads performed to execute the query.
SET STATISTICS IO ON
SELECT * FROM SalesLT.Customer;
Logical reads: Number of reads that were performed from the buffer cache.
Physical reads: Number of reads that were performed from the storage device as they were not available in
the cache.
3. Execution Plan: 
An execution plan is a detailed step-by-step processing plan used by the optimizer to fetch the rows. It can
be enabled in the database using the following 
procedure
. It helps us to analyze the major phases in the
execution of a query. We can also find out which part of the execution is taking more time and optimize
that sub-part.
SELECT p.Name, Color, ListPrice FROM SalesLT.Product p INNER JOIN SalesLT.ProductCategory pc ON
P.ProductCategoryID = pc.ProductCategoryID;
As we can see above, the execution plan shows which tables were accessed, what index scans were
performed for fetching the data. If joins are present it shows how these tables were merged.
Further, we can see a more detailed analysis view of each sub-operation performed during query execution.
Let us see the analysis of the index scan:

---

As we can see above, we can get the values of the number of rows read, the actual number of batches,
estimated operator cost, estimated CPU cost, estimated subtree cost, number of executions, actual
rebinds. This gives us a detailed overview of the several cost involved in query execution.