SQL Server 2008r2 keeps hogging CPU not responding after a while online 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.
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