SQL Server 2008 Basics

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