10 + Methods for SQL Server database optimization

There are many reasons why the query is slow in practice because of the several methods of optimizing SQL Server database, the most common of which are the following: No index or no index (this is the most common problem of slow query, is the defect of program design).

I/O throughput is small, creating a bottleneck effect.

A computed column is not created that causes the query to not optimize the SQL Server database.

Not enough memory.

Network speed is slow.

The amount of data queried is too large (you can use multiple queries, other ways to reduce the amount of data).

Lock or deadlock (this is also the most common problem of slow query, is the defect of program design).

Sp_lock,sp_who, the active user is viewed because of competing resources for reading and writing.

Unnecessary rows and columns are returned.

Query statement is not good, no optimization.

You can refine your query by:

1, put the data, logs, indexes on different I/O devices, increase the read speed, previously can be tempdb should be placed on the RAID0, SQL2000 is not supported. The larger the amount of data (size), the more important it is to increase I/O.

2. Vertical and horizontal partition table, reduce the size of the table (Sp_spaceuse).

3, upgrade the hardware.

4, according to the query conditions, to establish an index, optimize the index, optimize the SQL Server database access, limit the amount of data in the result set. Note that the fill factor is appropriate (preferably using the default value of 0). The index should be as small as possible, using a Lie Jian index with a small number of bytes (refer to the creation of the index), and do not Jianjian a single index on a limited number of values such as the gender field.

5, improve the speed.

6, expand the memory of the server, Windows 2000 and SQL Server 2000 can support 4-8g memory. Configure virtual Memory: The virtual memory size should be configured based on the services that are running concurrently on the computer. Run Microsoft SQL Server? 2000, consider setting the virtual memory size to 1.5 times times the physical memory installed on your computer. If you have additional full-text search features installed and you plan to run the Microsoft search service to perform full-text indexing and querying, consider:

Configure the virtual memory size to be at least 3 times times the physical memory installed in the computer. Configure the SQL Server max server memory server configuration option to 1.5 times times the physical memory (half of the virtual memory size setting).

7. Increase the number of server CPUs, but it is important to understand that parallel processing of serial processing requires resources such as memory. The use of parallel or string travel is the MSSQL automatic evaluation option. A single task is decomposed into multiple tasks and can be run on the processor. For example, delays in sorting, connecting, scanning, and group by words are performed simultaneously, and SQL Server determines the optimal level of parallelism based on the load of the system, and complex queries that consume large amounts of CPU are best suited for parallel processing. However, the update operation is Update,insert, and delete cannot be processed in parallel.

8, if you use like to query, simple to use index is not, but the full-text index, consumption of space. Like ' a% ' uses the index like '%a ' when querying with like '%a% ' without an index, the query time is proportional to the total length of the field value, so the char type is not used, but varchar. The full-text index is long for the value of the field.

9, DB server and Application server separation, OLTP and OLAP separation.

10. A distributed partitioned view can be used to implement a federation of database servers. A consortium is a set of servers that are managed separately, but they work together to share the processing load of the system. This mechanism of forming a federation of database servers through partitioned data can expand a set of servers to support the processing needs of large, multi-tiered Web sites. For more information, see Designing federated database servers. (Refer to SQL Help file ' partitioned view ') before implementing partitioned views, you must first horizontally partition the table.

After the member tables are created, a distributed partitioned view is defined on each member server, and each view has the same name. This enables queries that reference the distributed partitioned view name to run on any member server. The system operates as if each member server has a copy of the original table, but there is only one member table and one distributed partitioned view on each server. The location of the data is transparent to the application.

11. Rebuild the index DBCC REINDEX, DBCC INDEXDEFRAG, shrink data and log DBCC SHRINKDB,DBCC shrinkfile. Sets the auto-shrink log. For large databases do not set the database autogrow, it will degrade the performance of the server. There's a lot of emphasis on T-SQL, and here's a list of common points: first, the DBMS processes the query plan:

Lexical, syntax checking of query statements.

The query optimizer that submits the statement to the DBMS.

Optimization of the optimizer to do algebraic optimizations and access paths to the SQL Server database.

Query planning is generated by the precompiled module.

It is then submitted to the system for processing execution at the appropriate time.

Finally, the execution results are returned to the user second, look at the SQL Server data storage structure: A page size of 8K (8060) bytes, 8 pages for a disk area, according to B-Tree storage.

12. The difference between commit and Rollback Rollback: Roll back all things. Commit: Commit the current thing. There is no need to write things in dynamic SQL, if you want to write please write outside such as: Begin TRAN EXEC (@s) commit trans or write dynamic SQL as a function or stored procedure. [SPAN]

13, in the query SELECT statement using the WHERE clause to limit the number of rows returned, avoid table scan, if the return of unnecessary data, wasted the server's I/O resources, aggravating the burden of the network to reduce performance. If the table is large, locks the table during the table scan and prevents other joins from accessing the table, with serious consequences.

10 + Methods for SQL Server database optimization