Redgate SQL Prompt is useful for this kind of thing. It's not as intuitive as you would describe but it will help you identify unused variables, parameters and columns, which can be helpful.
As Scott said - this is more of a human endeavor. The low-hanging fruit I look for are things such as (and I'm pulling stuff off the top of my head):
(Coined by Jeff Moden as Row-By-Agonizing row)...
This includes cursors, loops, triangular joins, scalar user defined functions, and using recursive CTEs for counting. Finding and replacing RBAR with asset-based code usually yields the highest ROI for me.
2. Bad functions
As far as I'm concerned, any T-SQL user defined function that includes a BEGIN and END (such as multi-statement and scalar functions) will slow you down horribly. Replacing them with inline Table Values functions is the solution.
That's all I have time to mention but, if you can get these things under control you'll be way ahead of the game.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001