Introduction

In this article we will be taking a look at the new LINQ to SQL Profiler from HibernatingRhinos. This tool gives you a view into the goings on of LINQ to SQL. Not only does it allow you to see the SQL that is generated by your LINQ queries but it also shows you information about your connections, queries, as well as alerting you to all sorts of information that you might otherwise not know about.

We will see what it takes to get the software, install and configure it, and then wire it up for use. Once everything is up and running we will then run through two different querying scenarios. The first query will be a simple get some records where something contains something else. Easy query. But this easy query will be performed on a table of 1,000,000 records. The second query we will do will be on a smaller dataset but will require a little more effort from LINQ to SQL. The second query will get a list of keywords and then iteratively perform a count of instances of that keyword in another table of data. Although this may not be a production specific scenario it will give us a fair amount of data to look at in the LINQ to SQL Profiler.

LINQ to SQL is here to stay!

I think that there are many people out there that will agree with me that LINQ to SQL is a great little tool. It is easy to get up and running, well integrated into the Visual Studio environment, and just plain works. It is unfortunate that there was a time when it seemed that Microsoft was going to yank that product out of the .NET framework. But no worries now, it seems that LINQ to SQL is going to stay around for a while as it has a list of upgrades to its feature set in the upcoming .NET 4 release.

What's going on under the covers?

Anyone who has ever used LINQ to SQL knows that it allows you to specify your domain objects by performing a one to one mapping on top of your database tables. From there you can simply write a LINQ style query against your DataContext (the entry point to communicating with your database) and magic happens. LINQ to SQL will create parameterized queries for you based on the LINQ queries that you specify. 95% of the time these queries are spot on. However, I have found that when LINQ to SQL is wrong...it is WAY wrong! But how do I determine what is going on under the covers?

What were my options to view the generated SQL?

Many traditional DBA style folks would often wonder what was going on under the covers of LINQ to SQL (as they would with any very cool ORM product that threatens to move their cheese!). Up until now there were a few rough options that you had to figure out what sort of SQL was being generated on your behalf. You could turn on the profiler at the database level and watch as calls are shuffled over the wire to speak with your database. You could debug up to your query execution and then step into the debugger to see the Results View which displayed the query results. There is also Scott Guthrie's plugin for the SqlServerQueryVisualizer which shows you in real time the query that was generated (again while debugging). There is also the LINQ to SQL Debug Writer (which came out shortly after LINQ to SQL's release). Here is a blog post that explains most of these options.

Introducing LINQ to SQL Profiler

Up until now we have a few very cool ways to inspect our query as they were happening. This was ok for fumbling about your application trying to determine bottlenecks and other performance hiccups. They weren't what I would call an enterprise oriented product for tracking what your data access layer was doing. Here is where Ayende Rahien's product, LINQ to SQL Profiler comes in. This product, now entering beta and going live for all to see, seems to be just what we LINQ to SQL developers have been waiting for.

What does LINQ to SQL Profiler provide me with?

LINQ to SQL Profiler provides you with a view of all the current activity. It also allows you to see what your DataContext objects are up to by viewing their activity. From there you can easily view the actual SQL statement details. You also have the ability to view the stack trace for a query. Linq to SQL Profiler also provides you with an easy way to view pre-specified alerts to quickly catch known problems in L2S query statements. Alerts can be of a generic query case or you can also view a summary of alerts for a given DataContext. There are also screens to view queries by method and a way to view unique queries. Overall LINQ to SQL usage and application statistics are easily accessible as well.

I'm sold, how do I get LINQ to SQL Profiler running?

Getting LINQ to SQL profiler is quick and painless. Go to l2sprof.com/trial and sign up for a trail version of the software.

Once you have clicked Get Trial License you will receive an email with a generated trial license. Save that to the directory you wish to put all your other Linq to SQL Profiler stuff. From there you will need to download a copy of LINQ to SQL Profiler. This will take you over to the HibernatingRhinos.com site to get the latest build of LINQ to SQL Profiler. At the time of this writing I am downloading LinqToSql.Profiler-Build- 549.zip. From there I am extracting all of the contents of the downloaded zip into a binaries directory I created (I follow a trunk>binaries and trunk>source style directory structure). Once I have extracted all of the contents of the downloaded zip I can then run the the L2SProf.exe. The first screen I will see is a window prompting me to enter my license file. I browse out to located my license file (in my case it was named Andrew Siemer.xml). Then I am presented with a licenses agreement page which I accept. As I am working on a fairly annoying Windows Vista machine I am then prompted with a Windows Security Alert box asking me if I want to block this application or not. I clicked the unblock button to keep things moving along. Once I get this far I am then presented with the Linq to SqlProfiler Beta screen.

