October 13, 2004 at 10:13 am
Hello,
I was curious if there was a way to dynamically generate IO statistics and Execution Plans. I know I can do:
set statistics IO on
GO
select ...
GO
set statistics IO off
GO
and the same for execution plans using showplan_text, but is there a way I can dynamically generate these and export them to a file of some sort? The solution can be using a proc, DTS package, whatever.
Thanks,
Brian
October 13, 2004 at 11:03 am
I'm not entirely sure what you are after, but one possible solution would be to use SQL Profiler to capture the plan information (the know option is there, but I've never tried it). If you want to record a specific set of SQL you could then record for a specific SPID or user or whatever and use that SPID/user/whatever to execute the SQL you wish profiled.
October 13, 2004 at 11:20 am
Hello,
Thanks for the reply. What I want to do is:
I have about 160 procs. I've used vbscripts to create the SQL, for example, this would be one script:
set statistics io on
go
exec spProc params..
go
set statistics io off
go
set showplan_text on
go
exec spProc params..
go
set showplan_text off
go
I want to take these, run them, then get the results dynamically. I can't get access to them through ADO or ADO.NET because there is no return results. I was just curious if there was another way to "trap" the output.
Thanks,
Brian
October 13, 2004 at 12:56 pm
How about using the osql utility. If you put the SQL scripts in a folder somewhere you can use a DOS "for each" loop to run osql for each of the scripts directing the output to a text file based on the SQL file name in either the same or a different directory.
If what you want instead of the above is for the printed results to be returned back to your ADO or ADO.NET application you could write a Stored Producedure (SP) that launched osql to generate the output to a file and then import the file from the SP into a table which could then be selected out. I've not actually run osql from a SP but I'm sure it could be done with sp_CmdShell if nothing else. I suppose there could also be an undocumented or documented sp that writes output to a file or even table allowing you to bypass a step or two, but I'm not familiar with such a thing myself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply