SQL Server execution plans are invaluable for figuring out how a query was run – and how to make it run better. Most of the time that I spend with execution plans is in either SQL Server Management Studio or, more frequently, SQL Sentry’s Plan Explorer. However, many developers spend most of their time in Visual Studio. Is there a way for you to view execution plans using Visual Studio? Yes!
I downloaded and installed Visual Studio Premium 2013 with SQL Server Data Tools. This is the first time I’ve used Visual Studio in…many years. I wasn’t sure what had changed, how much I’d remember about it, and how easy it would be to figure out what I wanted. Turns out, it’s pretty easy to do what I wanted: connect to a database, open a query, and see if I could view an execution plan.
To start, I selected File > New > Project.
On the left I expand Installed > Templates and select SQL Server. I name it and select OK.
The first thing I want to do is connect to my database. I expand out the Server Explorer and click the Add SQL Server option.
A connection box opens; I enter my SQL Server name and credentials. This verifies I have permission to view the server. I can now select View > SQL Server Object Explorer to view the server objects in that pane.
To start a new query, I go to Tools > SQL Server > New Query. I can open an existing query by going to File > Open > File and selecting a .sql file. The query window has a toolbar that has some of the same options as SSMS, although the icons look different.
Display Estimated Execution Plan
Include Actual Execution Plan
SQLCMD mode
The estimated and actual execution plans look and behave the same as they do in SSMS.
An actual execution plan
This ability can be helpful if you spend a lot of time working in Visual Studio and want to access execution plans.
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
