SET SHOWPLAN_TEXT ON
To quickly analyze a slow-running query, examine the query execution plan to determine what is causing the problem.
SET SHOWPLAN_TEXT causes SQL Server to return detailed information about how the statements are executed.
Example
USE Northwind;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI';
GO
SET SHOWPLAN_TEXT OFF;
GO
Displays how indexes are used:
--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), SEEK:([Northwind].[dbo].[Customers].[CustomerID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)
Some Important Points :
1. SET SHOWPLAN_TEXT cannot be specified when using a stored procedure
2. You need to have the SHOWPLAN permission while running SET SHOWPLAN_TEXT
