A very powerful tool that SQL provides is the SQL Server Profiler. Anyone who has worked with SQL for a significant amount of time knows how this works but just to fill everyone else in, the SQL Server Profiler will monitor and track just about anything and everything that happens in your database. When you let this run over time you can export the results to the SQL Tuning Advisor which then gives you advice on how to tune up your database. The only downside to this approach is that it can be very taxing on your database.

An alternative approach

An alternative to the SQL profiler is that SQL actually stores query stats internally in the sys.dm_exec_sessions table. This is not a long term view but a shorter term view of queries that have run recently against your database. You can use the following query to find slower running queries:

SELECT TOP 5 S.cpu_time,
memory_usage,
total_elapsed_time
,S.session_id AS [SPID]
,S.login_name
,S.reads
,S.writes
,C.client_net_address
,T.dbid
,T.text
FROM sys.dm_exec_sessions S
JOIN sys.dm_exec_connections C
ON S.session_id = c.most_recent_session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) AS T
ORDER BY total_elapsed_time DESC

This query will give you valuable information such as the memory usage, cpu time and the time it took to run the query. This can be a very handy tool if you are trying to increase performance on your DB overall.

What about indexes?

One thing this query doesn’t address is your indexes. If you don’t use indexes, pick up a very large reference book sometime and try to find something very specific without using the books index. It will almost certainly take you longer. Same with SQL. Indexes are very important when it comes to reading data from a table, however they are not so nice when it comes to writing data to a table (the index also has to be written as well). So how do you find out if your indexes are being utilized? The answer is the following query:

USE master;
SELECT * FROM SYSdatabases
GO
 
SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = 9
GO

The first query will help you to identify which database you want to check for index performance. The second query you simply put in the ID of the database you want to check and it will give you a list of indexes and how they are being used in the database.