To find slow (cost intensive/expensive) SQL statements you can use the Activity Monitor in the SSMS (SQL Server Management Studio)
User rights
The SQL user have to have these user rights:
- VIEW SERVER STATE
To observe I/O activities the user have to have these user rights in additional:
- CREATE DATABASE
- ALTER ANY DATABASE
- VIEW ANY DEFINITION
Run Activity Monitor
Start the SSMS and connect to your SQL Server.
Than You can start the Activity Monitor through a symbol in the menu (see Pic. 1) or with right click on the server connection (see Pic. 2).
Find and analyze slow SQL statements
First you have to expand the section "Recent Expensive Queries".
In this view the SSMS show all SQL queries that the SQL server considers to be too "expensive", i.e. they either take too long or consume too much memory.
With right mouse click on an entry you can open the "Execution plan".
The Execution Plan will show all steps to get an result.
This window shows the entire sequence of the command and the distribution of costs. If the SQL server thinks that an index would help, it suggests one below the query. You can then select and set it up directly from the context menu.
Example with custo diagnostic SQL statement:
