Obtaining Actual & Estimated Execution Plans for SQL Queries

Ever wondered if you could determine how the SQL Statements will be executed?

Well, now you can in two ways:

1. Getting this information whilst not running any queries with SET SHOWPLAN_XML
2. Getting this information whilst running all queries with SET STATISTICS XML

Here is a small example of each:

-- Gets Estimated Execution Plan Without Executing the Query(ies)
SET SHOWPLAN_XML ON
GO
SELECT * FROM TableName WHERE Field = Value
GO
Set SHOWPLAN_XML OFF
GO
-- Get Actual Execution Plan
SET STATISTICS XML ON
GO
SELECT * FROM TableName WHERE Field = Value
SET STATISTICS XML OFF
GO

The details will be returned in the form of an XML file containing all the information such as memory usage, etc.