About SQL Server High concurrency solutions

Original address: http://www.cnblogs.com/zuowj/p/3566247.html

Now everyone is more concerned about the problem is in the case of high concurrency of multi-user, how to develop the system, which for us programmers, is really worth studying, recently looking for a job interview is often asked, in fact, I have to care and understand this kind of problem, but has not summed up, resulting in the interview can not be a complete and comprehensive answer, So today I have a special summary of the SQL Server high concurrency solution, I hope to help you, if there is not, please tell it in time, thank you!

SQL Server high concurrency solutions are mainly from the following areas:

1.SQL Statement Optimization:

A. To reduce the scope of the query (including the use of paged query), as far as possible the exact query criteria and query fields;

B. Use as little as possible in the query condition: like, (not) in, (not) is Null,order by,distinct,count (*),!=,<>;

C. Do not perform function operations on the fields of the query,

such as: AA. SUBSTRING (' aa123 ') = ' AA ', and should be: ' aa123 ' like ' aa% '; ---applied to the index

Bb. ' AA ' + ' 123 ' = ' aa123 ', and should be: ' AA ' =left (' aa123 ', 2)

D. Judge the data exists, do not use the top 1, but should be: EXITS

e. For complex SQL queries, you can use SQL stored procedures or build views directly (views are not too complex);

F. Use as little as possible of cursors, triggers;

2. Table Design Optimization:

A. Vertical partition table design, the table according to a certain principle (can be designed according to the frequency of field read and write) designed to correspond to several tables, the use of primary (external) key correlation query;

B. Horizontal partition table design, the data in the table according to the use value (such as: only used for nearly 3 months of effective data) to carry out data transfer backup, to reduce the amount of data in the table;

C. Table data Physical storage partition design, the table data in accordance with a rule to establish a physical table partition to store, to reduce the IO burden on the hard disk;

D. Establish an appropriate index (clustered and nonclustered indexes);

3. Transaction Setup Optimization:

The transaction ISOLATION level is: (the isolation level acts on the transaction, and the lock acts on each SQL statement)

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom image	Description	Generate or equate the corresponding lock
Uncommitted read (READ UNCOMMITTED)	Is	Is	Is	If other transactions are updated, whether committed or not, execute immediately	NOLOCK
Commit read (Read committed default)	Whether	Is	Is	Reads the submitted data. If other transactional updates are not committed, wait for	HOLDLOCK
REPEATABLE READ (Repeatable Read)	Whether	Whether	Is	No other transaction update is allowed during the query	HOLDLOCK
Serializable read (serializable)	Whether	Whether	Whether	No other transaction is allowed to insert or delet during query	HOLDLOCK

A. The principle of transaction isolation: share read, write it , that is to say: When executing the query, if the data consistency requirements are high, you can use the REPEATABLE read (REPEATABLE Read) isolation level, if there is no strict requirements, you can recommend the use of uncommitted read (read UNCOMMITTED) isolation level;

4. Server Hardware optimization:

A. server memory, hard disk and other core hardware performance of course, the stronger the better;

B. Buy multiple servers and set up clusters to achieve a high computational speed by using multiple computers for parallel computing, or you can use multiple computers to make backups, so that any one machine can break the whole system or run normally;

C. Establish a DB image synchronization on multiple servers, and realize the read and write separation, that is: In addition to the specified one or several servers have allowed updates, the remaining servers are only as data mirroring synchronization, can not be updated, only for query;

About SQL Server High concurrency solutions