SQL Server Context Series II Framework and configuration

Database objects

The database maintains a series of tables that store all objects, data types/constraints/configuration items/resources, etc., in 2008 kinds we call them system base tables, and these tables are invisible to us by default. We can log into the database through the administrator and execute the script:

Use master; SELECT name from sys.objects WHERE type_desc = ' system_table ';Can see dozens of data. In general, we are not able to see any data in these tables, only the data can be viewed through a DAC connection to the database. This data is primarily used by the database engine. catalog view

What is the directory view, the catalog view as a regular interface used to maintain the original data of the system. All catalog view names are started with the Sys. For example, sys.objects;

SELECT * from sys.objects; As we can see from the table, the defined system tables and user tables as well as the foreign keys, constraints, etc. of the tables are included. For the sake of visualization, we create two tables: CREATE TABLE Hvi_customer ( ID int NOT NULL identity (1, 1) primary key, [name] nvarchar () NOT NULL )   CREATE TABLE [Hvi_order] ( ID int NOT NULL identity (1, 1) primary key, Createtime date not NULL, cus_id int NOT null foreign key references Hvi_customer (ID) ) 　　Execute SELECT * from sys.objects where Type_desc <> ' system_table '. Results　　we can see that the external primary key we define is an object instance. System functions

System functions are also called attribute functions, and property functions provide values from database objects to some of our properties. The following system functions are included in SQL Server 2008:

serverproperty ColumnProperty Databaseproperty Databasepropertyex Indexproperty Indexkey_property ObjectProperty Objectpropertyex Sql_variant_property Fileproperty Filegroupproperty Typeproperty Connectionproperty Assemblyproperty 　　For example, we want to see the Recovery property of the msdb database, executable statement select DATABASEPROPERTYEX (' msdb ', ' Recovery '). The results are as follows: Then we look from the sys.databases: SELECT name, Recovery_model, Recovery_model_desc from sys.databases. The result is as follows: You can see that the value of the recovery property in msdb is simple by querying the system function DATABASEPROPERTYEX. The system functions also provide some abbreviation functions that allow us to access the catalog view. For example, the DB_ID function looks at the database ID by inserting the database name. Use the following statement to view the database ID SELECT database_id From sys.databases WHERE name = ' Heavi_case1 '; 　　Also we can use the DB_ID function to view the database ID: 　　 SELECT db_id (' heavi_case1 ');Database Engine Components

First, let's look at a diagram to see which components are included in the database engine.

　　

It can be seen that the database four main components are query Processor (processor), storage engine (Storage engin), Database operating system (SQLOS), protocol layer (Protocol layer, not shown in the diagram).　　What is the use of these four components respectively? (1) Protocol layer: The received request is translated into a table so that the query processor can handle it. You can also return the final execution results to the client; (2) query Processor: Convert, compile, optimize, process queries, (3) storage Engine: If the requested SQL contains data, it needs to be saved to the storage engine.　　Manage all data access. (4) Sqlos: Usually perform system functions, such as thread management, synchronization mechanism, deadlock processing, memory management, cache management, and so on, and then describe these four components separately. Database protocol

The database provides the programming interface API through the protocol layer, and the data transfer format uses the Microsoft custom table data stream packet (tabular data stream (TDS) packet), and the client also interacts with the data through the TDS packet and the database. The database contains the following protocols:

1) Shared memory: the same 2) Named Pipes: The protocol used under local area networks (LANs). A named pipe protocol can pass messages from one process to another.　　And the two processes can be on the same PC or on a different PC.　　3) TCP/IP: The database uses the widest range of protocols. 4) Virtual Interface Adapter (VIA) query optimizer (query Optmizer)

The query optimizer optimizes the processing of query statements from memory, CPU, I/O operations, and examines the number of times the data table is affected, the lookup table index and associated columns, and so on.

Storage Engine (Storage engin)

The storage engine consists of three parts: Access method, lock and Thing service, tool command.

