Database performance tuning concepts in brief

Predicates are used to used to specify a subset of rows to be returned. They are specified in the WHERE clause of a SQL statement.
In the example below “Id = 1” is a predicate:

SELECT * FROM Employee WHERE Id = 1;

Estimated Execution Plan defines how a query should actually execute
Actual Execution Plan tells what happened while a query was executed.
Table Scan/Index Scan looks at each and every row available in the table/index
Index Seek has address of each row based on the key field value. So seek directly goes to that data page and fetches the row if your predicate matches with the key field.
Key Lookup, In case of Key lookup, SQL server storage engine has to go from non-clustered index to clustered index, in order to fetch the value of non-key field of non-clustered index. This round-trip always consumes time and decreases performance.

You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.