Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to optimize the query speed in SQL Server. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

What are the ways to optimize the speed of SQLServer queries

1. Put the data, logs and indexes on different I Tempdb O devices to increase the reading speed. In the past, you can put Tempdb on the RAID0, but it is no longer supported by SQL2000. The larger the amount of data (size) is, the more important it is to improve the I _ map O.

2. Divide the table vertically and horizontally to reduce the size of the table (sp_spaceuse)

3. Upgrade hardware

4. According to the query conditions, establish the index, optimize the index, optimize the access mode, and limit the amount of data in the result set. Note that the fill factor is appropriate (it is best to use the default value of 0). The index should be as small as possible. It is better to use columns with a small number of bytes to build an index (reference index creation). Do not build a single index on fields with limited values, such as gender fields.

5. Improve the network speed

6. Expand the memory of the server. Windows2000 and SQLserver2000 can support 4-8 GB of memory.

Configure virtual memory: the size of virtual memory should be configured based on services running concurrently on the computer. When running MicrosoftSQLServer2000, consider setting the virtual memory size to one and a half times the physical memory installed on your computer. If you also have the full-text search feature installed and you plan to run the Microsoft search service to perform full-text indexing and queries, consider configuring virtual memory to be at least three times the physical memory installed on your computer. Configure the SQLServermaxservermemory server configuration option to 1.5 times the physical memory (half the virtual memory size setting).

7. Increase the number of server CPU; however, it must be understood that parallel processing serial processing needs more resources such as memory. The use of parallel or serial travel is selected by MsSQL automatic evaluation. A single task is broken down into multiple tasks and can be run on the processor. For example, delayed query sorting, join, scanning and GROUPBY words are executed at the same time, SQLSERVER determines the optimal parallel level according to the load of the system, and complex queries that consume a lot of CPU are most suitable for parallel processing. But the update operation UPDATE,INSERT,DELETE cannot be processed in parallel.

8, if you are using like for query, it is not possible to simply use index, but full-text indexing consumes space. When like''a%'' uses index like''%a'' and does not use index references to like''%a%'' queries, the query time is proportional to the total length of field values, so you cannot use the CHAR type, but VARCHAR. Create a full-text index for long values of the field.

9. Separation of DBServer and APPLicationServer; separation of OLTP and OLAP

10. Distributed partitioned views can be used to implement database server consortia.

A consortium is a group of separately managed servers, but they cooperate with each other to share the processing load of the system. This mechanism of forming a consortium of database servers through partitioned data can expand a set of servers to support the processing needs of large, multi-tier Web sites. For more information, see designing a federated database server. (refer to the SQL help file''Partition View')

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

B. after creating the member table, define a distributed partitioned view on each member server, and each view has the same name. In this way, queries that reference the name of the distributed partitioned view 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 partitioned view on each server. The location of data is transparent to the application.

What are the ways to optimize the speed of SQLServer queries

11. Re-index DBCCREINDEX,DBCCINDEXDEFRAG, shrink data and log DBCCSHRINKDB,DBCCSHRINKFILE. Set up automatic shrinking log. Do not set the database to grow automatically for large databases, it will degrade the performance of the server.

There is a lot of attention to the way T-sql is written. Here are some common points: first of all, the process of DBMS processing query plan is as follows:

1. Lexical and grammatical checking of query sentences

2. Submit the statement to the query optimizer of DBMS

3. Optimizer does algebra optimization and access path optimization.

4. query planning is generated by pre-compilation module.

5. Then submit it to the system for processing and execution at the right time

6. Finally, the execution result is returned to the user.

Second, take a look at the SQLSERVER data storage structure: a page size of 8K (8060) bytes, 8 pages for a disk, according to the B-tree storage.

12. The difference between Commit and rollback Rollback: roll back everything. Commit: submit the current thing. There is no need to write things in dynamic SQL, if you want to write, please write outside such as: begintranexec (@ s) committrans or write dynamic SQL into functions or stored procedures.

13. Use Where words to limit the number of rows returned in the query Select statement to avoid table scanning. if unnecessary data is returned, it wastes the server's Icano resources, increases the burden of the network and reduces the performance. If the table is very large, lock the table during the table scan and prohibit other joins from accessing the table, with serious consequences.

14. SQL's comments have no effect on implementation.

The above is how to optimize the query speed in the SQL Server shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.