Now that LINQ to SQL Profiler is installed, what's next?

This question is a bit more tedious to answer. We first need to create a quick console app, a simple schema, and some linq to sql queries. Then we can look at LINQ to SQL Profiler in action.

To get started I am going to add a console application to my source directory. I am going to call my project L2SProfDemo. Next I will add a new LINQ to SQL Classes item to my project. Do this by right clicking on your project and selecting add new item. From the add new item window choose the LINQ to SQL Classes item. Name it My.dbml as whatever name you choose will ultimately be transformed into a YourNameDataContext class which you will have to work within your code for ever after (well...sorta). By using My I will have a MyDataContext in code. Click Add. This will add all the LINQ to SQL items to your project and then open up the LINQ to SQL design surface when it is done. The next step is to create some sort of a schema to work with.

In this example I am going to work with the schema from my Lucene.NET vs SQL Server Full-text series (which I am still working on). In this schema I have a Paragraphs table, a Posts table, and a Tags table. I used the Paragraphs and Tags table to generate 1,000,000 fair sized records in the Posts table. This should give us some funkyness to look at in the profiler!

As you can see this is a fairly simple schema for us to play with. Now, on to the code. We will start by defining a query that will get us a collection of Post records that have "asp.net" and "linq to sql" tags and then output them to the console window. Given the number of records in this table, there is a lot output to the window. Running the app tells me that this works. Great.

Now let's plug in the LINQ to SQL Profiler. To do this we need to add references to our console app by right clicking on the project, add reference, then browsing to our binaries directory for the HibernatingRhinos.Profiler.Appender dll. Then we can add a using statement to our Program class:

And then we can add a line of code LinqToSqlProfiler.Initialize() to initialize the profiler :

We can now run our console application to see if everything works as expected. I am going to run the whole query against the 1,000,000 records to see what we get in the profiler.

This code basically ran one query over a million records and returned all the records with the appropriate tags and iterated over them to output them to the console window. The magic question, did LINQ to SQL Profiler work? Yes. Let's take a look at what it found for us.

As you can see in the screen shot above, there is a lot of useful data here even on a simple query. We can see that there was one data context used and that that data context caused an alert (red circle). We can see the statement that was generated by LINQ to SQL to perform the two Contains() filters that we performed. We can see that the execution of the query took 1ms but that the delivery of the results of that query took 211563ms!!! The thing that I like to be aware of which L2S Prof tells us is that one data context was opened, one query was executed, and one data context was closed. This reads as no leaks (thanks to the using statement). Also notice that of the 1,000,000 records looked at the query returned 122,667 rows.

Looking at the Data context usage tab (above) we can see that we generated two alerts actually. The worst one was that we had a large number of rows returned. The other was that we had an unbounded result set. And it shows another time sensitive representation in that this query took 217.82 seconds (same as above but in a different format!). And here is a nice feature, clicking on the read more text in the alerts tab takes us to the l2sprof.com site to expand on the meaning of this alert. Next to the Large number of rows returned alert, I clicked read more, and got this text.

Excessive Number Of Rows Returned

The excessive number of rows returned warning is generated from the profiler when a query is returning a large number of rows. The simplest scenario is that we loaded all the rows in a large table, using something like the following code snippet:

This is a common mistake when you are binding to a UI component (such as a grid) that performs its own paging. This is a problem on several levels:

• We tend to want to see only part of the data
• We just loaded a whole lot of unnecessary data
• We are sending more data than necessary over the network
• We have a higher memory footprint than we should
• In extreme cases, we may crash as a result of an out of memory exception

None of these are good, and like the discussion on unbounded result sets, this problem can be easily prevented by applying a limit at the database level to the number of rows that we want to load at any given time.

Looking at the next error regarding Unbounded results sets I see this data.

Unbounded Result Set

An unbounded result set is where a query is performed and does not explicitly limit the number of returned results from a query. Usually, this means that the application assumes that a query will always return only a few records. That works well in development and in testing, but it is a time bomb waiting to explode in production.

The query may suddenly start returning thousands upon thousands of rows, and in some cases, it may return millions of rows. This leads to more load on the database server, the application server, and the network. In many cases, it can grind the entire system to a halt, usually ending with the application servers crashing with out of memory errors.

Here is one example of a query that will trigger the unbounded result set warning:

If the performance category has many posts, we are going to load all of them, which is probably not what was intended. This can be fixed fairly easily by using pagination by utilizing the Take() method:

