When you
have a lot of data and/or are executing complicated SQL against it, performance
matters. There is a whole science behind understanding how to see how SQL
Server is attacking the problems posed by executing queries, as well as helping
SQL Server perform the work efficiently.
To start
with, SQL Server has a relationship engine that does the work to parse, bind,
and optimize SQL statements that it needs to execute. Parse means to check for
the syntax of the SQL and create a parsing tree. Bind means to resolve object
names and cerate an algebraic tree. And optimization is to find an execution plan
to run the query.
There is a Query Optimizer used by SQL Server
that is cost based. Each plan to execute a SQL statement will assign a cost.
The cost is calculated based on the algorithms used by the query and an
estimate of the rows involved. These row estimates are based on statistics that
are gathered on the data.
