torsdag 29 maj 2008

When to optimize

When getting an optimization task for someone I always try to start as wide as possibly. What type of machine are you running on? What is displayed by the task manager etc... Quickly start a trace to see what is actually happening and then view some DMV's to spot issues.

I've found that quite often the trace shows statements or procedures that by them selves eat up tons of resources. In case you find a top five of tasks that use in the range of 30% or more of cpu or io resources then optimize! Even if these tasks aren't causing locks or other problems it will be such a significant improvement to your client that they will notice.

As a side effect the entire database will run much smoother afterwards and with less load the risk of locks etc will decrease. These tasks are often easy to optimize since you will almost always benefit from trading insert/update/delete cost to select cost by removing or adding indexes.

Inga kommentarer:

Skicka en kommentar