Low Level SQL Server Architecture

Low Level SQL Server Architecture

 udayarumilli_sql_server_2012_ssrs

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.
You can have a look at here for complete “Low Level SQL Server Architecture
Posted in Database Design, SQL Development, SQL Server DBA | Tagged , , , , , , , , | 2 Comments

Leave a Reply

2 Comments on "Low Level SQL Server Architecture"

Notify of
avatar
Sort by:   newest | oldest | most voted
ravi kumar
Guest

dear Uday,
nice explanation but some one can better understand by a diagram ..if you have time please explain this by a figure.

wpDiscuz