Some time ago I developed a simple diagnosing tool to monitor our production SQL Server and today I have a pleasure to present it to the public:) It’s a web application that you can deploy on IIS or run locally on IIS Express. On the main page it shows all currently running requests and open sessions. The screenshot in this case will serve better than a text description so just have a look:
We are using Elmah on production to log problems in our web applications. Elmah logs are stored in a MySql table which structure is a slightly modified version of the elman_error table from the original Elmah script (I changed the engine to InnoDB and added partitioning over TimeUTC column):
CREATE TABLE IF NOT EXISTS `elmah_error` ( `ErrorId` CHAR(36) NOT NULL , `Application` VARCHAR(60) NOT NULL , `Host` VARCHAR(50) NOT NULL , `Type` VARCHAR(100) NOT NULL , `Source` VARCHAR(60) NOT NULL , `Message` VARCHAR(500) NOT NULL , `User` VARCHAR(50) NOT NULL , `StatusCode` INT(10) NOT NULL , `TimeUtc` DATETIME NOT NULL , `Sequence` INT(10) NOT NULL AUTO_INCREMENT , `AllXml` TEXT NOT NULL , PRIMARY KEY (`Sequence`, `TimeUtc`) , INDEX `IX_ErrorId` (`ErrorId`(8) ASC) , -- UNIQUE can't be created if it does not include all partition columns INDEX `IX_ELMAH_Error_App_Time_Seql` (`Application`(10) ASC, `TimeUtc` DESC, `Sequence` DESC) , INDEX `IX_ErrorId_App` (`ErrorId`(8) ASC, `Application`(10) ASC) ) ENGINE InnoDB DEFAULT CHARACTER SET latin2 partition by range columns(`TimeUtc`) ( partition before20130719 values less than ('2013-07-19 00:00') );
In this post I will show you an interesting problem that we experienced when querying a SQL Server database with Dapper. I will use a simplified data model and a sample application so you could reproduce the issue on your own. Our sample table will look as follows:
create table Stats ( StatsDay datetime not null, EventName varchar(100) not null, Item varchar(100) not null, Value int null, constraint PK_Stats primary key(Item,EventName,StatsDay) )
The majority of modern applications use ORMs as a way to connect to a database. Those libraries simplify the usage of the underlying ADO.NET API in a way that we might even forget that under the hood we are dealing with a relational model. This object-oriented wonderland usually lasts till the moment when the first SQL exceptions crop up. If we configured our ORM correctly we should be able to diagnose database problems with just logs that it provides. But what if we didn’t or if the problem lies deep in the ADO.NET layer? We might then try to use a debugger or a SQL Server Profiler. Both those tools, although great, are usually too heavy (or too invasive) for a production environment. Fortunately there is still one option left – ADO.NET ETW tracing. In today’s post I will show you how to turn on this type of tracing and how to use it to quickly diagnose some database problems.
Continue reading Diagnosing ADO.NET with ETW traces
In this post I’m going to show you how to diagnose SQL exceptions using memory dumps. Imagine you have a web application deployed on a production server. Your application is using Elmah configured to log all exceptions to a dedicated table. One day you receive information that users are unable to make orders and in the Elmah log there are lots of
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp; task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp; task, Boolean asyncWrite) ...
No more information available. If you are using NHibernate you may turn on SQL logging but, if you have thousands of users, this will have a negative impact on the application responsiveness. Another idea might be to use ADO.NET ETW logs – even though this way we will minimize the performance drop, it’s still not a perfect solution. ETW logs weigh hundreds of MB and contain a tremendous amount of information, which you need to filter and analyze. I plan to write about this type of logging in the future. For now, let’s take a third approach: create a production memory dump when the application breaks and analyze it locally in the debugger.
Continue reading Read last executed SQL statement from a memory dump