How to remove waits and modify primary index?

opd_dba 2017-10-30 23:06:16

How to remove waits? How to modify primary index?

SDyckes2 2017-11-02 19:36:46
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.