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 the logging feature, but it would be great if you could also somehow request the time. Any ideas?

Answer 1

SQL Profiler to get the query and time, as well as the execution path in the query analyzer to find out where the bottlenecks are.

Answer 2

As two people have said, SQL Profiler is a ready-to-use tool. I don’t want to be an echo, but I would like to dwell on the details: it not only provides the actual timings with SQL Server (as opposed to the time from the application side where the network input, connection and connection pool is added to the cake), but it also gives you [often more important] data on I / O statistics, information about blocking (if necessary), etc.

An important reason that I / O statistics are important is because a very expensive request can be executed quickly, consuming excessive server resources. If, for example, a query that is executed often ends up in large tables and there are no corresponding indexes that scan tables, the affected tables will be cached in SQL Server memory (if possible). Sometimes this can lead to the fact that the same request will be executed incredibly fast, at the same time it will damage the rest of the system / app / db, eating server resources.

Locking information is almost as important - → tiny queries that perform PK searches for a single record may have poor timings due to locking and locking. I read somewhere that this site was crammed with dead ends in its early beta versions. SQL Profiler is your friend for identifying and resolving lock issues.

Summarize; Do you use L2S, EF, regular ADO - if you want to make sure that your application "behaves" to the database, it always has SQL Profiler ready during development and testing. It pays off!

Edit:. Since I wrote the answer above, I developed a new runtime profiling tool for L2S that brings together the best of both worlds; I / O statistics and server timings from SQL Server, SQL Server execution plan, SQL Server Missing Indexes warnings in combination with a managed call stack to simplify the search for code that generates a specific query, and some additional filter options to register only queries that meet certain criteria. In addition, the logging component can be distributed with applications to simplify the execution of query profiles while working in a live client environment. The tool can be downloaded using:

http://www.huagati.com/L2SProfiler/ , where you can also get a free trial license for 45 days.

A more detailed description of the background and an introduction to the tool are also available here:
http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html

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

Answer 3

You can use System.Diagnostics.Stopwatch , this will allow you to track the execution time of the request. Just remember that Linq-> SQL queries are not executed until you list them. Also note that if you log into Console.Out , there will be a significant increase in performance.

Answer 4

What you can do is add a custom TextWriter implementation to DataContext.Log, which will write the generated sql to a file or memory. Then skip these queries by running them with raw ADO.NET code, surrounding each stopwatch.

I used a similar technique before, because it seemed that whenever I was developing some code, I never had a Profiler profile open, and it was very easy to output these results to an HTML page. Sure that you execute them twice at the request of the website, but it is useful to see the runtime as soon as possible, instead of waiting until you catch something in the Profiler.

Also, if you go to the SQL Tool route, I would recommend googling "the slowest DMV query" and get a stored procedure that can give you statistics on the slowest queries in your db. It is not always easy to scroll through the profiler results to find bad queries. In addition, with the correct sql 2005 dmv queries, you can also order, say, cpu vs. time, etc.

Answer 5

We use SQL Profiler to test our queries with LLBLGen Pro.

Answer 6

It is best to log file requests, and they use SQL Profiler to time them and set up indexes for requests.