October 17, 2002 at 3:52 am
SQL Server 2000
I'm trying to recover (amonst other things) the EstimatedRows column from the output of statements such as:
USE pubs
GO
SET SHOWPLAN_ALL ON
GO
SELECT title AS Qty
FROM pubs..titles
WHERE price > 10.00
GO
SET SHOWPLAN_ALL OFF
entirely within TSQL - ie without a client front end to parse the query plan resultset returned (we've done it easily enough through a client and ADO). Esentially I am trying to compare actual no of rows returned by the query to the number of rows estimated by the Query Optimiser (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 TSQL or a structure such as temporary table which I can then interogate from within TSQL to recover the values I require.
Approaches such as
USE pubs
GO
CREATE PROCEDURE dbo.usp_dev_showplan
@sql_str VARCHAR (7000)
AS
SET @sql_str =
'SET SHOWPLAN_ALL ON
GO '
+ @sql_str
+
' GO
SET SHOWPLAN_ALL OFF'
EXEC (@sql_str)
GO
EXEC dbo.usp_dev_showplan 'SELECT title FROM pubs..titles WHERE price > 10'
fail as SET SHOWPLAN_ALL is not allowed in stored procedures ....
We'd like to be able to do it all in TSQL so as to run a sample workload overnight (From the Agent) to automatically log trend data (estimated rows against actual, estimated cost against actual cost etc) over time.
Any ideas?
Many Thanks
Steve
October 17, 2002 at 4:40 am
Unfortunately due to the fact SET SHOWPLAN_ALL is required to execute in it's own batch it effectively prevents doing this. However if you are not wanting to write a seperate app you can do an ADO connection to the server using VBScript to do as you have before right in a Job and store on the server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 17, 2002 at 4:51 am
I think you're right - on a reread BOL states the SHOWPLANALL output is designed specifically for apps, seems a shame its output can't be recovered "internally" as it were.
Thanks for your suggestion - VBScript and ADO inside an Agent Job will be the neatest approach.
Thanks for your time.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply