Generally stored procedure is optimized by removing unnecessary use of Temporary tables, cursors.
Also try to avoid long running queries. Use Clustered index on queries which requires complex join statements. By this way most of the optimization could be performed.
Also if you are using SQL Server Management Studio, Check the "Actual Execution Plan"
of your stored procedure. If there are long running queries, you can right click on the line to see what optimization you require as well.
Always check for Table Scanning. If there are lots of percentage load on Table Scanning, try to build indexes on them.
Happy Coding. Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator