No one likes to deal with a slow query. However, before you handle with a query, I suggest you understand which architecture it runs on, you can be sure it will clear up many doubts.
This is the second article in a series of articles on SQL Tuning, the idea is to provide a comprehensive overview of this subject.
This is the article 02 of 03:
- SQL Tuning — The Basics that Work
- SQL Tuning — SQL Server Architecture
- SQL Tuning — Analyzing and Improving Queries with Query Execution Plan
Before any changes, I want to emphasize that the recommendation is for you to read the query’s Execution Plan and understand why the slowness occurs. This way, we can take an initiative to solve the problem. In the third article of this series, I will guide you through step by step on how to analyze a heavy query.
• • •
Architecture
The SQL Server architecture consists of three main components:
- Protocol Layer
- Relational Engine
- Storage Engine
Architecture diagram:
Protocol Layer
The protocol layer is responsible for managing communication between clients and the SQL Server. It handles client authentication, establishing connections, and session management. Additionally, it translates SQL commands sent by clients into instructions understandable by the SQL Server engine.
The MS SQL server supports three types of client-server protocols:
- Shared Memory — In this protocol, both the client and the server run on the same machine, such as when you install the SQL Server on your own machine.
- Named Pipes — Here, the client and server are on the same local network. This protocol allows the client and the SQL server to communicate through a local area network (LAN).
- TCP/IP — Here, the client and server communicate via the TCP/IP protocol. This can be over the internet or on a local network. The client and server can be on different machines or locations, such as when you access the server from your home.
It’s important to note that all protocols use TDS (Tabular Data Stream). TDS is a communication protocol developed by Microsoft to facilitate communication between client applications and SQL Server database servers.
As you’ve seen, it’s used to send and receive data between the client and server during the execution of SQL queries.
Note: You can see, disable or enable these protocols in SQL Server Configuration Manager
Relational Engine
This is the heart of the SQL Server. It is responsible for processing and executing SQL query execution plans, performing queries, optimizing queries, and managing transactions. This is where we focus when we want to improve query performance.
The Relational Engine has three main components:
CMD Parser (Command Parser)
Here we have the first component of the Relational Engine. It receives the query from the Protocol Layer and checks if the query is correct (syntax errors, etc.). After analyzing the query, it will generate a Syntax Tree.
Query Optimizer
It is responsible for determining the most efficient execution plan for a SQL query. It uses the Syntax Tree generated by the CMD Parser to explore various strategies and generate different possible execution plans.
Query Executor
This is responsible for executing the execution plan selected by the Query Optimizer. It interprets the execution plan and manages the execution of the different operations necessary to process the query. This includes reading and writing data to disk, sorting, joining tables, applying predicates, and aggregations.
The query executor interacts with other components of the SQL Server, such as the Access Manager and the Buffer Manager, to access the necessary data and ensure the consistency of operations. When the query execution is complete, the query executor returns the results to the client.
Storage Engine
This mechanism is responsible for managing data storage and retrieval on disk. It translates logical operations (such as queries and updates) into physical read and write operations on the disk. Additionally, it manages data storage in pages and disk space allocations.
In this part, all the work of writing and retrieving requested by the Query Executor happens.
Access Manager
The Access Manager is responsible for controlling and coordinating access to system resources such as files, memory, and other devices. Here it manages access requests and ensures they are carried out securely.
This is also where commands that are SELECT and Non-SELECT are handled, with Non-SELECT commands being sent to the Transaction Manager and SELECT commands being sent to the Buffer Manager:
Buffer Manager
It manages the cache of data pages in the SQL Server’s memory. You know when you run the query for the first time and it takes a while? But the second time it’s faster? That’s this component at work.
It typically stores data pages that are accessed frequently, reducing the need for frequent reads and writes to the disk. It helps improve performance by reducing I/O time.
There are three main operations that happen here:
- Plan Cache: The Buffer checks if the execution plan is cached and can be executed.
- Data Parsing: The plan exists, and the Buffer enables access to the data.
- Hard Parsing: The data does not exist in the Buffer Manager and needs to be fetched from Data Storage (First query and takes longer).
Transaction Manager
This is the component responsible for coordinating and managing transactions in SQL Server. This includes ensuring that transactions are executed according to the ACID properties (Atomicity, Consistency, Isolation, and Durability).
Within the Transaction Manager, there are two main components:
- Lock Manager: Manages resource locks in the database to ensure data consistency and integrity. It coordinates the release and management of locks, ensuring that transactions are executed in an isolated and concurrent manner.
- Log Manager: Manages the transaction log of the database. It records all transaction operations in a transaction log, ensuring transaction durability and allowing recovery in case of system failure. The transaction log is also used to support undo and redo operations during transaction processing (BEGIN, COMMIT, ROLLBACK).
Overview
Let’s do an overview of what we’ve learned, imagine you just hit F5 on your query:
- The client sends a SQL query.
- The protocol layer receives the request and forwards it to the Relational Engine.
- The Relational Engine processes the query, optimizes it, and sends it to the Storage Engine.
- The Storage Engine then retrieves the necessary data from the disk and returns it to the Relational Engine.
- Finally, the results are sent back to the client through the protocol layer (Protocol Layer).
All of this process happens in seconds, it’s wonderful!
• • •
I hope this article can help you better understand the life cycle of your query, but remember: the best way to solve this problem is by understanding the Execution Plan and taking action on it. I’m looking forward to the next article, my friends!