Please provide some advice to an accidental DBA.
We are currently hosting in an Azure VM an instance of a SQL Server 2008r2 DB. It is being used to hold data relative to a few websites and it has been online and working 24/7 for 15 months except for one hiccup related to the log file size (which we solved switching to SIMPLE recovery mode).
Well, the problem is that recently every few weeks or so, the MSSQL process starts hogging ~50% of CPU usage in the host machine and stops serving some basic queries (it stucks forever processing the query blocking every other pending or new query). There aren't any processes running (spid > 50) and the daily backups seem to work as usual. The Error log file shows no strange events.
Restarting the DB takes almost 8 hours (which I ignore if it's normal) and only delays the problem a few weeks. I'm not sure at all what may cause the problem, as there is no shortage of disk space and, well, I'm quite unexperienced in these scenarios.
Any ideas? I'm fearing is log-related or somehow involves disk caching but I'm completely lost here. I'd honestly appreciate advice for a newcomer on how-to diagnose and isolate the problem.
Additional info: Some of the queries that the DB dislikes involve TOP and ORDER BY clauses which when removed work flawlessly. Particularly this case amuses me:
SELECT TOP 101 * FROM [table] ORDER BY id DESC
...hangs the DB completely and...
SELECT TOP 100 * FROM [table] ORDER BY id DESC
...works flawlessly in 00:00:00.