Improved. NET application performance and Scalability (iv)-SQL server performance

Upward expansion and plane expansion

Optimize the application before scaling up or scaling out.

Optimize your application before you have an upward expansion and plane expansion.

You can reduce invalid locks, use parameterized queries, add appropriate indexes, and so on to improve disk, memory, and CPU utilization.

Address historical and reporting data.

Process history and data for a report.

A system with a long running time must have a lot of historical data, in the current network has been saved this data, will certainly bring negative impact on the application of the line, query a small library and query the cost of a large library is very different, so generally should be part of the historical data into different areas, historical data is read-only, You can even put it in a separate read-only database. Data for the report is also, mostly historical data, you can set up a separate data warehouse to store them, and do not put together with the current network library.

Scale up for most applications.

For most applications for upward expansion.

If you have better hardware after you've processed historical data and report data, then add these new hardware, such as faster CPUs and hard drives, larger memory, and so on, but add new hardware postscript to modify your SQL Server configuration to accommodate these new hardware, such as opening/ 3G switch to use more memory.

Scale out when scaling the up does isn't suffice or is cost-prohibitive.

Use horizontal expansion When you are still not satisfied with the upward expansion or the cost of upward expansion is too high.

In horizontal expansion, some tables may need to be partitioned horizontally so that they can be partitioned onto different machines, which may require replication of the data with SQL Server replication across multiple machines. and a federated server made with a large number of PC servers is also more complex in disaster recovery and disaster-tolerance, and you need to weigh which of these complexities to pay for hardware.

Schema

Devote the appropriate to schema.

Appropriate resources are given for schema design.

In order to design and test the data model, it will be very expensive to spend enough time and devote enough resources to collect the business logic before the architecture is designed.

Separate online analytical processing (OLAP) and online transaction processing (OLTP) workloads.

Split OLAP and OLTP load.

The characteristic of OLAP is the frequent execution of long queries, which are characterized by small and many transactions, typically returned in 1 seconds, long-running query analysis, report queries, and ad hoc queries that hinder insertions and other OLTP transactions. If you need to support both types of work at the same time, consider creating a report server to support OLAP and reporting, and you can use SSAS to support these efforts.

Normalize, Denormalize later for performance.

Follow the paradigm Design database, and then do not follow the paradigm to optimize performance.

Following the paradigm design database allows the database to reduce redundancy, but the written query statement is complex, with many joins, and can degrade performance, sometimes in order to prevent some common query across the table query, the same data will be stored in multiple tables to improve performance, but this violates the paradigm.