Solved

View SQL execution plan in vb.net

Posted on 2014-02-19
5
434 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

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 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