Analyzing Queries with a SQL Profiler

I teach a number of courses that include technologies that wrap up data access–everything from Entity Framework to WCF Data Services to RIA Services. They all simplify the business of working with data–but at the cost of hiding the implementation from the developer. One question that continually crops up in class is: how do I know what’s going on underneath?

There are a number of possible answers. You might, for example, be able to use the Visual Studio debugger to see the SQL statement has been issued–but in some cases there is no easy way to see exactly how your programmatic query has been translated into SQL. And that can be a real problem. You might be issuing massive queries when you only intended to select a small amount of data. And if you can’t see the SQL, you have no real way to know what’s actually going on.

That’s where a SQL Profiler comes in.

In a recent blog post, I talked about IQueryable and the Web API, and a reader posted a comment regarding the dangers of using oData for filtering–specifically, that all of the data would be pulled back into memory before being paged at the oData layer. This is just the kind of situation where a SQL Profiler helps you get to the bottom of what is actually happening: is the oData filter being incorporated into the query, or run against the return from the query? Let’s take a look and find out.

First, we need a SQL Profiler–and there’s a free one available here. (If you like it, you might consider going to the DataWizard Web site and buying the full version).

The profiler gives us a number of options:

sql analyser options

You can check out performance:

performance screen

Get some insight into processes:

Application Dashboard

Or you can trace individual SQL statements, which is what will allow us to determine how our query is being translated into SQL.

In this case, I’ve issued an oData query against the Web API and requested that only the top 5 elements are returned. In an ideal world, this would result in a SQL query for the TOP (5) matching rows.

As you can see, only five items were returned from the service. But where they filtered in the database or in memory after the whole data set was returned?

Web API Query

Let’s run a SQL trace and find out what’s really going on. I did so by clicking on “New SQL trace” and then accepting the defaults on the dialog:

Trace Dialog

Then I ran the query. The result was unequivocal: there is no ‘TOP’, so the entire data set is being returned and then filtered in memory:

Trace

If oData queries to the Web API behave that way, what of other frameworks that expose IQueryable? I quickly put together a RIA service against the Cocktails database and created a client-side query requesting the first 5 matching elements:

Ria Query

This time, the SQL profiler shows that the query has incorporated the paging restriction:

SQL output

SQL profilers are indispensable for showing you what’s really going on in the database–good and bad.

Kevin Rattan

For related information, check out these courses from Learning Tree:

Building Web Applications with ASP.NET MVC

Building Web Applications with ASP.NET and Ajax

Programming WCF Web Services for .NET

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.