Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    794

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    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

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    794
    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 Attached Thumbnails ExecutionPlan_Properties.jpg  
    Last edited by corncrowe; 02-20-13 at 15:35.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    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

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    794
    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.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    794

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •