Slow SQL Server insertion

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.