This is a question asked by a netizen In the QQ Group I joined. He said that an INSERT operation on a table will time out. Confused. Ask for help from people in the group. I just knew something about this and answered the question about optimizing the clustered index. He does not understand how to do it, and does not seem to know what a clustered index is. I told him that you can check whether there are many disk IO records when you insert records. He said that his disk has been replaced by RAID, so it is difficult to check disk IO. OK, I continue to analyze it for him: Your insert is slow because every time you insert a new record, the database server will be inserted in a middle location, causing a large number of disk files to be read and written. he said: how can this problem be solved? My hardware configuration can no longer be upgraded. I said: You can check the clustered index to optimize the clustered index. He does not seem to understand it. I wrote the following:
Is that true:
Use WanerSoft2007
Declare @ table_id int
Set @ table_id = object_id ('dbo. ws_userproinfo ')
Dbcc showcontig (@ table_id)
Dbcc dbreindex ('dbo. Ws_UserProInfo ', '', 0)
I looked at it and said it was a redo index batch process, not a clustered index. I continued: clustered index refers to the physical placement order of records, which is done according to clustered index. Is the most important index. Clustered indexes are well designed and can achieve good performance in multiple database operations. Poor design will lead to huge performance differences, such as the insert operation. When the design is good, the new record only needs to be added at the end of the physical, and there is no need to move a lot of records, so fast. When the design is poor, the new record is inserted at a certain location in the middle of the physical, so it is necessary to move a lot of records and there will be a lot of disk IO. He went for a while and came back to paste a picture for me:
I read the figure and told him that the clustered index should use short fields and one-way increments. Date and identity can be selected. He understood this time and asked if the bookid and nclassid fields were removed from the clustered index. I said yes, but he did it. Then he came back and said, That's Great. The problem has been solved.