Optimizing SQL And Estimation Plan

The day before yesterday we were under great pressure as production database took 100% of CPU for a long time which made most users unable to work. This thing happened a few times a month, usually by the end of a month, but nobody knows why. Since I was new to the company, I didn't participate in the troubleshooting last month. But yesterday my team leader asked me to be involved and I jumped to work(even though I don't know much SQL optimization:( ).

Fist we used Log Explored to pinpoint the bad guy, that is a stored procedure "OrderSummaryToInvoice". And then trace into the code, I realized they used many weird statements like: where @orderno like '%'+char(39)+ltrim(rtrim(order_no))+char(39)+'%' and
invoiced_ind='Y') . Here order_no is the primary key of that table. Is this a good choice? I doubt it. So, I tried to execute some of them with fake data and as it turned to take 2 or 3 minutes to return from a table with 500,000 records. But when I changed it to: where order_no in (select value from fn_Split(@orderno, ',')) and invoiced_ind='Y'). The time cost was liminated, it took zero second!

As I realized later from estimation plan, "like" doesn't make much use of index, so SQL server has to compare row by row which is very slow.

Most time I used fake data to find out how much time it will take to execute certain SQL statements, it was fine, but I have to wait if some of them executed too long. Later I realized it will be much much more faster with estimation plan in Query Analyzer (and it
even works for a whole stored procedure!) and it will tell us the time cost division between statements, what a relief! I used estimation plan to remove another bottleneck which took 75% of the time in the revised version of the stored procedure this morning, now user could finish his/her task within 20 seconds, before then, it was 30 minutes, even more.

From the code I know there are still some places maybe improved, but since 20 seconds is good enough, I won't improve it now.