If you have some experience working with databases, you may certainly used several SQL commands. Have you wondered what happens when a query is submitted? How does the query optimizer work ?
When a query is submitted to a SQL Server, a number of processes on the server are set into motion.
However, primarily it focuses on 2 stages:
- Relational engine processes.
- Storage engine processes.
In a RDBMS, in the first stage of the process, a query is parsed and then processed by the query optimizer, which generates an execution plan. The execution plan which is in binary format, is sent to the storage engine.
The storage engine then uses that plan as a basis to retrieve or modify the underlying data. The storage engine is where processes such as locking, index maintenance and transactions occur.
That's pretty much the gist of it.