SQL Server optimization notes

Recently, I optimized a system built using JBuilder + JBoss + SQL Server. The typical three-tier structure consists of a Web server built by JBoss, And the backend uses SQL Server as the database. Before optimization, the system shows that there is basically no concurrent access, and the response to operations involving a large amount of data is particularly slow. When multiple users access the service at the same time, the Web service may crash.

After in-depth understanding with the O & M personnel, I have a deep understanding of the most serious cases. From the Web day and code analysis, I found that the main problems are concentrated in two sections, the first is the issue of code logic, but the time consumed for database access.

The SQL Server version is 2005. The data size of several tables is extremely large, ranging from 700 to 800 mb, and the number of records has reached about 200 million rows.

SQL Server 2005 provides SQL Server Profiler to track the SQL statements executed inside the database, as well as the time consumed for SQL statement execution, and so on, which can effectively analyze the read/write performance of tables. Before carefully analyzing SQL Server Profiler data, prepare a bit of knowledge about stored procedures.

1. stored procedures are a set of SQL statements in a large database system that are compiled and stored in the database to complete specific functions, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. It mainly improves the execution efficiency of SQL statements, and data search still needs to be indexed. first, the local temporary storage process, with the well font size (#) as the first character of its name, will become a local temporary storage process stored in the tempdb database, and only the user who created it can execute it; 3. create proc sp_name @ [parameter name] [type], @ [parameter name] [type] As begin ......... end4. call exec sp_name [parameter name] 5. show procedure status

After using the stored procedure, you can accelerate the preparation for SQL statement execution.

If a table contains about 0.1 million rows of data and a good index executes the SELECT statement on the table, the time consumed can be less than 1 ms.

 

After an index is created on the same table, the query results are compared. The table size is 20 mb and the number of rows is about 0.1 million rows. A good index is created and the query time is about 1 ms.

 

The size of this table is nearly 800 mb, and the data is recorded in 1.6 million rows. After the index is created, the query time is reduced to 230 Ms.