Displaying SQL Server Query Execution Plans and TIME and IO Statistics
DBAs mostly operate, manager or administer a SQL Server instance using SQL Server Management Studio (SSMS). Any operation performed against SQL Server instance through SSMS GUI internally will be translated into set of T-SQL command which can be individual queries or Stored Procedures, functions, etc. Also, any one can run the T-SQL queries directly from the SSMS which gets executed and the operation gets performed and any results are returned.
From a user point of view, we understand T-SQL commands, but SQL Server database engine will further translate these queries into query execution plans so that it can perform the requested operations in the best way possible. So, if there are complains or request that the SQL queries are performing slow, then we can try run those queries from SSMS and use various options available to understand what the query might be doing internally with the help of SQL Server query execution plans, execution IO and TIME statistics.
Below are some of the options provided by SQL Server to see various details about the query execution. These below Transact-SQL SET statements are the options for displaying execution plan information produce output in XML and text.
SET SHOWPLAN_XML ON – This causes SQL Server not to execute Transact-SQL statements, instead SQL Server returns the execution plan information about how the statements are going to get executed in the form of an XML document.
SET SHOWPLAN_TEXT ON – This causes SQL Server to return the execution plan information for each query in text. The Transact-SQL statements or batches are not executed.
SET SHOWPLAN_ALL ON – This is similar to SET SHOWPLAN_TEXT, except that the output is more verbose than that of SHOWPLAN_TEXT.
SET STATISTICS XML ON – This returns execution information for each statement after this statement executes in addition to the regular result set the statement returns. The output is in an XML format. SET STATISTICS XML ON produces an XML output for each statement that executes. The difference between SET SHOWPLAN_XML ON and SET STATISTICS XML ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS XML ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.
SET STATISTICS PROFILE ON – This returns the execution information for each statement after the statement executes in addition to the regular result set the statement returns. Both SET statement options provide output in text. The difference between SET SHOWPLAN_ALL ON and SET STATISTICS PROFILE ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS PROFILE ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.
SET STATISTICS IO ON – This displays information about the amount of disk activity that is generated by Transact-SQL statements after the statements execute. This SET option produces text output.
SET STATISTICS TIME ON – Displays the number of milliseconds required to parse, compile, and execute each Transact-SQL statement after statements execute. This SET option produces text output.
Some of these options can also be turned on from the SSMS GUI.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.