Now we are assured that we only need to handle a predictable, small result set, and if we need to work with all of them, we can page through the records as needed. Paging is implemented using the Skip() method, which instruct Linq to Sql to skip (at the database level) N number of records before taking the next page.

But there is another common occurrence of the unbounded result set problem from directly traversing the object graph, as in the following example:

Here, again, we are loading the entire set without regard for how big the result set may be. Linq to Sql does not provides a good way of paging through a collection when traversing the object graph. It is recommended that you would issue an separate and explicit query for the contents of the collection, which will allow you to page through that collection without loading too much data into memory.

That was one query, what else does this do?

Now let's modify our code and look at another form of query (though perhaps less production oriented). This query will get all the tags in the system and then iterate through and count the number of associations that each tag has to the collection of paragraphs...one at a time.

This generates a much different result as it is firing one query to get a collection of tags to work with, and then iterates through that collection to fire off another query to determine the count of paragraphs associated to that tag.

This set of queries executed pretty quickly in comparison to the previous one. Lets look at what LINQ to SQL Profiler has to say about this.

As you can see in the above window LINQ to SQL Profiler is not terribly excited about my approach. It reported that there were one connections made and one closed but that 61 queries were executed. It is complaining that a Select N+1 data access anti pattern has been performed! Here is what that error is:

Select N+1

Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. Take a look at this code sample, then we'll discuss what is going on. Say you want to show the user all comments from all posts so that they can delete all of the nasty comments. The naive implementation would be something like:

In this example, we can see that we are loading a list of posts (the first select) and then traversing the object graph. However, we access the collection in a lazy fashion, causing Linq to Sql to go to the database and bring the results back one row at a time. This is incredibly inefficient, and the Linq to Sql Profiler will generate a warning whenever it encounters such a case.

The solution for this example is simple. Force an eager load of the collection using the DataLoadOptions class to specify what pieces of the object model we want to load upfront.

In this case, we will get a join and only a single query to the database.

Note: this is the classical appearance of the problem. It can also surface in other scenarios, such as calling the database in a loop, or more complex object graph traversals. In those cases, it it generally much harder to see what is causing the issue.

Having said that, Linq to Sql Profiler will detect those scenarios just as well, and give you the exact line in the source code that cause this SQL to be generated.

Other options for solving this issue are futures, part of the PLinqOextension project for Linq to Sql, which are also used to solve the issue of Too Many Queries.

There was also another alert found in the form of too many database calls per session. Here is that error.

Too Many Database Calls Per Session

One of the most expensive operations that we can do in our applications is to make a remote call. Going beyond our own process is an extremely expensive operation. Going beyond the local machine is more expensive still.

Calling the database, whether to query or to write, is a remote call, and we want to reduce the number of remote calls as much as possible. This warning is raised when the profiler notices that a single session is making an excessive number of calls to the database. This is usually an indication of a potential optimization in the way the session is used.

There are several reasons why this can be:

• A large number of queries as a result of a Select N + 1
• Calling the database in a loop
• Updating (or inserting / deleting) a large number of entities
• A large number of (different) queries that we execute to perform our task

For the first reason, you can see the suggestions for Select N + 1. Calling the database in a loop is generally a bug, and should be avoided. Usually you can restructure the code in such a way that you are not required to call the database in that way.

Updating a large number of entities is discussed in Use Statement Batching, and can be achieved by using the PLinqOproject, which is a set of extensions on top of Linq to Sql.

The last issue is more interesting. We need to get data from several sources, and we issue multiple queries for that data. The problem is that we issue multiple separate queries to accomplish this, which has the problems listed above.

PLinqOsupports the notion of query batching, using a feature called futures, which allow you to take several different queries and send them to the database in a single remote call. This can dramatically reduce the number of remote calls that you make and increase your application performance significantly.

This is a lot of information to be bubbled up about LINQ to SQL. And given that this is only an initial release I must say that I am excited to see what the future holds in store. This is almost like the RedGate Ants Profiler for LINQ to SQL database calls.

Summary

In this article we took a look at the previously existing methods for debugging and profiling LINQ to SQL. We then shifted our focus to a new tool called LINQ to SQL Profiler by Ayende Rahien. We looked at what it takes to get the trial, install it, and configure it. We then took a look at a couple different types of query approaches and the information that LINQ to SQL Profiler was able to tell us about each approach. All in all this is a wonderful tool that any LINQ to SQL developer should take advantage of.

About Andrew Siemer

I am a 33 year old, ex-Army Ranger, father of 6, geeky software engineer that loves to code, teach, and write. In my spare time (ha!) I like playing with my 6 kids, horses, and various other animals. This author has published 29 articles on DotNetSlackers. View other articles or the complete profile here.