Rows processed statistic information - sql server 2005
Hello, In Oracle, there's a view called V$SQLAREA that lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution. There is one column -ROWS_PROCESSED that sums the Total number of rows processed on behalf of this SQL statement. I'm looking for collateral information in SQLSERVER 2005. I looked in some of the DMV's (like sys.dm_exec_query_stats), but I haven't found anything related. @@ROWCOUNT won't be useful to me, as I want incremental statistics information that will sum the rows_processed of the top cpu/io/execution consumption queries in the database. I would appreciate any help in regards the subject. Thanks in advance, Roni. --------------------------------- I saw that when I query the following query, I receive the Query Plan in XML. Inside the XML plan code, there's a "EstimateRows" part with a number that correlates to the number of estimation rows of the query. I'm thinking of the option to substr the query_plan column to retreive only the above information (unless I would find it in some system views/tables). **Where can I find the Actual number of rows of the query ? Where is it stored ?** SELECT case when sql_handle IS NULL then ' ' else ( substring(st.text,(statement_start_offset+2)/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else statement_end_offset end - statement_start_offset) /2 ) ) end as query_text , query_plan, FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) cross apply sys.dm_exec_sql_text(sql_handle) st;
There isnt anything that is a direct parallel to the view you mention in SQL Server. The DMV's keep some information that might help you - the index dmv keeps information about scans, seeks etc. and there is the option to see what effect a query will have by using the different STATISTICS options, although that is when running a nquery from SSMS, not when a piece of script is executed as part of a sproc. What are you actually trying to achieve - is it for information so that you can tune a query or are you trying to view the cumulative queries on certain parts of your data? How do you intend to collect/store/use the information?
I think I understand what you want, but as others have said, that kind of info (row counts) isn't available. You want this info to enable you to do performance tuning - then I would suggest thinking about it in a different way - the number of rows returned, whilst obviously has a direct impact on the performance of a query, isn't necessarily a good indicator of performance. You can rewrite a query to return less rows, but use more resources - is that an improvement? So the kind of counters you get in the DMVs (like sys.dm_exec_query_stats) are more useful in determining performance *change*. If you *really* want row counts, I guess you would either have to roll-your-own DMV-type solution and capture the row counts after every SQL statement, or build a good case for it and suggest it up on [Connect] :