Sql query to return statements with a large result set

Question

Sql query to return statements with a large result set

simonjday

SSC Veteran

Points: 217
More actions
June 18, 2010 at 9:04 am

#222393

Hi All,
I have a newbie Sql server question
What i would like to do is to be able to run a query that will return the top number of sql queries than return a result set greater than a defined value
The reason I ask is that i am looking at a specific issue where i believe the data i am looking at is badly structured in a third party app and i wanted to know how many queries would return a result set above a certain value as i have reason to believe that large results are are causing performance issues for the third party app
Daft question i know but i just thought i would ask
Thanks in Advance
S

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

Well, the easiest way I know is to run the app, while running a SQL profiler, and filtering on the number of rows returned by each query. You can do that directly in the SQL profiler.

I hope it helps,

Cheers,

J-F

Stefan_G SSCertifiable Points: 6609 More actions · Answer 2

It is not so easy to return the queries that return a large result set, but it is very easy to return the queries that take the longest to execute.

You could try something like this:

SELECT top 20

max_elapsed_time/1000000.0 as max_duration,

total_elapsed_time/1000000.0 as total_duration,

execution_count,

char(13)+char(10)+

(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle))

+char(13)+char(10)+'------------'+char(13)+char(10)+char(13)+char(10) AS query_text

FROM sys.dm_exec_query_stats

where last_execution_time > convert(char(8), getdate(), 112) +' 07:00'

and max_elapsed_time > 5000000

ORDER BY 1 DESC

This query will return the 20 most expensive queries that where executed after 07:00 today.

simonjday SSC Veteran Points: 217 More actions · Answer 3

Thanks Guys

I did think that using the most expensive query option was a good way to go but i will also give the Profiler option a go also

Thanks for the quick suggestions

S