How to execution plan on SQLServer

In SQLServer Managment Studio to the execution plan one can use one of the following SET commands

“SET SHOWPLAN_XML ON” – SQLServer will not execute the SQL and it will return execution plan and has estimated row counts and stats in form of a XML document. The output displayed in SQL Server management studio can be stored with .sqlplan extension using “Save Results As..” which will allow show you view execution plan of the SQL in SQL Server Managment Studio. To turn off this option by “”SET SHOWPLAN_XML OFF”
“SET SHOWPLAN_TEXT ON” – SQLServer will not execute the query, not include estimated row counts and statistics.
“SET SHOWPLAN_ALL ON” – SQLServer will not execute the query, it will include estimated row counts and statistics
“SET STATISTICS PROFILE ON” – SQLServer will execute the query and include estimated rows counts and statistics and actual row counts and statistics.
“SET STATISTICS XML ON” – SQLSever will execute the query and include estimated rows counts and statistics and actual row counts and statistics.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s