SQL Server Basic
SQL process
650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/87/F4/wKioL1fk86eD5RL_AAFeqBqTpX4092.jpg-wh_500x0-wm_3 -wmp_4-s_2816278903.jpg "title=" 1.jpg "alt=" Wkiol1fk86ed5rl_aafeqbqtpx4092.jpg-wh_50 "/>
TDS is a protocol, a series of rules that describe how data is transferred between two computers. Like other protocols, it defines the type of information transmitted and the order in which they are transmitted. In summary, the protocol describes " bits on the line ", that is, how the data flows. The Tabular Data flow protocol is built on top of TCP/IP net-library and is contained within a TCP data segment. TDS uses 1433 ports for database communication.
The TDS protocol is located within the data segment of TCP and is structured as follows:
Ip |
Tcp |
TDS Head |
DATA segment of TDS |
8 bit header
TDS clients use SQL Server dedicated application-level protocols called Tabular data Streams (TDS) to send SQL statements. The packet is generated by the Microsoft OLE DB provider for SQL Server, the SQL Server ODBC driver , or the db-library DLL . the TDS packet is then passed to the SQL Server client net-library, and the client net-library the TDs Packets are encapsulated as network protocol packets. On the server, the server net-library receives the network protocol Packet and takes the TDS packet and passes it to the relational database server. This process reverses execution when the results are returned to the client.
table Data Flow endpoint: once a network protocol is successfully connected, it creates a corresponding TDS Endpoint for transmitting information between client and server
for this step, performance issues may occur when the content of the request is converted to In the process of formatting that SQL Server can handle, if the amount of data is large, the conversion overhead is high, whether from an external incoming SQL Server or from SQL Server output to the outside.
command interpreter: in the After the TDS translates the request into a recognizable format within SQL Server, it is first uploaded to the command interpreter, which checks whether the incoming T- SQL syntax conforms to the rules. If the syntax conforms to the requirements, build the query tree in the incoming query optimizer .
The command interpreter also checks if a cached execution plan already exists, reuses it if it exists, and lets the query optimizer generate an execution plan if it does not exist
query optimizer: the component is At the heart of SQL Server, the optimizer's role is to perform a series of optimizations for a particular request to manipulate the data as efficiently as possible, ultimately choosing the lowest-cost candidate plan to generate an estimate execution plan and passing in the executor to execute the query.
Query Executor: The query executor is the component that executes the query, but it is not actually executed, but The OLE DB protocol accesses and uses a series of instructions to manipulate the storage engine to perform operations on queries. The storage engine is the actual query performer
data access methods: a data access method is a collection of code that provides storage, querying, and modification of data and indexes. It contains the code of the index operation data, but does not go to the data to manipulate the data, but submits the request to the buffer manager
Buffer Manager: the Buffer Manager is the management the components of the Bufferpool. Buffer Pool is a major part of SQL Server memory that manages data in memory and transmits the appropriate data as output to ( data access Methods ). If the required data is not in memory, the buffer manager needs to read the corresponding data page from the disk to the memory data cache, and then return the required data to the data access method. There may be a wait state in this process Pageiolatch
Data cache: the data cache is the largest part of Bufferpool, or the most memory-intensive part of SQL Server. It is used to store data that is loaded from disk into memory for subsequent operations to use
the size of the cache for each database in the current data cache. You can use a DMV to query
--sys.dm_os_buffer_descriptors
SELECT COUNT (*) * 8/1024 as ' Cached Size (MB) ',
Case database_id
When 32767 and then ' Resourcedb '
ELSE db_name (database_id)
END as ' Database '
From Sys.dm_os_buffer_descriptors
GROUP by Db_name (database_id), database_id
ORDER by ' Cached Size (MB) ' DESC
Transaction manager
The transaction manager consists of two main parts: the lock manager and the log Manager
1 . Lock Manager ensures concurrency and isolation of data by using lock mechanism.
2, log Manager through the pre-write log mechanism to ensure the ACID property of the transaction when the data needs to be modified, the data access method will go to the transaction manager through the transaction log operation, the request to the Buffer Manager to complete, It is then returned to the data access method and written to the log file through the transaction manager
Database transactions
A transaction is a unit of work for a database operation, which can be a select statement, or it can contain multiple select,update,dalete, the command collection for operations such as insert. This collection is considered a unit of work, and Many of the features of SQL SERVER are based on this unit of work, the transaction
The relational database contains 4 core transactional features: Atomicity, consistency, isolation, and durability, referred to as ACID Properties
1 , Atomic
atomicity means that all operations within a transaction must be completed or rolled back. A typical example is when operating on an ATM, either by taking money, saving money or transferring funds, or by never doing anything. If the transfer is successful but the other party does not receive the money, it is bound to cause great problems
2 , consistency
consistency requires that the entire transaction be consistent in the state of the database before and after the run, such as the inventory cannot be negative, the value of the gender field is either " male " or " female " . To ensure transactional consistency, transactions cannot break the consistency constraints in the data definition
3 , Isolation
isolation guarantees that at the same time, the operation of a transaction cannot be affected by another firm. But SQL Server is actually allowed to do this.
4 , Persistence
persistence means that once a transaction is successfully committed, it is permanently stored in the server's file system, and even if the system crashes in the middle, the effect is not lost, which is ensured by logging
committing a transaction means permanently recording the impact of the transaction into the database, and rolling back means removing all the operations that have occurred, which is tantamount to not doing it, but the log will actually record the operation.
650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/87/F8/wKiom1fk89HQ2pKDAAEBZ9_jAbI627.jpg-wh_500x0-wm_3 -wmp_4-s_2601675609.jpg "title=" 2.jpg "alt=" Wkiom1fk89hq2pkdaaebz9_jabi627.jpg-wh_50 "/>
relational engine and storage engine are SQL Server The two main components
1, the relational engine, also called the query processor, because its main role is to optimize and execute the query. The command parser contained in the relational engine is used to check the syntax and transform the query tree, while the query optimizer is used to refine the query, and a query executor is responsible for executing the query
2. Thestorage Engine, which manages the data and I/O involved, contains data access methods to handle I/O requests to rows, indexes, also, row versions, spatial allocations, etc. , and the buffer Manager is used to manage SQL Server The main memory consumption components-- Buffer Pool, the transaction manager will pass the managed data and maintain the ACID properties of the transaction
Buffer pool: in the field of database performance and management is a very important component, is also the most memory consuming component in SQL Server. The buffer pool contains all the caches of SQL Server, such as plan cache and data cache .
here is the logical CPU format in the operating system with different number of digits
The maximum number of worker threads that can be supported.
1) for the four-bit operating system:
Total Available Logic Cpu<=4 , the maximum worker thread is =256.
Total Available Logic Cpu>4 , the maximum worker thread =256+((logical CPU-4) x8).
2) for the four-bit operating system:
Total Available Logic Cpu<=4 , the maximum worker thread is =512.
Total Available Logic when cpu>4, the maximum worker thread =512+((logical CPU-4) x).
There is also a relatively simple way to check the maximum number of threads for the current system. For example, execute the following script:
SELECT Max_workers_count
Fromsys.dm_os_sys_info
typically , a wait of type ThreadPool means there is currently a large number of parallel execution plans, or a CPU bottle is encountered
Neck, but regardless of the situation, you need to check this part of the data frequently to make sure that there are enough threads available to
each worker thread consumes 2MB of memory in the system and consumes 0.5MB in the
memory, so SQL Server creates a worker thread only when it is needed. This part of the information is available through sys.
Dm_os_workers This DMV to see.
SELECT COUNT (*)
Fromsys.dm_os_workers
SQL Server NUMA CPU configuration
SELECT scheduler_id,
CPU_ID,
PARENT_NODE_ID,
Status
From Sys.dm_os_schedulers
The current number of cached plans and the MB Number
SELECT COUNT (*) as ' number of plans ',
SUM (CAST (size_in_bytes as BIGINT))/1024/1024 as ' Plan Cache size (MB) '
From Sys.dm_exec_cached_plans
To view the corresponding amount of memory for a cached object
SELECT ObjType as ' Cached Object Type ',
COUNT (*) as ' number of plans ',
SUM (CAST (size_in_bytes as BIGINT))/1024/1024 as ' Plan Cache size (MB) ',
AVG (usecounts) as ' avg use Count '
From Sys.dm_exec_cached_plans
GROUP by ObjType
Querying the database path
Use master
Go
SELECT Physical_name
From Sys.master_files
WHERE database_id = db_id (' rjbdb ')
Check certificates
Select Name, Start_date, expiry_date
From Sys.certificates
Where name not like ' ##% '
View Mirroring Endpoints
SELECT * from Sys.endpoints E
Querying mirroring status
SELECT *
From sys.database_mirroring
where database_id=db_id (' rjbdb ')
This article from the "Computer Network Technology" blog, reproduced please contact the author!
SQL Server 2008 Basics