SQL Server database operations on billions of tables

Sorting hundreds of billions of tables or a join between billions of tables can cause the system to lose its response.

1. I did make a large query that involved a data table with 200 million records, and a group by operation, resulting in large CPU, memory, and disk overhead. Later, with Microsoft's people to experiment again, my query does cause the system to slow down the response. We also experimented with this, and counted the number of rows on this 200 million table, that is, select COUNT (*) from table1, which took 1 minutes, with memory up to 5G, the disk subsystem loaded and the CPU suddenly raised. This shows that the operation of billions of tables can be very serious in reducing efficiency.

2. The disk allocation for the entire server is the same, the database library accessed by the site is located in the disk array, and our statistical temp library is in D, and C and D seem to be a disk group, which is equivalent to the C disk. A large amount of disk I/O to our database can cause the system to slow down. So when my query is large, it slows down the entire system of the server.

3. Database data files from the growth of 1024k each time, data file space has been nearly used up, but to occur growth, and the growth of space requirements should be relatively large, so will continue to apply for growth, resulting in large disk overhead.

4. The operation involves the delete operation, will form a large number of logs, and last week, after the expansion, found that the log file smaller than before, is estimated to be rebuilt, yesterday's operation will cause the log file will also be growing, will also cause the load of the disk increased.

Solution:

For one:

Avoid large table operations, all operations can be separated by the province or time, so whether from the time or geographical dimension, can basically be broken into more than 30 small table operation, or even more. The results should then be merged to avoid the above problems.

For two:

No solution, just recommend that our database also separate to a group of disks, do not compete with the system.

For three:

Timely deletion of useless temporary data, to protect the database space, but also to do space monitoring, once the data file space growth, to the DBA an early warning mail, we received the message can be immediately processed accordingly.

For four:

The log file has now risen considerably, we perform a truncation log action to keep the log file space use at a lower level.