(1) Access method: You need to use the access method when locating the data in the database. The access method then previews the data page and index page, returning the queried data to the query processor. Similarly, the access method also receives data from the client, opens the table, and then updates the data.(2) Service of things: saying things will have to say the acid properties of things. Atomicity: atomicity. Either commit, or neither commit; consistency, consistency; isolation: isolation; Durability: persistence. The database exception occurs when the transaction is being executed.　　Then the transaction will not execute. (3) Lock operation: It is the key function of multi-user database, which ensures the synchronization of data from different isolation levels; Sqlos (SQL OS)

Sqlos runs at the bottom of the database engine, and planning and memory management are the most important two functions of sqlos. Other features include:

(1) Synchronizer: The synchronization object includes mutual exclusion lock, read-write lock, rotation lock, etc. (2) Memory Manager: Allocates memory for different component, (3) database exception handler: Resolves disposition user error, (4) Deadlock detector: does not resolve deadlock but check deadlock condition; (5) I/O Asynchronous Processor: guarantees that the system can continue executing when the I/O operation is requested; the NUMA framework for SQL Server 2008

NUMA (non Uniform Memory access Architecture) is called a non-uniform RAM access framework. The NUMA framework benefits are quantifiable, follow the symmetric architecture (SMP), all memory accesses pass through the same memory stack, and this mode works fine if the CPU is relatively young. However, if multiple CPUs have centralized access to a single system stack, the performance is greatly impacted. NUMA limits the number of CPUs in a single memory stack, with each set of processes having its own memory and I/O channels. Each group is called a NUMA node, and the nodes interact through a high-speed intra-connector, with each node having a CPU count dependent on the specific hardware device. The NUMA framework structure diagram is as follows:

　　

Memory Management

　　By default, SQL Server 2008 is almost completely dynamic in managing memory resources. The database is almost always interacting with Sqlos when allocating memory. So Sqlos is the underlying component of the database that is very important.

buffer pool and data cache

　The primary memory component of the database is the buffer pool. All memory in the cache pool is used as a cache of page data that is read from disk. The cache manager manages disk operations, saving data and index pages to the data cache so that data can be shared by users. Requests from the cache pool when other components require memory. A slow-to-exist database is also stored as a page, with the same dimensions as the data and index pages. You can understand that a buffer is a framework for working with pages in a database. Many buffers reduce the cache pool of other memory components and then turn into various memory caches. For example cache stored procedures and execution plan caches we are often called plan caches.

accessing memory data pages

the speed of accessing the pages in the data cache must be very fast, and the pages in the data cache are quickly accessed in hash form. The hash table contains a set of pointer data mapped to a real buffer page. If all pointer mappings cannot be placed on a single hash page, these pointers are concatenated into a hash page. The primary key of the hash is generally a DBID-FI leno-pageno identifier (database ID, file number, page number combination), the hash table maintains an index and Special page Association relationship. The hash method allows the database to quickly find special data pages from memory, even if there is a large amount of memory data. Similarly, just a small amount of memory read can determine whether the desired page exists in the cache and whether it needs to be read from disk.

Manage pages in the data cache

You can use a data page or an index page if it exists in memory. Therefore, a buffer must exist in the data cache to read the page. Maintaining a valid cache of instant provisioning is an important performance optimization. If a buffer does not read effectively, many memory pages have to look for a buffer to empty to use as a workspace.

SQL Server 2008 provides a single mechanism for saving page changes to disk, marking pages that have not been referenced for some time. The database maintains an empty page address list. Any worker can get an empty cache page from the first page of this list.  The memory of this section will continue to be updated ... SQL Server Contextual Series directory structure:

One of the SQL Server contextual series catalog

SQL Server Context Series II Framework and configuration

SQL Server contextual series three databases and files

SQL Server sequence of 4th logs and recovery

SQL Server ins and outs series five tables

SQL Server Contextual Series six index

SQL Server Contextual series of seven more special storage

SQL Server Contextual series eight query optimization

SQL Server Contextual series nine plan cache

SQL Server contextual series ten transactions and concurrency

SQL Server Context Series II Framework and configuration