CODEWITHSUNDEEP
c#sql-servervisual-studio-2010profiling
Matthew Groves
Matthew Groves

Reputation: 26169

Visual Studio 2010 profiler with SQL

I'm using Visual Studio 2010's built-in profiler to look at a section of poorly performing code. However, I'm seeing some results that don't quite make sense. Here is a shot of the report:

report screenshot

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

So, my question is: is this profiler tool useless for anything involving database access, since the SQL work is being done by another process (i.e. the SQL server), and therefore I have to measure it some other way?

Upvotes: 4

Views: 5736

Answers (4)

Colin Thomsen
Colin Thomsen

Reputation: 1806

You might also find the 'Tier Interaction Profiling' option useful for investigating SQL issues 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).

Once you have collected data using this mode, switch to the 'Tier Interactions' view and you will see detailed information about SQL calls, including call counts and elapsed times (picture from profiler blog)Tier Interaction View

Upvotes: 1

Mike Dunlavey
Mike Dunlavey

Reputation: 40689

The Visual Studio profiler has two modes of operation, sampling and instrumentation. In sampling mode, it does not draw samples when it is blocked, as for I/O. Because of that, it cannot show you any part of the call tree except that in which the leaves are doing raw CPU processing.

You are using sampling mode. Try the instrumentation mode, which operates on wall-clock time, thus it includes I/O.

And whatever you do, please ignore exclusive time. Only pay attention to inclusive time, as a percent of total time. You are looking for routines in your code that are active a large fraction of time, most of it spent in calling other routines, and you are looking for the calls you can do without.

P.S. I do this, which always works.

Upvotes: 3

Rick Sladkey
Rick Sladkey

Reputation: 34250

The Visual Studio Profiler is a CPU Profiler. CPU Profilers are useful for analyzing CPU-bound programs or CPU-bound portions of IO-bound programs. Here are introductions to those terms:

Since your process is I/O-bound, a CPU profiler will not help you make your program run faster. As you suspected, you will have to profile the SQL query using other tools such as the:

or one of many other tools.

Upvotes: 1

Hasanain
Hasanain

Reputation: 935

In my opinion, your assumption about Visual Studio's profiler is correct, regarding the SQL work.

In order to check your SQL work, a better option would be to run SQL Profiler and track your queries. You can setup the SQL profiler, to show the execution plans of all incoming queries (...for the relevant database(s) ) from your application. That will indicate whether it's your SQL work or as the snapshot suggests Regex.Replace.

Hasanain

Upvotes: 2

Related Questions