question

bappaghoshjis avatar image
bappaghoshjis asked

Performanc tunning of a sp

HI,

I am attaching a execution plan with the post. There index insert is taking 93% cost. Please suggest me to tune it.

Thanks,

Bappa Ghoshloaddata.jpg

performance-tuning
loaddata.jpg (235.0 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

A clustered index is the actual table. If the execution plan shows the major part of the cost for the plan is the clustered index insert, I don't see that there's much tuning to do execution plan wise.

There might still be tuning to do in that insert, but that won't be shown in the execution plan.

Couple things to check/consider, if you need to speed up that insert:

- Check if you get many bad page splits during the insert (you can check this using extended events). If this is the case, you may want to rebuild the clustered index with a lower fillfactor than the default 100%. You may also want to redesign the clustered index, to avoid those page splits (and thus avoid fragmentation on the table). A page split occurs when a page where the inserted row is to be inserted is full. If the table is clustered on an ever increasing column (like an identity-column), your inserts will only cause page splits on the logically last page of the table. But if you have it clustered on something not ever increasing, your inserts will cause a row to be inserted between two other pages and in that case, when a page is full, there will be a considerable amount of I/O happening just to allocate a new page, move rows from the original page to this new page and update pointers between the pages.
- Check your disk I/O speed. The most rudimentary way to check will be to open up Resource Monitor and check response time and disk queue length on the data- and transaction log disk during the insert. You have bad response times and/or high disk queue length for a number of reasons. Before looking into faster disks, you should check that you have transaction log and tempdb on disks separate from the data disk.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your reply @Magnus Ahlkvist

0 Likes 0 ·

Please also let me know if there are any tips and tricks to tune performance in sql server, I am relatively new in performance tuning and no one to help me now.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.