Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: http://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

@@VERSION:

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)

share|improve this question
    
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. –  Aaron Bertrand Feb 5 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! –  Sean Anderson Feb 5 at 18:10
    
If this is hosted somewhere, you might want to ask the host what happens on this server at 3 AM. It could be their maintenance interfering with you, or a "noisy neighbor." Keep in mind that a noisy neighbor doesn't necessarily have to be on the same server as you - could relate to SAN or network overhead. –  Aaron Bertrand Feb 5 at 18:16
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 Feb 5 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 Feb 5 at 18:31
show 7 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.