Analysis

ORM Profiler has a deep analysis system which can find many problems in the profiled data. By default the recommended analysis functions are enabled with default arguments. To specify different arguments and enable different / more analysis functions, click the Analysis Settings button on the Home tab of the UI. The Analysis settings are preserved to disk, and re-loaded the next time the client is opened.

A snapshot can also be re-analyzed with different arguments and analysis functions. To do so, click the Re-analyze button on the snapshot's tab.

When an analysis function is triggered, it assigns an Alert to the element which triggered the analysis function, e.g. a command or a connection. Alerts (and exceptions) are visually represented in the various views in the ORM Profiler client. By hovering over the alert and exception icons in the Alerts column, you can quickly check which alerts / exceptions occurred by examining the tooltip. Clicking the element which has the alert assigned will show the element in the bottom pane of the client GUI where you can view the alerts in the Alerts tab.

Supported Analysis functions and Alerts

Below you'll find a brief outline of the various alerts the analysis functions are able to find.

Alert: Slow Transaction

The Slow Transaction alert occurs if the time taken from Begin Transaction to Commit/Rollback transaction is higher than a specified threshold.

Alert: Connection Stayed Open Too Long

The Connection Stayed Open Too Long alert occurs if the time taken from Open connection to Close connection is higher than a specified threshold.

Alert: Undisposed Connection

The Undisposed Connection alert is an alert which occurs when a connection is discovered in the snapshot which hasn't been disposed by the profiled application.

Alert: SELECT N+1

The SELECT N+1 alert is an alert which can mainly occur when the O/R mapper uses lazy loading. It's a typical situation where the user has no knowledge of the intense database activity originating from the same source.

Example: Fetching 10 orders using a SELECT ... FROM Orders WHERE ... statement, followed by the same SELECT ... FROM Customers WHERE ... statement multiple times: the Customers targeting SELECT statement is originating from the fact that for each read Order entity a new query is executed to fetch its related Customer entity.

Alert: SELECT N+1 Across Connections

The SELECT N+1 Across Connections alert occurs when a command is executed multiple times within its own connection, while it's equal to another command in another connection executed previously by the same thread.

Example: Using a loop, a user calls a method multiple times which creates a context / session / ORM construct to fetch data. This results in SELECT N+1, however as it occurs on new connections, it's not detected as a normal SELECT N+1

Alert: Unbound Resultset

The Unbound Resultset alert is an alert which occurs when a SELECT statement is used without a limitation on the resultset. This means there's no row limit and no WHERE clause. It's not bad in general to have unbound resultsets, however they can slow down an application over time when the target(s) of the query become large so more and more rows are fetched by the same query.

Example:

SELECT CustomerId, CompanyName FROM Customers

Alert: Select Outside Transaction

The Select Outside Transaction alert is an alert which could signal a situation that could result in a deadlock situation.

Example: Connection C is opened, Transaction T is started over C, and within T, commands C1 and C2 are executed. Then, on the same thread, a new connection D is opened to execute command C3. After that, T is committed. This can lead to deadlocks because the locks set by C1 or C2 can make C3 block as it's executed outside C because it's executed outside T.

Alert: Large Resultset

The Large Resultset alert is an alert which occurs when a query results in a read of more than X rows (on the datareader). X is configurable.

Alert: Slow SQL Statement

The Slow SQL Statement alert is an alert which occurs when a SQL statement execution takes more than X milliseconds. X is configurable.

Alert: Slow Data Read

The Slow Data Read alert is an alert which occurs when the time taken to read all rows using a datareader is more than X milliseconds. X is configurable. X is the time taken for reading the rows, not for executing the SQL statement: X starts counting as soon as the datareader is created.

Example: A SELECT statement is executed rather quickly, however due to the massive amount of data (e.g. it contains a lot of big blob/image data fields) the materialization of entity objects using the datareader takes a long time, e.g. several seconds.

Alert: Parameter Size Fluctuation

The Parameter Size Fluctuation alert occurs when the same query is executed with parameters with different max length. This occurs in some o/r mappers which could lead to execution plan mismatches.

Example:

SELECT CustomerId FROM Customers WHERE Country==@p0

The first time this query is executed, the max length is 10, the next time it's 11.

Alert: Multi-threaded Connection Usage

The Multi-threaded Connection Usage alert occurs when a connection is used by multiple threads to execute commands over it. This can lead to unwanted results.
Example: Thread X creates a connection C, stores C somewhere and thread Y then continues to use C. Typically this happens when e.g. an adapter / context / session is shared in an ASP.NET Application object.

Alert: Updates In Loop

The Updates In Loop alert occurs when a lot of UPDATE queries are executed within the same connection for the same target. This signals a situation where multiple entities are read into memory, they're updated, and then persisted again. This can also be done with a direct Update statement in some cases. The minimum is configurable.

Example: UPDATE Customers SET ... WHERE ... statement is executed at least n times.

Alert: Deletes In Loop

The Deletes In Loop alert is equal to the Updates In Loop alert: it occurs when a lot of DELETE statements for the same target are executed.

Alert: Selects In Loop

The Selects In Loop alert is equal to the Updates In Loop alert: it occurs when a lot of SELECT statements for the same target are executed. This can be confused with Select N+1 Alert, which has Selects called in a loop, but only after a parent Select (which usually returns as much rows as in the loop). Selects In Loop Alert is not occuring when Select N+1 is applicable.

Alert: Too Many SQL Statements Per Connection

The Too Many SQL Statements Per Connection alert occurs when more than n commands are executed over a single connection. n is configurable.

Alert: Idling Connections

The Idling Connections alert occurs when a connection is open for X ms and activity over the connection (commands execution, datareader reads) are only Y ms of that X ms. The alert is raised when X-Y>=Z where Z is configurable.

Example: (LLBLGen Pro specific) Opening a connection manually (either by starting a Transaction in Selfservicing or using OpenConnection on an adapter), then doing something non-persistence wise, then executing some persistence code. It can also be the user has written code which triggers the open / close of the connection in code high up in the call chain which keeps the connection open longer than needed.

Alert: DML Without Transaction

The DML Without Transaction alert occurs when multiple INSERT / UPDATE or DELETE statements are executed without a transaction within the same connection, or using multiple connections (e.g. one for each statement), all within the same thread. The statements have to occur in a block, without being separated by a transaction start/commit.

Example: Calling multiple methods which do save or delete actions for the same entity / entities: within the methods a new connection is started, but across the methods they're not running in the same transaction

Alert: Massive SQL Statement

The Massive SQL Statement alert occurs when a SQL statement is executed which is longer (in characters) than a set threshold. This alert is mainly meant to find large statements which might have been created using slow LINQ statements so the user should look into optimizing them. The time spent in a LINQ provider isn't determinable by ORM Profiler, so in order to find the potentially slow LINQ queries, use this alert. This alert is mainly meant for Entity Framework which LINQ provider is slow when it has to produce large SQL statements.

Caught Exceptions

Exceptions are not seen as an alert, but are reported separately with the element which caused the exception.