There is a plethora of tools available, but the ones I use mostly are:
SQL Server Profiler, see tutorial here:
SQL Server Profiler Step by Step[
^]
But I would not recommend it, it's too complex and version specific, the open-source
ExpressProfiler is much easier to use, see:
ExpressProfiler - Home[
^]
You can also test queries in SQL Server Management Studio like this:
SET STATISTICS IO ON
SET STATISTICS TIME ON
CHECKPOINT
DBCC DROPCLEANBUFFERS
USE [XXX]
SELECT * FROM MyTable
As already mentioned by Member 11712753, you can use
execution plan
:
In Management Studio query editor, you can choose to either display an estimated execution plan or the actual execution plan by clicking the Display Estimated Execution Plan or the Include Actual Execution Plan button on the query editor toolbar.
Displaying Graphical Execution Plans (SQL Server Management Studio)[
^]