Generate Executed Execution Plan from SQL Server with OSQL

Hi Experts,

Does anybody know how to make the (Executed)  Execution Plan output from OSQL, just like it does from within Query Analyser ? (Possibly via a SET Command ? the SHOWPLAN options/are not suitable as this does not actually execute the statement and generates inconsistencies between the run / estimated run plans..)


Regards,

Dan.
LVL 13
danblakeAsked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
Ok, I don't have a 7.0 server here to test with, but I think this works on both (I can test tomorrow).  This returns the actualy query plan used:

set statistics profile on

0
 
arbertCommented:
May not be as easy as you want it to be, but take a look at DBCC TRACEON using traceflag 310 or 317 in combination with trace flag 3604  You can tell it to log the plan that was actually used to a file.  This  is actually pretty cool because it gives TONS more information than a QA plan does.
0
 
arbertCommented:
Actually, hang on, those don't appear to work any more...let me look...
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
arbertCommented:
Looks like the above flags were holdovers from sybase--this is actually pretty interesting:

http://www.windowsitlibrary.com/Content/77/18/1.html
0
 
danblakeAuthor Commented:
Just to clarify -- I'm using SQL Server 2000 & 7.  (Might need one version for each system, I understand that they do not always run the same on both systems.)
0
 
danblakeAuthor Commented:
Its not getting any results back under sql-server 2000 (using QA) with these options at all ...
(This is more of a 6.5 option ?)
0
 
danblakeAuthor Commented:
It is still a good article though..
0
 
danblakeAuthor Commented:
@arbert -- now you know that old question where a user was requesing what indexes are used on sps/views.. (cached execution plan...) instead of using sql-server profiler wouldnt' it be better to use this 'set statistics profile on' as it lists the indexes used (as long as you can generate a test work-load for every sp)... points are yours' arbert.  
This works and does the trick for me on my sql 2000 box...
0
 
arbertCommented:
Ya, I understand.  The whole "estimated" and "actual" plan is kind of a pain.  Especially if your talking para plans when it depends on the actual workload on the box too--sometimes it might run para, sometimes not.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.