Is there a way to find most expensive queries within a particular time?

I did ask this before. But here is what I would like to do - I would like to run my .net application and monitor the sql queries being done and monitor only those as compared to sql profiler which开发者_高级运维 logs everything.

So, does anyone know of a tool or a way I can monitor queries for an application and then find out the most expensive queries of that session ?

In your applications connection string to SQL Server, add (OTTOMH) APPLICATION_NAME = 'ABC App'. So in effect it should be like

'Server=YOURSERVERNAME;Integrated Security=SSPI;Application_Name=ABC App'

Once your app is running, you can run sp_who2 in SQL Server Management Studio to view all the connections - check there to make sure that your application is showing up with the name rather than the generic name for .Net apps.

*Double check if this is App Name or Application_Name*

When you are setting up the trace flags in Profiler, go to the Events tab, and click on Column Filters. The first one is for the ApplicationName. Set that to '%ABC App%'

Use a server side SQL Trace (not the Profiler GUI as this adds more overhead), Filter on the spid(s) used by your application, use RML utilities to process and aggregate the trace data.

RML Utilities will take care of templatizing similar queries with differing text column values (e.g. calls to the same stored procedures with different parameter values) so they are aggregated together.

  SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
  ((CASE qs.statement_end_offset
  WHEN -1 THEN DATALENGTH(qt.TEXT)
  ELSE qs.statement_end_offset
  END - qs.statement_start_offset)/2)+1),
  qs.execution_count,
  qs.total_logical_reads, qs.last_logical_reads,
  qs.total_logical_writes, qs.last_logical_writes,
  qs.total_worker_time,
  qs.last_worker_time,
  qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
  qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
  qs.last_execution_time,
  qp.query_plan
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
  ORDER BY qs.total_logical_reads DESC