SET SHOWPLAN_ALL

SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

The two statements that follow use the SET SHOWPLAN_ALL settings to show the way SQL Server analyzes and optimizes the use of indexes in queries.

USE AdventureWorks2012; GO SET SHOWPLAN_ALL ON; GO -- First query. SELECT BusinessEntityID  FROM HumanResources.Employee WHERE NationalIDNumber = '509647174'; GO -- Second query. SELECT BusinessEntityID, EmergencyContactID  FROM HumanResources.Employee WHERE EmergencyContactID LIKE '1%'; GO SET SHOWPLAN_ALL OFF; GO

SET STATISTICS IO

SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

USE mydb

GO

SELECT * INTO SalesOrderDetail FROM AdventureWorks2008.Sales.SalesOrderDetail 

GO

SET STATISTICS IO ON

DBCC dropcleanbuffers

DBCC freeproccache

GO

SELECT * FROM SalesOrderDetail 

GO

SELECT * FROM SalesOrderDetail

Displays the number of milliseconds required to parse, compile, and execute each statement.

To use SET STATISTICS TIME, users must have the appropriate permissions to execute the Transact-SQL statement. The SHOWPLAN permission is not required.

USE AdventureWorks2012; GO        SET STATISTICS TIME ON; GO SELECT ProductID, StartDate, EndDate, StandardCost  FROM Production.ProductCostHistory WHERE StandardCost < 500.00; GO SET STATISTICS TIME OFF; GO