SQL Server 200.8 billion data performance optimization

According to the design practice, the query when the master table through the Keyword Field Association query, the query statement is as follows:

Select Top 1000 a.word,a.queryurl,a.irank,a.title,a.baiduurl,a.itraffic1,a.itraffic2,b.ibaiduindex from Zibiao a

With (NOLOCK)

INNER JOIN Zhubiao B with (NOLOCK) on A.word=b.word

where a.queryurl= ' http://zhidao.baidu.com '

ORDER BY B.ibaiduindex Desc,a.irank

Find the speed is very slow, fast moment, slow time for a few minutes. The query execution plan for SQL Server analysis is as follows:

Analysis of this execution plan diagram, the main resource cost is in the main Table clustered index lookup, should be the child table based on the URL to find keywords, and then to the main table to find the corresponding index of the keyword time is more time-consuming.

Because these 2 tables update frequency is very high, thought is the update frequency is too fast, resulting in lower index efficiency, the idea of using a database read-write separation scheme, dedicated to a standby server, through the publication of subscriptions, the two tables published to the standby server, dedicated to the standby server to do the query, found that the speed has not been improved.

In the CSDN sent a discussion post, netizens discussed very warmly, SQL Server many moderators have participated in the discussion, given a lot of programs. Some people say is IO read and write bottleneck, so upgraded the next server, hard drive using RAID10 solid-state hard disk, memory is upgraded to 128G, but the effect is still not obvious, the same statement, slow time or need dozens of seconds.

Has the user to give the proposal, is proposes puts the main table field to the child table redundancy, in order to reduce the associated resource consumption, and then unifies the above execution plan diagram, does the bottleneck is in the main tables ' clustered index lookup, then executes the Irank sort statement separately:

Select Top 1000 a.word,a.queryurl,a.irank,a.title,a.baiduurl,a.itraffic1,a.itraffic2 from Zibiao a

With (NOLOCK)

where a.queryurl= ' http://zhidao.baidu.com '

ORDER BY A.irank

found that the execution speed is very fast, almost seconds open, of course, the Irank field has been indexed.

The test was successful, so the Ibaiduindex field of the primary table was added to the child table redundancy and the index was established (the indexing here is tricky). Execute statement:

Select Top 1000 a.word,a.queryurl,a.irank,a.title,a.baiduurl,a.itraffic1,a.itraffic2,a.ibaiduindex from Zibiao a

With (NOLOCK)

where a.queryurl= ' http://zhidao.baidu.com '

ORDER BY A.ibaiduindex Desc,a.irank

Query instant out results. The implementation plan is as follows:

Here are the SQL statements to analyze how we can build an index. Indexing includes the order of the fields, the sorting method of the fields, the include items are very important, any mistake, can not achieve good optimization effect.

1. We definitely need to build a composite index. So what should I use to combine the fields? Here my combination field is: Queryurl (this field first, because we are the first to filter based on this field), Ibaiduindex, Irank

2. Note here, because my fixed sort is ibaiduindex desc,irank ASC, so the order of the fields should be: Queryurl,ibaiduindex,irank. The categories that are sorted at the same time are: Ibaiduindex desc, irank ASC. I didn't notice here at first, the sort of ibaiduindex is ASC, and it turns out that it still takes 2 seconds to execute the SQL statement above.

3. Index contains items, also important, if you do not need to include items, the index to find the primary key, but also according to the primary key to find other fields. So we need to set the index inclusion column and add the rest of the fields that are left in the index field.

The optimized effect, as shown in the execution plan above, is to find our data in a nonclustered index lookup, and none of the sorting time is required, because our indexes are sorted in order. Of course, here's to say, the index contains really good, but the cost is disk space. Added an index containing entry, the database space increased by dozens of G.