USE Pubs
GO

-- 1 - SHOWPLAN_TEXT 
SET SHOWPLAN_TEXT ON
GO
SELECT TOP 1 a.au_lname AS 'AuthorLastName', a.au_fname AS 'AuthorFirstName', 
t.title AS 'Title', t.pubdate AS 'PublicationDate'
FROM dbo.Authors a
INNER JOIN dbo.TitleAuthor ta
ON a.au_id = ta.au_id
INNER JOIN dbo.Titles t
ON ta.title_id = t.title_id
WHERE a.state = 'CA'
GO
SET SHOWPLAN_TEXT OFF
GO

-- 2 - SHOWPLAN_ALL 
SET SHOWPLAN_ALL ON
GO
SELECT TOP 1 a.au_lname AS 'AuthorLastName', a.au_fname AS 'AuthorFirstName', 
t.title AS 'Title', t.pubdate AS 'PublicationDate'
FROM dbo.Authors a
INNER JOIN dbo.TitleAuthor ta
ON a.au_id = ta.au_id
INNER JOIN dbo.Titles t
ON ta.title_id = t.title_id
WHERE a.state = 'CA'
GO
SET SHOWPLAN_ALL OFF
GO

-- 3 - Graphical Query Plan

-- 4 - SQL Server DMV's
USE master;
GO
SELECT * FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO  

-- 5 - Pick a prepared plan
SELECT * 
FROM sys.dm_exec_cached_plans 
GO
-- 6 - Get the plan
SELECT * 
FROM sys.dm_exec_query_plan (0x05000E0067C18A77B8A0E711000000000000000000000000)
GO

-- 7 - Double click on the XML

-- 8 - Show the io, memory, cpu usage, etc.

-- 9 - Show how to save the query plan, edit, etc.