Solved

View SQL execution plan in vb.net

Posted on 2014-02-19
5
424 Views
Last Modified: 2014-02-20
When I run a query in SQL I can press a button see the execution plan.  How can I run an SQL query from a GUI and see the execution plan?

My problem is that the SQL query runs fine from management studio, but not when run from the GUI.  It could be a case of parameter sniffing as per these links, but I thought the execution plan could confirm this.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9fd72536-f714-422a-b4c9-078e2ef365da/stored-procedure-called-from-the-application-is-slow-java?forum=transactsql

http://pratchev.blogspot.co.uk/2007/08/parameter-sniffing.html

My GUI is a vb windows form GUI and links to a SQL 2005 database.  I'd like to be able to do this with a web based GUI too.
0
Comment
Question by:AlHal2
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 39872568
Use the Profiler, with this you can choose to save the executionplan in the trace. Filter on your application if its a Heavy used server.

Regards Marten
0
 

Author Comment

by:AlHal2
ID: 39872761
How do I do this in vb 2008?
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 39872984
You don't
you start profiler (hook up against the SQL Server your GUI is targeting), use the normal template (Standard), modify under:
Events Selection, check Show all events
  Navigate to Performance
    Check Showplan XML
Run the trace

Now run your GUI thing
Check the trace
After analyzing the queryplan, Fix/index/query/Stored Proc/statistics or just some error

Repeat by restarting the trace, run query in GUI again until problem is fixed.
Done!

Regards Marten
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 250 total points
ID: 39872987
to start the profiler, start SSMS, use:
  Tools/SQL Server Profiler

And off you go

Regards Marten
0
 

Author Closing Comment

by:AlHal2
ID: 39873057
Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

22 Experts available now in Live!

Get 1:1 Help Now