SQL Server 2008 T-SQL select hanging, but not dead locked

From the looks of your query I believe that the process is waiting on a spill to tempdb. You will need to look at the output of

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = <SPID for the SP>

to get a better idea of whats occuring in the background, Adam Machanic's sp_whoisactive is a great tool for simplifing the collection of details about a queries execution.

Also make sure that autogrowth is set correctly for your database and tempdb with sensible auto growth amounts for the data and log and that the number of VLFs in the log files is under a 100. Look at his post for details 8 Steps to better Transaction Log throughput