Here I would like to describe the process architecture whan a new request submitted to SQL Server.
I have submitted a Query to SQL Server from an Application and I got the reply as “data inserted successfully”. What are the overall processes worked inside?
At Client:
1. User enter data and click on submit
2. The client database library transforms the original request into a sequence of one or more Transact-SQL statements to be sent to SQL Server. These statements are encapsulated in one or more Tabular Data Stream (TDS) packets and passed to the database network library
3. The database network library uses the network library available in the client computer to repackage the TDS packets as network protocol packets.
4. The network protocol packets are sent to the server computer network library across the network
At Server:
5. The extracted TDS packets are sent to Open Data Services (ODS), where the original query is extracted.
6. ODS sends the query to the relational engine
7. A connection established to the relational engine and assigns a SID to the connection
At Relational Engine:
8. Check permissions and determines if the query can be executed by the user associated with the request
9. Query sends to Query Parser
It checks that the T-SQL is written correctly
Build a Parse Tree \ Sequence Tree
10. Parse Tree sends to Algebrizer
Verifies all the columns, objects and data types
Aggregate Binding (determines the location of aggregates such as GROUP BY, and MAX)
Builds aQuery Processor Tree in Binary Format
11. Query Processor Tree sends to Optimizer
Based on the query processor tree and Histogram (Statistics) builds an optimized execution plan
Stores the execution plan into cache and send it to the database engine
At Database Engine:
12. Database engine map a batch into different tasks
13. Each task associated with a process
14. Each process assigned with a Windows Thread or a Windows Fiber. The worker thread takes care of this.
15. The Thread/Fiber send to the execution queue and wait for the CPU time.
16. The Thread/Fiber identifies the table location where the data need to be stored
17. Go to the file header, checks the PFS, GAM and GSAM and go to the correct page
18. Verifies the page is not corrupted using Torn page Detection / Check SUM and writes the data
19. If require allocates new pages and stores data on it. Once the data is stored/updated/added in a page, it updates the below locations
PFS – Page Free Space
Page Header – Checksum / Torn Page Detection (Sector info)
BCM – Bulk Change MAP
DCM – Differential Change MAP
20. In this process the
Memory manager take care of allocating buffers, new pages etc,
Lock manager take care of allocating appropriate locks on the objects/pages and releasing them when task completed
Thread Scheduler: schedules the threads for CPU time
I/O manager: Establish memory bus for read/write operations from memory to disk and vice versa
Deadlock\Resource\Scheduler Monitor: Monitors the processes
21. Once the process is completed the result set is submitted to the relational engine and follow the same process for sending back the result set to client application.
22. The connection will be closed and the SID is removed.
This information I have collected from various articles and reading through books online.
If someone wants to add / update can suggest / assist me on this.
dear Uday,
nice explanation but some one can better understand by a diagram ..if you have time please explain this by a figure.
Dear Ravi, Thanks for visiting our blog, wish you have a good time and happy reading.
You can get more detailed architecture from the link:
http://udayarumilli.com/sql-server-architecture-qa-3/
Thanks
http://udayarumilli.com/