Solved

Generate Executed Execution Plan from SQL Server with OSQL

Posted on 2004-04-25
9
425 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:danblake
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Actually, hang on, those don't appear to work any more...let me look...
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Looks like the above flags were holdovers from sybase--this is actually pretty interesting:

http://www.windowsitlibrary.com/Content/77/18/1.html
0
 
LVL 13

Author Comment

by:danblake
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 13

Author Comment

by:danblake
Comment Utility
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
 
LVL 13

Author Comment

by:danblake
Comment Utility
It is still a good article though..
0
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
Comment Utility
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
 
LVL 13

Author Comment

by:danblake
Comment Utility
@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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now