I have an ASP.NET web application written with C#. It uses SQL Server 2008 database. It seems that some of the SQL queries are deadlocking the SQL server and I'm also experiencing bad SQL Server performance. To find out which, I thought to add a diagnostic event logging, but I'm not sure how. If I was writing a plain Windows application, I'd do that with the Windows Event Log, or with just a simple text file. So I'm curious, how to do the same from a web application?
-
1for the issue of dead lock, this might help you [stackoverflow.com/questions/12422986/… [1]: stackoverflow.com/questions/12422986/…Singleton– Singleton2013-03-01 22:01:03 +00:00Commented Mar 1, 2013 at 22:01
-
added a sql server tag for you.RandomUs1r– RandomUs1r2013-03-01 22:23:18 +00:00Commented Mar 1, 2013 at 22:23
Add a comment
|
3 Answers
There are many options for logging from an ASP.Net web application.
NLog is one worthy of consideration because it performs well and offers a wide range of log targets based on severity including text file, database, email, or a GUI log viewer such as Sentinel.
Having said that, application-level logging is probably not the best way to diagnose specifically SQL Server deadlocks. I would suggest starting with SQL Server Profiler for that task
1 Comment
Jeremy
I agree with @ErikJ NLog and Log4Net are great solutions.
.NET has build in loggin system with rich features and flexible configuration since version 2.0. You can start with this article on MSDN For some reason most people do not use it though and prefer either Log4net or NLog for logging. First is port of java logging tool and second is rethinking of it. I personally prefer to use TraceSource with filters. ADO.NET natively support it as well.
7 Comments
c00000fd
Good deal. Where is that built-in log saved -- file system file, or database?
vittore
@c00000fd wherever you configure it to be saved. Here is information on listeners msdn.microsoft.com/en-us/library/4y5y10s7.aspx
Eric J.
IMHO the built-in logging framework lacks the completeness of a solution like NLog or Log4Net.
vittore
@EricJ. can you give an example ?
Eric J.
Example: What do you have to do to with the built-in frameworks to write Critical & error items to a text file, database and email address; warning items to a text file & database, and other items just to a text file, but for the debug build also write everything to a GUI based viewer to facilitate development? With NLog it's just a few lines of configuration.
|
Deadlocking occurs on the SQL side, it's impossible for ASP.NET to be aware of the full picture.
I recommend checking your queries for a WITH LOCK statement and before removing it figuring out why it's there to begin with.
Otherwise, if you find an offending query via SQL server Diagnostics manager... or if you can reproduce the deadlock purely on the SQL side, add WITH NOLOCK to that query. T
his is by far the simplest solution, better solutions involve getting to the root cause of why the deadlock is occurring in the first place.