What are the components of an SQL Server? The SQL Server Database Engine consists of four fundamental components. Regardless of how we deploy our SQL Server, we should expect these components to be active. They may not be exposed to administrators in all cases, but they help SQL Server operate.
Now, let’s analyze in detail what are the components of SQL Server.
① Protocol or Network
This is the section that allows us to connect clients to SQL Server. We can monitor network traffic to understand when network-related issues may affect performance. We also monitor network activity for signs of issues related to how applications or users interact with the server. A frightening example could be a distributed denial of service attack on a website that depends on the database server.
SQL Server supports different protocols and uses Tabular Data Stream (TDS) for communication over the network. In most practical cases, we will use TCP/IP to handle SQL Server connections. Other supported protocols are Named Pipes and Shared Memory, where the Shared Memory protocol can only be used by clients running on the same host as the SQL Server instance. Named Pipes are rarely used, suitable for a local area network (LAN), and the more the LAN is distributed, the lower the efficiency.
② Storage Engine
The SQL Server Storage Engine handles tasks such as transactions, file management, and accessing various database objects. Without a storage engine, the database cannot be transactional or concurrent, nor can it persistently store data. We monitor storage engine metrics, including storage capacity and performance, file access, and storage allocation.
③ Query Processor
The Query Processor handles and executes queries. This is a rather complex engineering task, but at a higher level, its purpose is straightforward. It analyzes, plans, and executes queries sent to SQL Server by applications. We monitor Query Processor activity by analyzing the queries being processed, the time they take, and the resources they use. Collecting query plans used to execute queries and statistics used to generate query plans is also very helpful.
④ SQL Operating System
SQLOS refers to the SQL Server component that is similar to an operating system. There are many functions that make up SQLOS and other SQL Server components interact with SQLOS through APIs. SQLOS is responsible for tasks such as CPU scheduling, threads, memory management, logical I/O, and background processes. Some of the things that background processes handle include monitoring deadlocks, keeping an eye on available resources, and finding memory to release.
When we monitor CPU activity, memory allocation, higher-level blocking, and locking, we are monitoring things related to SQLOS. An interesting note about SQLOS is that it contains a complete memory manager. Many applications rely on the host operating system for this, but SQL Server handles its own memory management. This is relevant because the way memory is allocated and used is a key aspect of SQL Server performance monitoring.
The above is an analysis of the four components of SQL Server, which we hope can help you understand better!
