Better understanding of MySQL transactions

I just realized that my application was needlessly making 50+ database calls per user request due to some hidden coding -- hidden in the sense that between LINQ, persistence frameworks and events it just so turned out that a huge number of calls were being made without me being aware.

Is there a recommended way to analyze individual transactions going to my SQL 2008 database, preferably with some integration to my Visual Studio 2010 environment? I want to be able to 'spy' on individual transactions being made, but only for certain pieces of my code, and without 开发者_StackOverflowmaking serious changes to either the code or database.

I addition to SQL Server Profiler, there are a number of performance counters you can look at to see both a real time evaluation and a historic trend:

• Batch Requests/sec: Effectively measures the number of actual calls made to the SQL Server
• Transactions/sec: Number of transactions in each database.
• Connection resets/sec: number of new connections started from the connection pool by your site.

There are many more performance counters you can monitor, specially if you want to measure performance, but going through is besides the scope here. A good starting point is Monitoring Resource Usage.

You can use the SQL Profiler tool that comes with SQL Server Management Studio.

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.

As mentioned, SQL Profiler is userful at the SQL Server level. It is not available in SQL Server SSMS Express however.

At the .NET level, LINQ to SQL and the Entity Framework both support logging. See Logging every data change with Entity Framework, http://msdn.microsoft.com/en-us/magazine/gg490349.aspx, http://peterkellner.net/2008/12/04/linq-debug-output-vs2008/.