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
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