Performance Optimization of SQL Server databases

In a large database, performance has become one of the focuses of attention. How to Make the efficient and effective operation of the database become an issue that must be considered by database administrators and developers. Performance is the measure of efficiency when one or more applications run in the same environment. Performance is usually expressed by response time and work efficiency. Response time refers to the time it takes to complete a task. You can reduce the response time from the following three aspects:

· Reduce the number of competitions and waits, especially the number of disk read/write waits

· Use faster Components

· Reduce the time required to use resources

The vast majority of performance gains come from excellent database design, precise query analysis, and appropriate indexing. The best performance can be achieved by establishing excellent database designs and learning to use the SQL Server Query Optimizer during development.

To achieve better database performance, we need to optimize the database to reduce competition for system resources, such as competition for data cache, process cache, system resources, and CPU.

SQL Server has the following optimization levels:

· Application Layer-most performance gains come from the optimization of queries in your SQL application, which must be based on a good database design.

· Database layer-resources shared by applications at the database layer. These resources include hard disks, transaction logs, and data caches.

· Server layer-there are many shared resources on the server layer, including data cache, process cache, lock, and CPU.

· Device layer-refers to the disk for storing data and its controller. At this layer, you should pay special attention to disk I/O.

· Network Layer-refers to the network connecting users and SQL Server.

· Hardware layer-indicates the CPU available.

· Operating system layer-ideally, SQL Server is the only main application of a machine. It must be used with the operating system and other sybase software, for example, Backup Server or SQL Server Monitor shares processor, memory, and other resources.

In most cases, we optimize the application layer, because the optimization of application performance is the most acceptable function, and the results can be observed and tested, query performance is a key to the overall performance of SQL applications.

Problems at the application layer include:

· Different performance policies are required for decision-making support VS. And online transaction processing (OLTP ).

· The transaction design can reduce the concurrency, because long transactions keep occupying the lock, which reduces the access of related data by other users.

· Join operations are required for data modification due to association consistency.

· Indexes supporting the Select Operation increase the data modification time.

· The audit set up for Security restricts Performance

InApplication LayerOptimization options include:

· Remote processing or replication can separate decision support from OLTP machines

· Use stored procedures to reduce Compilation Time and Network Utilization

· Use the minimum number of locks to meet your application needs

Database LayerProblems include:

· Create backup and recovery Solutions

· Distributed Data Storage on devices

· Audit operations affect performance. Only audit what you need

· Routine maintenance activities will reduce performance and prevent users from operating database tables.

Optimization Options at the database layer include:

· Use the threshold value of the transaction log to automatically dump the transaction log to prevent it from exceeding the space used

· Use thresholds in data segments to monitor space usage

· Use partitions to accelerate data loading

· Positioning of objects to avoid hard disk Competition

· Put important tables and indexes into the cache to ensure that important tables and indexes can be obtained at any time

Server LayerAre:

· Application Type-whether the server supports OLTP or DSS, or both

· The number of supported users affects the optimization decision-as the number of users increases, the competition for resources will change

· Network load

· When the number of users and transactions reaches a certain number, the replication server or other distributed processing is a solution.

Server layer optimization options include:

· Optimized memory-a key configuration parameter and other parameters

· Decision-making is client processing or server-side processing-can some processing be performed on the client?

· Configure the cache size and I/O size

· Add multiple CPUs

· Schedule batch processing tasks and generate reports for idle time

· The workload changes and specific parameters are reconfigured

· Determine whether DSS can be moved to the device layer of another SQL Server

Device layer problems include:

· Whether the primary device, the device that contains the user database, the user data device, or the Database Log needs to be mirrored

· How to distribute system databases, user databases, and database logs between devices

· Whether partition is necessary to achieve high performance of heap table insert operations

Options for optimization on the device layer include:

· Using multiple medium-size devices and multiple controllers may provide better I/O performance than using a small number of large devices

· Distributed databases, tables, and indexes for I/O loading on different devices

Network Layer

In fact, all SQL Server users access their data through the network. The main problems at the network layer are:

· Network Traffic

· Network bottleneck

· Network speed

Network-layer optimization options include:

· Configure the package size to match the application's needs

· Configure subnets

· Separates busy network usage

· Create a high-capacity network

· Configure multiple network Engines

· Better design of applications and limit required network transmission

Hardware Layer

Problems on the hardware layer include:

· CPU Efficiency

· Disk access: controllers and disks

· Disk backup

· Memory usage

Options optimized on the hardware layer include:

· Increase the CPU to adapt to the workload

· Configure the scheduler to improve CPU utilization

· Follow multi-processor application design guidelines to reduce competition

· Configure multiple data cache operating system layers

Operating system layerThe main problems are:

· File system-is it exclusively used by SQL Server?

· Memory Management-accurately estimates memory usage of the operating system and other programs

· CPU utilization-how many processors are available in the entire system? How many are allocated to SQL Server?

Options for optimization at the operating system layer include:

· Network Interfaces

· Select between the file and the original Partition

· Increase memory

· Move customer operations and batch processing to other machines

· SQL Server uses multiple CPUs

(