Design Optimization of SQL Server application procedural performance optimization

Optimize Your Application Design

If you use a multi-tier design for your application, SQL Server is only part of a large application. The implementation of multi-layer design has a greater impact on application performance than you think. It has a greater impact than SQL Server.

Unfortunately, when the application performance is low, it is often blamed on SQL Server, without reflecting on the design of the application, in fact, in many cases, design defects are the main cause of application performance problems. I will provide some suggestions to help you design your applications to prevent SQL Server from continuing to be solely responsible for its low performance.

When designing a multi-layer application, you must first choose logical and physical design. In these two designs, performance problems are most likely to occur in physical design because the theory must be implemented in the real world. Like anything else, you are faced with a variety of options, many of which will lead to upgrades or performance problems.

To determine which option is correct, you need to use testing techniques again to start early potential tests during the design phase. You can use Quick prototype testing, to determine which implementation can best meet users' needs.

In addition, when designing physical implementations, follow the following suggestions to ensure application scalability and optimal performance:

Perform data-centric tasks on SQL Server in the form of stored procedures as much as possible. Avoid data processing at the presentation layer and business layer.

Do not save or modify the status data at the business layer, as far as possible in the database.

Do not create complex or obscure object classifications. The creation and use of complex Classes usually consume resources and reduce the performance and scalability of applications. The reason is that when these objects are created and released, the memory allocation overhead is usually relatively large. When designing applications, you can consider using the Microsoft Transaction Processing Server (MTS) to make full use of the advantages of the database connection pool and Object pool. MTS can run to store database connections and objects in the pool, which can greatly improve the overall performance and scalability of applications.

If your application queries SQL Server for a long time, you can consider querying SQL Server asynchronously when designing the application. Such a query does not have to wait until the previous one is executed. One way to add this function to your multi-tier application is to use Microsoft Message Queue Server MSMQ ).

Although the above suggestions do not ensure that you have an application that can be upgraded and executed quickly, they can be said to be a good start.

Optimize Database Design

Similar to application design, database design is critical to the scalability and performance of SQL Server applications. Similar to application design, if you do not properly design the database at the beginning, it is often very difficult to modify the application after it is put into the production environment, the cost is high. When designing an SQL Server database, the following things are critical to its upgrade and performance.

Similarly, you need to use real data as early as possible to test your design. This means that you need to develop a prototype database with sample data, and then test the design using the expected behavior type in the real application.

One of the design principles you need to determine at the beginning is whether the database will be used for online transaction processing (OLTP) or Online Analytical Processing (OLAP ). One of the biggest mistakes that people often make when designing a database is trying to design a database to meet both OLTP and OLAP needs. If you want high performance and scalability, these two application types are mutually exclusive.

OLTP databases are usually highly normalized and help reduce the amount of data that must be stored. The less data is stored, the less I/O operations performed by SQL Server, and the faster database access. The transaction processing should be completed as soon as possible to reduce lock conflicts. Finally, to reduce the overhead of a large number of insert, update, and delete operations, you must use as few indexes as possible.

On the other hand, OLAP databases are highly normalized. In addition, it does not use transactions because the database is locked for read-only records. Of course, a large number of indexes are required to meet a wide range of report requirements.

It can be seen that the implementation of OLTP and OLAP databases is completely different. It is impossible for you to design a database to meet both requirements.

It is relatively easy to modify the database after problems are found in the early stage of database design. Therefore, it is almost impossible to modify the database design after the application development is complete.

1. Microsoft will launch two bundled server software for plug-and-play for Small and Medium-sized Enterprises
2. Remote Server Management Using port collision Technology
3. Storage document damage caused by Windows Home Server software error