Collection of reasons for slow query speed of SQL server and optimization suggestions

Time:2020-10-29
  • Collection of reasons for slow query speed of SQL Server
  1. There is no index or index is not used (this is the most common problem of slow query, which is a programming defect).
  2. The I / O throughput is small, forming a bottleneck effect.
  3. The computed column was not created, which caused the query not to optimize the SQL Server database.
  4. Out of memory.
  5. Network speed is slow.
  6. The amount of data found is too large (you 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 a programming defect).
  8. Sp_ lock,sp_ Who, active users view, because read-write competes for resources.
  9. Unnecessary rows and columns were returned.
  10. The query statement is not good, there is no optimization.
  • Optimization suggestions
  1. Put the data, log and index on different I / O devices to increase the reading speed. Previously, tempdb should be placed on raid0, which is not supported by SQL2000. The larger the amount of data (size), the more important it is to improve I / O.
  2. Divide the table vertically and horizontally, reduce the size of the table (sp_ spaceuse)。
  3. Upgrade the hardware.
  4. According to the query conditions, index is established, index is optimized, SQL Server database access mode is optimized, and the amount of data in the result set is limited. Note that the fill factor is appropriate (preferably the default value of 0). The index should be as small as possible. It is better to use a column with a small number of bytes to build an index (refer to the creation of the index). Do not build a single index for fields with limited values, such as gender field.
  5. Speed up the Internet.
  6. Expand the memory of the server, Windows 2000 and SQL Server 2000 can support 4-8g of memory. Configure virtual memory: the virtual memory size should be configured based on the services running concurrently on the computer. When running Microsoft SQL Server? 2000, consider setting the virtual memory size to 1.5 times the physical memory installed on your computer. If you have additional full-text search installed and you plan to run the Microsoft search service to perform full-text indexing and queries, consider configuring the virtual memory size to at least three times the physical memory installed on your computer. Configure the SQL Server Max server memory server configuration option to 1.5 times the physical memory (half of the virtual memory size setting).
  7. Increase the number of CPU in the server; however, it must be understood that parallel processing and serial processing require more resources, such as memory. Whether to use parallel or serial stroke is selected by MSSQL automatic evaluation. When a single task is decomposed into multiple tasks, it can be run on the processor. For example, delaying the query sorting, connection, scanning and group by sentences at the same time, SQL server determines the optimal parallel level according to the load of the system, and complex queries that need to consume a lot of CPU are most suitable for parallel processing. However, the update operations update, insert and delete cannot be processed in parallel.
  8. If you use like to query, you can’t simply use index, but full-text index consumes space. Like ‘a%’ when using index like ‘% a’ without index reference like ‘% a%’, the query time is directly proportional to the total length of field values, so var char can not be used instead. Full text indexing for fields with long values.
  9. DB server and application server are separated; OLTP and OLAP are separated.
  10. Distributed partition view can be used to implement database server Federation. A federation is a group of servers managed separately, but they cooperate to share the processing load of the system. The mechanism of forming database server Federation through partitioned data can expand a group of servers to support the processing needs of large multi-layer web sites. For more information, see designing a federated database server. (refer to SQL help file ‘partitioned view’) before implementing partition view, you must first partition the table horizontally.
  11. After the member table is created, a distributed partitioned view is defined on each member server, and each view has the same name. In this way, queries that refer to the names of distributed partitioned views can be run on any member server. The system operates as if there is a copy of the original table on each member server, but there is only one member table and one distributed partition view on each server. The location of the data is transparent to the application.
  12. Re index DBCC reindex, DBCC indexdefrag, shrink data and log DBCC shrinkdb, DBCC ShrinkFile. Set auto shrink log. Do not set automatic database growth for large databases, it will reduce the performance of the server. There is a lot of attention in the writing of T-SQL. The following are the common points: first, the process of DBMS processing query plan is like this: lexical and syntax check of query statements. Submit the statement to the query optimizer of the DBMS. The optimizer does algebra optimization and access path optimization of SQL Server database. The query plan is generated by the precompiled module. It is then submitted to the system for processing and execution at an appropriate time. Finally, the execution results are returned to the user. Secondly, take a look at the data storage structure of SQL Server: the size of one page is 8K (8060) bytes, and the eight pages are a disk, which are stored according to the B-tree.
  13. The difference between commit and rollback. Commit: commit the current transaction. There is no need to write things in dynamic SQL. If you want to write things, please write them outside, such as: begin tran exec (@ s) commit trans, or write dynamic SQL into functions or stored procedures. [SPAN]
  14. In the query select statement, use the where sentence to limit the number of rows returned to avoid table scanning. If unnecessary data is returned, the server’s I / O resources are wasted, the network burden is increased, and the performance is reduced. If the table is very large, it will lock the table during the table scanning, and prohibit other joins from accessing the table, which will result in serious consequences.