SQL Server high CPU-intensive workaround

Trigger reason: At the end of the month when the system checkout the operation of the ERP more, there is a user feedback system between the data transmission for a long time without synchronization. Then to the server to view, did not find a program caused by the problem, looked at the CPU utilization, found that the SQL Server occupancy rate of more than 90%.

Resolution process: First Baidu, look at the possible reasons should be the index problem. Previously there was a careful look at the SQL Server index related books and the way to find optimized indexes. So immediately use SQL Server Profiler to start detecting SQL statement with high CPU usage.

It is detected that the statement that updates the data transfer consumes a high CPU (update t_syncin_log set ... where [email protected]), and then view this table to see that there is no index. A primary key and a f_guid nonclustered unique index are established.

And then re-detection, found that the stored procedure to extract the order information on the CPU is high, open the stored procedure after the discovery of the previously written page script is based on SQL2000 write (not in), because the database has now been upgraded to 2008, so the page is rewritten (row_number Over ... The way).

Result: After two optimizations have been done, the CPU is almost back to normal level.

The index is like the relationship between human beings and food, the reasonable choice is to provide nutrition without causing too much burden.

SQL Server high CPU-intensive workaround