Unanswered: Retrieving Estimated Rows From SHOW_PLAN_ALL from within TSQL
SQL Server 2000
I'm trying recover the EstimatedRows column from the output of statements such as:
SET SHOWPLAN_ALL ON
SELECT title AS Qty
WHERE price > 10.00
SET SHOWPLAN_ALL OFF
entirely from within TSQL - ie without a client front end to parse the query plan resultset returned. Esentially I am trying to compare actual result counts returned to the number of rows estimated by the Query Optimiser (this is as part of a automated performance analysis module within a knowledge system).
So far I haven't found a way to recover the output from the above statement block into a structure such as temporary table which I can then interogate from within TSQL to recover the values I require.
Approaches such as
CREATE PROCEDURE dbo.usp_dev_showplan
@sql_str VARCHAR (7000)
SET @sql_str =
'SET SHOWPLAN_ALL ON
SET SHOWPLAN_ALL OFF'
EXEC dbo.usp_dev_showplan 'SELECT titles FROM pubs..titles WHERE price > 10'
have failed ....
We've done it easily enough through a client and ADO - but want to wrap it all up in TSQL to run within the Agent to automatically log trend data over time.