How to quickly analyze a slow running query using SHOWPLAN_TEXT

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.

Eg:

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

Read more about it over here