Capture execution plans
May include but is not limited to: graphical execution plans, SHOWPLAN
I’m a bit confused about this one being a separate objective. As all of us know, SQL Server produces graphical (and textual) execution plans for your queries. You can watch these plans any time you’d like by pressing Ctrl+M.
A few things to know about execution plans:
- They are read from left to right, and from top to bottom.
- The thicker an arrow, the more data it returns.
I think that’s good enough. Another way to access graphical execution plans is the SHOWPLAN clause. When specifying it, SQL Server returns the execution plan in an XML format, without executing the commands. The syntax to use:
SET SHOWPLAN_XML ON
If you prefer plain text, use the SHOWPLAN_TEXT clause instead. But make sure you turn off these features, because you only get execution plans as a result.
You can get additional information about your queries when using the STATISTICS clause. The difference is that when you specify STATISTICS, you get a result after your query is executed. There are four things to query for: XML, PROFILE, IO, TIME. The syntax is:
SET STATISTICS TIME ON
Then you can view the results in the Messages pane in SSMS.