Good way to time SQL queries when using Linq to SQL

Question

Is there a good way to time SQL queries when using Linq to SQL? I really like logging feature, but it would be great if you could somehow also time that query. Any ideas?

Answer 1

As two people already said, SQL Profiler is the out-of-the-box tool to use for that. I don't want to be an echo but I wanted to elaborate a bit more in detail: Not only does it provide the actual timings from SQL Server (as opposed to timing from the app-side where network i/o, connection and connection pool timings are added to the cake) but it also gives you the [often more important] I/O statistics figures, locking info (as needed) etc.

The reason I/O statistics are important is that a very expensive query may run fast while consuming excessive amounts of server resources. If for example a query that is executed often hits large tables and there are no matching indexes resulting table scans, the affected tables will be cached in memory by SQL Server (if it can). This can sometimes cause the same query to execute blazingly fast while in effect it is harming the rest of the system/app/db by eating up server resources.

Locking info is almost as important -> tiny queries doing PK lookups for a single record can have bad timings due to locking and blocking. I read somewhere that this very site was plagued by deadlocks in its' early beta days. SQL Profiler is your friend for identifying and resolving problems caused by locking too.

To summarize it; whether you use L2S, EF, plain ADO - if you want to make sure your app "behaves nice" towards the database always have SQL Profiler ready during development and testing. It pays off!

Edit: Since I wrote the answer above I have developed a new runtime profiling tool for L2S that bring the best of both worlds together; I/O stats and server-side timings from SQL Server, SQL Server execution plan, SQL Server's "missing index" alerts, combined with the managed call stack to make it easy to find what code generated a certain query, and some advanced filter options to log only queries that fulfill certain criteria. Additionally, the logging component can be distributed with apps to make runtime query profiling in live customer environments easier. The tool can be downloaded from:

http://www.huagati.com/L2SProfiler/ where you can also get a free 45-day trial license.

A longer background description and intro to the tool is also posted here:
http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html

...and a sample/walkthrough of using some of the more advanced filter options is available here:
http://huagati.blogspot.com/2009/08/walkthrough-of-newest-filters-and.html