I've been using SQL Server profiler quite frequently to check for redundant or badly performing queries.
But is there an event (among the giant list) that allows you to log the total amount of time it takes to transfer the data from the data base to the application?
This would be a very good indicator for queries that return way more data than needed in the application.
Once data leaves SQL Server, it's at the mercy of your hardware (NICs and network latency).
You would typically profile that from the client side of things.
Use SQL Profiler to observe the number of reads performed by queries. This is a good indicator. Queries that use 'SELECT *' typically can't use a covering index, so look for Bookmark lookups.
It sounds to me like you are wanting to identify queries that return large result sets. Taking this a level deeper then, what you really want to do is to identify which queries are consuming the largest amount of data. This can be seen in terms of both logical reads and physical reads.
In order to view this information in a report you can use the freely available Performance Dashboard Reports or make use of the SQL Server DMV's.
For example, the following queries have been taken from the excellent SQL Server Performance Blog by Glenn Berry:
- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;