I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it.

enter image description here

You can see that everything seemed to be OK until about 3AM this morning and then all of my requests started timing out. This isn't the first time this has happened, but after I push new changes to the server it starts up OK again. It doesn't seem to happen under low load, but has also ran at high load for hours perfectly fine. I don't think 3AM is an especially high load time.

Things I have done / to note:

  • Dedicated server, MSSQL and I'm using NHibernate as an ORM to generate my queries.

  • The server is pretty much just a data store. 99% of the queries are very basic insert/selects. I have one bulk insert for creating a large amount of playlist items.

  • I have batching enabled. It is set to 50 by default and 200 if >1000 playlist items are about to be written to the database. This reduced the spikes in my DB's response times.

  • I have my command timeout set to 5 seconds. It was set at the default, 30 seconds, and I was still experiencing the issue. The problem seems to have stayed away for a longer period of time after setting it to 5 seconds.

  • I have tried running exec sp_updatestats as described here: https://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation# which did show some indexes being updated, but did not result in any real differences.

  • I have set my databases connection isolation level to ReadUncommitted: configuration.SetProperty("connection.isolation", "ReadUncommitted");

  • I specify the default DB for NHibernate performance as described here: configuration.SetProperty("default_schema", "[db896d0fe754cd4f46b3d0a2c301552bd6].[dbo]");

What on earth should I be looking at to try and get a grip on what is causing my database to lockup? It's showing only 1% CPU usage so I don't think it is overly taxed. It shouldn't be a long running query if I have 5 second caps on everything. It's not the indexes. I'm running out of ideas.

In terms of queries -- it's all SELECT user statements. It's just a bunch of people trying to login and access their account. I'm getting about 1000 requests / 30 minutes for SELECT user.

Here's some more info:

enter image description here enter image description here


Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64) Nov 30 2012 17:11:43 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

  • So do you have scheduled jobs that start at 3:00 AM (like index rebuilds, defrag, backups, etc.)? Keep in mind that response time is rarely associated with CPU - more likely it is I/O. Commented Feb 5, 2014 at 18:06
  • No. There's nothing scheduled, it's a completely clean server that is spun up by AppHarbor: appharbor.com It's just got the handful of tables on 1 DB on it. The 3 AM death time I don't think is super special. It died on me at 4PM a couple of days ago and then came back up once I restarted everything. And yeah, it says 1% CPU usage so I don't think it's that! Commented Feb 5, 2014 at 18:10
  • 3
    Couple of things, check what queries are running at a specific time using Adam Machanic's sp_whoisactive stored proc, sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/… That could tell you if you are dealing with locking/blocking or other waits. Also, check your waits to see what the bottleneck is. Brent Ozar has a great query for that here, brentozar.com/responder/triage-wait-stats-in-sql-server. That should also let you know specifically what bottlenecks you are facing.
    – njkroes
    Commented Feb 5, 2014 at 18:19
  • 1
    It shouldn't but Read Uncommitted still requires a schema lock so there is a possibility that if your query can't obtain a schema lock, that it could be blocked. However, I doubt that is your issue. More likely that you are waiting on something like storage.
    – njkroes
    Commented Feb 5, 2014 at 18:31
  • 1
    Ha! It's showing only 1% CPU usage so I don't think it is overly taxed. The graph you've added tells a different story. It's not completely CPU-bound, but it's a far cry from 1%. How's recompilation? Is all the hibernate junk ad hoc SQL? Do you have the optimize for ad hoc workloads setting enabled? What version of hibernate? @@VERSION of SQL Server? Commented Feb 5, 2014 at 22:19

1 Answer 1


I was setting indices on my PKs, but I did not have indices on my FKs. Most of my queries were of the form: "Select Playlists p where p.UserId = 'XXX'"

Even though I had an index on User's ID, I did not have one on Playlist's UserID. This was root cause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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