How to remove waits and modify primary index?

How to remove waits? How to modify primary index?

avatar image By opd_dba 0 asked Oct 30 at 11:06 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Your question is very vague, the more specific a question you can ask, the more specific the answer will be.

You may be able to reduce the IO waits by updating the statistics on some of the tables, the optimizer is estimating a much higher number of rows than actual rows being returned. If you look at the Top Operations tab of Plan Explorer, you will see Object10 and Object8 are both in the top 3 of cost and have large discrepancies between Actual Rows and Est Rows.

If you can remove the use of FUNCTION1 in the WHERE clause, you may be able to utilize a better index. When you use a function on a column data in the where clause it can no longer utilize an index for that table, frequently resulting in a scan of a table or index.

Check for indexes that line up with the ON conditions in the JOIN.

In the Wait Stats tab, we see the SOS_SCHEDULER_YIELD wait, and it a hyperlink to the same page I linked it to here. Paul Randal does a good job explaining this wait and has a link to another article he penned about this wait type.

If you need to modify a primary key, you will have to drop the index/key and recreate it. On a large table, this will take time and resources, plan accordingly.

avatar image By SDyckes2 81 answered Nov 02 at 07:36 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x2

asked: Oct 30 at 11:06 PM

Seen: 22 times

Last Updated: Nov 02 at 07:36 PM