Ideally when you execute a SQL Query with conditions, it will be executed in the following sequence.
FROM
ON
JOIN
WHERE
GROUP BY
HAVING
DISTINCT
ORDER BY
TOP
SELECT
But in this case we will see how simple SELECT/DML queries run at architecture level.
Part one: Common for all the queries:
When any user ran any query it will pass through Network where it will create TDS packet and use TCP/IP PORT with SQL default port number 1434.
When a new request reaches the server and the task is created to handle that request, in PENDING state. At this stage the server has no idea yet what the request actually is. The task has to start executing first, and for this the engine must assign a worker to it. You can see the list of tasks in the server by querying sys.dm_os_tasks.
Workers are the thread pool of SQL Server. A number of workers is created initially at server start up and more can be created on-demand up to the configured max worker threads. Only workers execute code. Workers are waiting for PENDING tasks to become available (from requests coming into the server) and then each worker takes exactly one task and executes it. The worker is busy (occupied) until the task finishes completely. The lists and state of workers inside SQL Server can be seen by querying sys.dm_os_workers.
After worker picked up the task, it will go to task execution where query get Parse can say check if the request is correct or not. If request/query get incorrect, it will kick back to user and throws error. Once Query get Parsed successfully it will get compile and optimize the query where it SQL will give the best optional plan to execute the query. After that query will go to Query execution, where it will check if that query already present in execution buffer cache, if its already present then it will get the data/result from buffer pull cache where it was stored during previous execution for that same query.
Part two: for SELECT queries
If it is a new query then it will create new execution plan and will check in storage engine for access method and check with Buffere manager. Buffer manager will check with Data Cache if previous data/result already present or not. If yes, it will give result. If no, it will check into database with data file and get the data/result and provide it back to user. Once user get the output, worker return to Idle and new worker comes in picture with new task.
Part three: for Insert/Update/Delete queries
Up to part one it will be same flow. When query execution plan get created and will go to Transaction manager where it will work with Log manager which it deal with Transaction log and data cache. Which store the uncommitted transactions called dirty pages. Once check point or commit transaction get occurred it will do required changes in database Data file. If begin Transaction used in our query and not committed, then Lock manager will create log on that object and once commit executed, lock get release.
