Solved

View SQL execution plan in vb.net

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.NET 2008 VB and C# 6 39
VB.NET 2008 - Windows Protected Your PC 7 41
Input parameteres to DragOver 2 19
Install IIS7.5 on Windows Sever 2012 R2 4 23
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

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