Visual Studio 2010 Profiler with SQL

Question

I use Visual Studio 2010's built-in profiler to view a section of badly working code. However, I see some results that are not entirely clear. Here is a snapshot of the report:

This seems to indicate that Regex.Replace is a bottleneck (and therefore I should try to reduce or eliminate this usage as much as possible). However, this seems inaccurate because I know that this particular section of code uses the database heavily, and therefore I expect SqlCommand.ExecuteNonQuery to be at least slightly higher in this report, if not more dominant than Regex to use.

So my question is: is this profiling tool useless for anything related to accessing the database, since the SQL is being done by another process (like a SQL server), and therefore I have to measure it in some other way?

Answer 1

The Visual Studio profiler has two modes of operation, selection and tooling. In sampling mode, it does not output samples when it is locked, as for input / output. Because of this, it cannot show you any part of the call tree except the one in which the leaves perform unprocessed processor processing.

You are using sampling mode. Try using a tool that works in wall-mounted mode, so it includes I / O.

And whatever you do, please ignore the exceptional time. Pay attention only to the inclusive time as a percentage of the total time. You are looking for routines in your code that are active most of the time, most of which are spent calling other routines, and you are looking for calls that you can get around.

PS I am this one that always works.

Answer 2

In my opinion, your assumption about the Visual Studio profiler is true regarding SQL.

To test SQL, the best option would be to run SQL Profiler and track your queries. You can configure the SQL profiler to display execution plans for all incoming queries (... for the corresponding databases (databases)) from your application. This will indicate if your SQL job is working or how the snapshot suggests Regex.Replace.

Hasanain

Answer 3

Visual Studio Profiler is a CPU profiler. CPU profilers are useful for analyzing processor-bound programs or processor-related parts of IO-related programs. The following terms are presented here:

• processor binding
• I / O binding

Since your process is related to I / O, a CPU profiler will not help you speed up your program. As you suspected, you would have to profile the SQL query with other tools, such as:

• SQL Server Profiler

or one of many other tools.

Answer 4

You can also find the option "Profiling between levels" that is useful for studying SQL problems with the Visual Studio 2010 profiler. You can enable this option on page 3 of the Performance Wizard or as part of the session properties (see our blog for screenshots).

After you have collected data in this mode, go to the “Level interactions” view and you will see detailed information about SQL calls, including the number of calls and elapsed time (image from the profiler blog)