Total votes: 0
Print: Print Article
Please login to rate or to leave a comment.
Published: 23 Nov 2009
Download Sample Code
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.
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
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.
Color Scheme to Change
I was told by Ayende that this color scheme is not yet finalized and that it may
change. He is not yet happy with it!
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
Excessive Number Of Rows
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
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
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
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 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
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.
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
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
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.
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.
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.
You might also be interested in the following related blog posts
SQLAuthority News Meeting SQL Friends SQLPASS 2011 Event Log
SQLAuthority News SQLPASS Today FREE 100 SQL Wait Stats Book Print Copy Book Signing
SQLAuthority News SQLPASS 100 SQL Wait Stats Book Print Copy Giveaway A Book Every Minute for an Hour Tomorrow
SQLAuthority News SQL Server Health Check Service Speed UP SQLServer
Comparing LINQ to SQL vs the classic SqlCommand
Connecting to SQL Azure with Telerik OpenAccess
Migrating to Postgresql with my friend NHibernate
Migrated from Community Server to DasBlog
LINQ to SQL, Lazy Loading and Prefetching
Please login to rate or to leave a comment.