Reasons and workarounds for slow SQL server database queries

Ask

SQL Server database queries are slow for a number of reasons, and are common in the following ways:

1, no index or no index (this is the most common problem of slow query, is the defect of program design)

2, I/O throughput is small, forming a bottleneck effect.

3. No computed columns are created, resulting in queries not being optimized.

4. Insufficient memory

5. Slow network speed

6, the amount of data queried is too large (can use multiple queries, other methods to reduce the amount of data)

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

8, sp_lock,sp_who, the activity of the user view, the reason is to read and write competitive resources.

9. Return unnecessary rows and columns

10, query statement is not good, no optimization

You can refine the query in the following ways:

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. Upgrading hardware

4, according to the query criteria, index, optimize the index, optimize access mode, limit the data volume of 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. When you 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 on 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 Update,insert,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. Separation of DB server and application server; 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.

A, before implementing a partitioned view, you must first horizontally partition the table

b, after creating the member table, define a distributed partitioned view 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, DBCCINDEXDEFRAG, 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:

1. Lexical and grammatical checking of query statements

2. Query optimizer to submit statements to the DBMS

3 optimization of optimized algebra and access paths

4. Generate query plan by precompiled module

5, and then at the appropriate time to submit to the system processing execution

6, finally returns the execution result 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.

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.

14. The SQL Comment Statement has no effect on execution

15, as far as possible without using the cursor, it occupies a large number of resources. If you need to execute row-by-row, try to use non-cursor technology, such as: In the client loop, with temporary tables, table variables, subqueries, with case statements and so on.

Cursors can be categorized according to the extraction options it supports: forward-only the rows must be fetched in the order from the first row to the last row. Fetch NEXT is the only allowed fetch operation and is the default. Scrollable can randomly fetch any row anywhere in the cursor. The technique of cursors becomes very powerful under SQL2000, and his purpose is to support loops.

There are four concurrency options READ_ONLY: The cursor is not allowed to locate updates (update), and there are no locks in the rows that make up the result set.

Optimistic with ValueS: Optimistic concurrency control is a standard part of transaction control theory. Optimistic concurrency control is used in situations where there is only a small chance for a second user to update a row in the interval between opening the cursor and updating the row. When a cursor is opened with this option, there is no lock to control the rows in it, which will help maximize its processing power. If the user attempts to modify a row, the current value of this row is compared with the value obtained when the row was last fetched. If any value changes, the server will know that the other person has updated the row and will return an error. If the value is the same, the server executes the modification.

Reasons and workarounds for slow SQL server database queries