By working at some performance issues with SQL Stored Procedures, Views and Functions I had to make some investigations on SQL performace, how to do that, I've been searching a lot to find some tips.
So the first tip is: Avoid using CURSORS.
Yes, avoid cursors, do everything to avoid cursors.
Often time people say to me that for their case it is not possible to avoid cursors, and often times I prove them that it is possible.
When there is just a few things that you could do with a set of data then try to do that in some statements by joining the data, instead of going through each item.
It is not always clear that cursors could be avoided, this is why you have to think several times if it is really not possible to avoid them.