1

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.

  • You must first add the output of select @@version – Shanky Feb 14 '17 at 14:32
  • @Shanky Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Data Center Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) – FPS Feb 15 '17 at 12:58
  • There is hardly any point in troubleshooting issues related to high CPU if you are running RTM version. You must apply SP3 and see if the issue subsides – Shanky Feb 15 '17 at 14:39
3

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
|improve this answer|||||
  • I had just installed sp_Whoisactive this morning before asking for help. It shows no processes running except the following query, which I launched this morning and I suspect is also beyond the expected time (4 hours by now): ALTER DATABASE [table] MODIFY FILE ( NAME = N'table_Log', SIZE = 200400896KB ). BTW thanks on the article regarding the Top 100 issue. That was a very nice read and very helpful. – FPS Feb 14 '17 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.