Solved

View SQL execution plan in vb.net

Posted on 2014-02-19
5
440 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
[X]
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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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