SQL Server 2008r2 keeps hogging CPU not responding after a while online
SQL Server CPU usage stuck at 50% and no process running is not possible, so I suggest that you troubleshoot this condition better. sp_WhoIsActive is a great tool to capture active sessions on your instance.

Regarding the long restart time, I concur that 8 hours is a disproportionate time for recovery. This can only mean that you're undoing something that has been running for quite a while and has not been committed to disk yet. What that "something" is must be determined before cycling the instance. Again, sp_WhoIsActive is your friend.

Regarding the TOP 101 sort, this is a well known problem: Paul White wrote a nice article on the topic back in 2010.

Basically, SQL Server can use an alternative sort algorithm to sort up to 100 rows, but has to switch to full sorting when more than 100 rows are requested, hence the abnormal performance difference.

Solutions range from "dirty tricks" to rewrite the same query in ways that trick the optimizer to what IMHO is the best solution: avoiding the sort altogether by adding an index.

Reasons why this hangs the machine completely are beyond me and would need additional investigation. Things you could look into are:


tempdb filling due to enormous spills
long blocking chains