SQL Server performance optimization split boost performance distributed view (vii)

Distributed view:

1. partition large tables onto multiple servers for storage

2. There are multiple tables physically and logically

3. Access to multiple server tables through view implementations

4. Consolidate Network Load Balancing

Link Two databases:

Create the same data structure:

CREATE TABLE sales (c1 int not NULL,C2 int) ALTER TABLE sales ADD constraint PK_SALES_C1 primary KEY (C1) ALTER TABLE sales Add constraint ck_sales_c1 Check (c1 between 1 and 100)
--Post Database     101---200

Click New Linked server:

To create a view:

CREATE VIEW V_sales as SELECT * FROM Sales UNION ALL SELECT * FROM [LOCALHOST\SRC1] HRDB.dbo.Sales

Establish a link to the first server on the second server and the same steps as above

Execute the following statement:

Insert V_sales VALUES (2,2) Insert V_sales values (200,200)

Error: (Ensure that distributed things are available)

Open:

Error at the time of execution:

Change to this:

Set XACT_ABORT on Insert V_sales values (2,2) Insert V_sales values (200,200)

Execute the following statement:

The results are as follows:

Execute the following UPDATE statement:

The first server is not all on the second server:

This shows: Distributed view can effectively solve the system performance problems, the large table different record range---on different servers, and then through the view of the table operation, effective balance server load traffic

Split up access to the boosted data:

Placement of data:

1. Principles of Data placement:

1. Create filegroups on different powerless disks or raid

2. Place tables in common connection queries to different filegroups

3. Place large, frequently accessed tables into different filegroups

4. log files are stored on separate disks

5. Place the tempdb database on a fast disk or raid

SQL Server performance optimization split boost performance distributed view (vii)