asked
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?
Surely testing whether a query returns the right data should happen *before* you deploy the application?
without wishing to add more abstraction to this, surely the number of rows returned is almost meaningless without a record of the number of columns and their data types that make up the dataset. Will you also be endeavouring to log this information too?
+1 for asking the question that came to my mind - 'What are you actually trying to achieve'. It seems to me that the number of rows processed would be an arbitrary statistic, at best...
I would like to see if a specific row returns large amount of rows, then we can analyze the applicative queries - perhaps the WHERE clause is too broad.. We use this information frequently in our Oracle analysis (Our application supports Oracle & SQL SERVER), and we would like to know if there's an equivalent information (statistics) in the system views/tables
I don't really understand the mocking attitude in your note. We tune the application before and after the deployment at the customer. After several months we would like to monitor the application from the DB angle at the customer's side and would like to receive as many data as possible. The customer add many customization to the product that is not out-of-the-box, so we would like to view their outcome. I'm looking for a specific information, that ORACLE database already provides inside its system views. I want to see the same information in SQL SERVER. if there isn't such a data, so be it. However, In case there is, I would be happy to hear it. I edited my question, as I found an "estimation number of rows" inside the query plan XML column in sys.dm_exec_query_plan. I'm note sure its the right approach. I would like to see the value from within a system view/table column
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][1] [1]:
http://connect.microsoft.com
