AlHal2
asked on
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.
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.
ASKER
How do I do this in vb 2008?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
Regards Marten