If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to return the number of selected rows from all queries performed on DB?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 789
How to return the number of selected rows from all queries performed on DB?

Recently I had an application developer approach me and asked if I could provided him with a list of sprocs by returned row count. We had an issue where the application passed in a number of parameters which attempted to return 200k plus rows of data and the application was timing out. He changed the required parameters in the application and a reasonable number of rows were returned as expected. Short term solution to this one problem.

However there are always timeout issues with this particular application and we got to thinking that maybe other sprocs that were called using parameters would also fail at some point in time because too much data was being returned.

His request made sense if we could proactively identify potential issues with sprocs returning large number of rows.

I vaguely remember doing something like this before, but couldn't find my code snippet so I was wondering if anyone else had done this before and had some idea where to point me.

I tried stats and cached plans but the information wasn't in these dm views as far as I could tell.
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Sprocs typically have parameters, so wouldn't the rowcount vary from one execution to the next?
The request doesn't make sense to me, and sounds like developers pleading "Please! Save us from ourselves!"
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 789
Quote:
Originally Posted by blindman View Post
Sprocs typically have parameters, so wouldn't the rowcount vary from one execution to the next?
The request doesn't make sense to me, and sounds like developers pleading "Please! Save us from ourselves!"
Yes, the row count can vary. But it's an interesting idea to me because I'm responsible for database performance and if one sproc is attempting to return 5k rows of data then I'd like to take a look and find out why. What I'm looking for in the execution results for all sprocs that are executed on the database. More specific would be the number of rows return and other useful tidbits. I can get lots of stats from dmv's but poor performance can often be found in execution results by row count.

Update: I found the properties in execution plan has what I want, so now I just need to figure out how to get this information from worse performing query results.
Attached Thumbnails
How to return the number of selected rows from all queries performed on DB?-executionplan_properties.jpg  

Last edited by corncrowe; 02-20-13 at 14:35.
Reply With Quote
  #4 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Have you looked in the "Recent Expensive Queries" section of the Activity Monitor?
You can pull the sql for problem queries out of that, and it will show both sprocs and application-generated sql.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 789
Quote:
Originally Posted by blindman View Post
Have you looked in the "Recent Expensive Queries" section of the Activity Monitor?
You can pull the sql for problem queries out of that, and it will show both sprocs and application-generated sql.
Yep, but it is really labor intensive. I have scripts to give me this information along with the xmlqueryplan, but doesn't give me row counts. I will write a script later today or tomorrow to pull this information. For now, I'm busy moving databases from unlicensed servers to licensed ones. Busy week.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 789
Resolved

I wrote code to find the row_count for every stored procedure executed against selected database. Below are the three components I created to retrieve this information.

First I create the table structure in the master database.

Code:
CREATE TABLE ExecRequests  (
  id int IDENTITY(1,1) PRIMARY KEY
, connection_id uniqueidentifier not null
, session_id smallint not null
, request_id int null
, request_start_time datetime null
, login_time datetime not null
, status nvarchar(60) null
, command nvarchar(32) null
, sql_handle varbinary(64) null
, statement_start_offset int null
, statement_end_offset int null
, plan_handle varbinary (64) null
, database_id smallint null
, user_id int null
, blocking_session_id smallint null
, wait_type nvarchar (120) null
, wait_time_s int null
, wait_resource nvarchar(120) null
, cpu_time_s int null
, tot_time_s int null
, reads bigint null
, writes bigint null
, logical_reads bigint null
, [host_name] nvarchar(256) null
, [program_name] nvarchar(256) null
, blocking_these varchar(1000) null
, percent_complete decimal(9,6) null
, row_count int not null
)
Next is the code snippet I put into a SQL Agent job that runs continious and populates the table.

Code:
WHILE 1=1
BEGIN
	INSERT INTO master.dbo.ExecRequests (connection_id,session_id,request_id, request_start_time, login_time, status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,blocking_session_id,wait_type,wait_time_s,wait_resource,cpu_time_s,tot_time_s,reads,writes,logical_reads,[host_name], [program_name],[percent_complete],[row_count] )
	SELECT r.connection_id,s.session_id,request_id, r.start_time, s.login_time, r.status,command,r.sql_handle,statement_start_offset,statement_end_offset,r.plan_handle,5,user_id,blocking_session_id,wait_type,r.wait_time/60.,r.wait_resource ,r.cpu_time/60.,r.total_elapsed_time/60.,r.reads,r.writes,r.logical_reads,s.[host_name], s.[program_name],r.[percent_complete],r.[row_count]
	FROM sys.dm_exec_sessions s
	LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
	LEFT OUTER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = r.plan_handle
	CROSS APPLY sys.dm_exec_query_plan (r.plan_handle) qp
	CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) est
	WHERE r.session_id > 35		--retrieve only user spids
	AND r.session_id != @@SPID	--ignore myself
	AND r.row_count != 0
	WAITFOR DELAY '00:00:01'
END
Here is the code to retrieve the results.

Code:
WITH SPROC_NAME_CTE
AS
(
	 SELECT DISTINCT r.connection_id,r.session_id,qp.objectid,o.name
	 FROM master.dbo.ExecRequests r
	 LEFT OUTER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
	 CROSS APPLY sys.dm_exec_query_plan (r.plan_handle) qp
	 INNER JOIN EOP_ORDER.dbo.SYSOBJECTS o ON qp.objectid = o.id
), SPROC_ROW_CTE
AS
(	 
	 SELECT r.connection_id 
	 , r.session_id 
	 , r.row_count
	 , r.host_name 
	 , r.program_name
	 , r.status
	 , r.blocking_these
	 , blocked_by =  r.blocking_session_id
	 , r.wait_type , r.wait_resource
	 , DBName = db_name(r.database_id)
	 , r.command
	 , request_start_time
	 , r.tot_time_s, r.wait_time_s, r.cpu_time_s, r.reads, r.writes, r.logical_reads
	 , cacheobjtype = LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35)
	 , QueryPlan  = qp.query_plan
	 , qp.objectid
	 , ROW_NUMBER() OVER(PARTITION BY r.connection_id ORDER BY tot_time_s DESC) as RowNum
	 FROM master.dbo.ExecRequests r
	 LEFT OUTER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = r.plan_handle
	 CROSS APPLY sys.dm_exec_query_plan (r.plan_handle) qp
	 CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) est
	 WHERE host_name != 'DFW-IT-CREVILL7'
	 AND program_name not like '%agent%'
	 )
	 SELECT -- RowNum
	   sn.name
	 , sr.command
	 , sr.session_id 
	 , sr.row_count
	 , sr.host_name 
	 , sr.program_name
	 , sr.status
	 , sr.wait_type 
	 , sr.wait_resource
	 , sr.DBName 
	 , sr.request_start_time
	 , sr.tot_time_s, sr.wait_time_s, sr.cpu_time_s
	 FROM SPROC_NAME_CTE sn
	 INNER JOIN SPROC_ROW_CTE sr ON sn.connection_id = sr.connection_id
	 AND sn.objectid = sr.objectid
	 AND sn.session_id = sr.session_id
	 WHERE RowNum = 1
	 ORDER BY row_count desc, name asc
Feel free to try it and let me know if there are any suggested changes.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On