Is there any tool to see the queries run against the database?

Question

Is there any tool that will inspect either asp.net or sql server and report all the queries that are run against the database? The reason I ask is I am using Linq for a project and want to double check what its actually doing for each page.

Ideally I'd like to view a page in a browser and have a report of all the queries that were run to create that page.

I know I can view the SQL it runs for individual queries using debugging/breakpoints, and I know about LinqPad, but I'm afraid Linq is making several more queries on its own to get related data that I may not be directly aware of. Is there anything (tool/program/report/etc) like what I described? Thanks!

EDIT: Is there any FREE tool that can do this? I am using Sql Server 2008 Express and don't have SQL Profiler unfortunately.

Answer 1

SQL Profiler does that.

Answer 2

Absolutely, There is a SQL tool called SQL Profiler. It does require elevated database permissions in order to run profiler.

There is a decent tutorial on how to run Profiler on TechRepublic.

Another option out there is the NHibernate Profiler. I know that it is not as "free" as SQL Profiler, have never used it, but the screen shots for it look pretty cool.

Answer 3

Profiler is the best tool of them all for this but it can be expensive in inexperienced hands.

You can also try to do "exec sp_who" and then a "dbcc inputbuffer (111)" - just put the process id in the place of 111.

Answer 4

Since you are using SQL Server Express, how about this tool?

Profiler for Microsoft SQL Server 2005/2008 Express Edition

Microsoft SQL Server family includes free Express edition, that is fully functional, however has some disappointing limitations which prevent from using it in development process. One of them is absense of profiling tools, standard SQL profiler is not included. However, now you have an ability to use express edition for tuning your system. SQL Server Express Edition Profiler provides the most of functionality standard profiler does, such as choosing events to profile, setting filters, etc. By now there are no analogue free tools.

Download Here

Answer 5

For the LINQ to SQL queries specifically, you can also use the DataContext.Log property to output the queries to a TextWriter, so you can do things like write to debugger output window or (as in my use) to log4net.

These links might help:

• Log to debugger output window, file, or memory
• Log to log4net

Won't cover the stuff not generated by L2S, so this may not be the end all solution for you... but I've found it helpful.

Answer 6

A quick and dirty way to log LINQ to SQL queries in ASP.NET is this (assuming a Northwind.Dbml):

NorthwindDataContext context = new NorthwindDataContext();
context.Log = Response.Output;

This will write all queries to the response stream. Nasty, but handy for instant gratification without need for debuggers or profilers etc.

Answer 7

This one is only free for the first 45 days, but it gives you runtime profiling/logging with a bunch of filter options, SQL Server query execution plan logging etc. Built specifically for profiling L2S apps:

http://www.huagati.com/L2SProfiler