Sql-server – Web application using SQL Server 2008 starts hanging

lockingsql-server-2008

We have a custom web application that uses SQL Server 2008 (10.50.1600). It's worked fine for years, but lately web pages that return data "instantaneously" have begun showing the "endless swirly blue thing" in IE. Something is causing queries to run slower, lock, contend or something else. Doesn't seem to be deadlocking, as I am not seeing deadlock errors at the user level, which I have (very rarely) seen (and know they will trigger a specific error).

If users close out of IE completely and go back in, they usually are able to do the previous work without this hanging behavior (well, until such time as the hang happens again).

How would you begin to diagnose the source of this issue?

Best Answer

I would check at logs, 1) IIS Logs 2) Windows Logs 3) SQL Server Logs

I think logs usually gives away a hint in case of any issue.

Related Solutions

Sql-server – How large is “too large” for SQL 2008 Web Edition

This is a very tough question to answer and the only correct thing to say is "it depends". Yes, it's trite but that doesn't make it untrue. That being said I'll try my best to give you an answer (use Standard) and explain the thought process behind it.

I'm not familiar with the "Web" Edition of SQL but I can certainly rattle off the differences between Standard and Enterprise. At the risk of a massive generalization I'd say that if you're looking at between 10 and 100GB for a single DB and you're not sure you need Enterprise features, the upfront cost difference combined with the ease of upgrading the SKU (Edition) if/when you need to mean that choosing Standard over Enterprise probably makes the most sense. I'm guessing that "Web" edition is some semi-stripped down Standard version and personally I'd avoid it based on the "Web Workloads Only" disclaimer. What does that even mean?!

So how does one truly determine if they'll need Enterprise features? Well, the best way I can think of is to download the Standard and Enterprise trials (or get an MSDN license) and do performance testing with both editions. If you find you can't hit/exceed your performance targets with the lower edition identify the bottlenecks and then see if the Enterprise features would assist. If so, upgrade & make the needed changes to use the features then re-test. In my experience the main features one makes use of in Enterprise are (in order of most common to least):

online reindexing
row/page compression
backup compression
filtered indexes
partitioning & partitioned indexes
indexed views
parallel index operations

And finally... When moving up from something that's running well on SQL Express I don't think there would be many cases that justify the jump to Enterprise. Alone, the additional system resources that the full version of SQL can use should deliver big gains.

Sql-server – SQL Server 2008 T-SQL select hanging, but not dead locked

From the looks of your query I believe that the process is waiting on a spill to tempdb. You will need to look at the output of

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = <SPID for the SP>

to get a better idea of whats occuring in the background, Adam Machanic's sp_whoisactive is a great tool for simplifing the collection of details about a queries execution.

Also make sure that autogrowth is set correctly for your database and tempdb with sensible auto growth amounts for the data and log and that the number of VLFs in the log files is under a 100. Look at his post for details 8 Steps to better Transaction Log throughput

Best Answer

Related Solutions

Sql-server – How large is “too large” for SQL 2008 Web Edition

Sql-server – SQL Server 2008 T-SQL select hanging, but not dead locked

Related Question