SQL Server数据库查询速度慢原因及优化方法(国外英文资料).docVIP

SQL Server数据库查询速度慢原因及优化方法(国外英文资料) SQL Server database query slow reason and optimization method (1) Release time: 2008.12.03 04:59 source: CCID, author: voboy [CCID -IT technology reports] SQL Server database query speed is slow, there are many common: 1. There is no index or index (this is the most common problem of slow queries and is a bug in programming) 2, I/O throughput is small, forming a bottleneck effect. 3, no calculation column was created, causing the query not to be optimized. 4, lack of memory 5, network speed is slow 6, query the amount of data is too large (you can use multiple queries, other ways to reduce the amount of data) 7, lock or deadlock (which is also the most common problem in slow queries, is a bug in programming) 8, sp_lock, sp_who, active users view, because read and write competitive resources. 9 returns the unnecessary rows and columns 10, query is not good, no optimization The query can be optimized by the following methods: 1, the data, log, index on different I/O devices, increase the speed of reading, previously Tempdb can be placed on the RAID0, SQL2000 is not supported. The larger the amount of data (size), the more important it is to improve the I/O. 2, vertical and horizontal division table, reduce the size of table (sp_spaceuse) 3 、 upgrade hardware 4, according to the query conditions, establish index, optimize index, optimize access mode, limit the amount of data of the result set. Note that fill factor should be appropriate (preferably using default 0). The index should be as small as possible, using a column with small bytes to index (refer to the creation of the index), and do not create a single index for fields with limited values, such as gender fields. 5, improve network speed. 6, to expand server memory, Windows 2000 and SQL Server 2000 can support 4-8G memory. Configuring virtual memory: the virtual memory size should be configured based on concurrent services running on the computer. When running Microsoft SQL Se