Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

View SQL execution plan in vb.net

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
AlHal2
Asked:
AlHal2
  • 3
  • 2
1 Solution
 
Marten RuneCommented:
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
 
AlHal2Author Commented:
How do I do this in vb 2008?
0
 
Marten RuneCommented:
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
 
Marten RuneCommented:
to start the profiler, start SSMS, use:
  Tools/SQL Server Profiler

And off you go

Regards Marten
0
 
AlHal2Author Commented:
Thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now