Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Generate Executed Execution Plan from SQL Server with OSQL

Posted on 2004-04-25
9
428 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
ID: 10912551
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
ID: 10912559
Actually, hang on, those don't appear to work any more...let me look...
0
 
LVL 34

Expert Comment

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

http://www.windowsitlibrary.com/Content/77/18/1.html
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 13

Author Comment

by:danblake
ID: 10912594
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
 
LVL 13

Author Comment

by:danblake
ID: 10912629
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
ID: 10912649
It is still a good article though..
0
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 10912692
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
ID: 10916098
@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
ID: 10918050
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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