Technical: Microsoft – SQL Server – Query Stats – Average # of Records Returned

Technical: Microsoft – SQL Server – Query Stats – Average # of Records Returned

Introduction

So you ‘re looking at a new database and you know more than likely there are some non-performant queries.

Per this particular thread, I have a feeling that some of the queries might be returning more records than need be; especially for an OLTP.

 

Code

May be try Dynamic Management Views — specifically sys.dm_exec_query_stats.



SELECT 
		 SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1
				, (CASE 
					WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
					ELSE qs.statement_end_offset END - qs.statement_start_offset
				)/2
			) AS query_text

		, qs.execution_count
		, qs.total_rows / qs.execution_count as avgRows		  
		, qs.total_rows
		, qs.last_rows
		, qs.min_rows
		, qs.max_rows
		, qs.total_logical_reads
		, qs.total_logical_writes		

FROM sys.dm_exec_query_stats AS qs

		CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

order by
	  qs.total_rows / qs.execution_count desc

Keep in mind

Please keep in mind that sys.dm_exec_query_stats only started exposing “row count” information as of the following releases:

  • Microsoft SQL Server 2008 /R2 – Service Pack 1
  • Microsoft SQL Server v2012 – RTM

Before then, if you try to access any of the “row count” columns,  you will get:

Invalid column name 'total_rows'.
Invalid column name 'last_rows'.
Invalid column name 'min_rows'.
Invalid column name 'max_rows'.

References

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s