does SQL server have the equivalent of MySQL query logs

Question

I have used MySQL for a long time and have found the general query log and slow query log to be very useful in finding out what is happening on the server, and in identifying bottlenecks.

Now I need to use SQL Server. Does SQL server have similar log facilities,and if so, what are they called ?

Answer 1

SQl server keeps statistical information about all queries in various tables. You can use the following code to determine what the longest running query is (from the sys.dm_exec_query_stats table) you should run the following DBCC command the query. This DBCC commands clears the server cache restarts logging of the query running time.

DBCC FREEPROCCACHE

Run following query to find longest running query

SELECT DISTINCT TOP 10
 t.TEXT QueryName,
 s.execution_count AS ExecutionCount,
 s.max_elapsed_time AS MaxElapsedTime,
 ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
 s.creation_time AS LogCreatedOn,
 ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
 FROM sys.dm_exec_query_stats s
 CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
 ORDER BY
 s.max_elapsed_time DESC
 GO 

You should also take a look at this technet magazine article Optimizing SQL Server Query Performance Which has a query for determining which query is the most expensive read I/O query, as well as how to look at execution plans an other optimizations.

Answer 2

You can use SQL Profiler to monitor the system in real time to identify slow running statements.

Answer 3

SQL can also suggest indexes you might like to add or elimitate: http://msdn.microsoft.com/en-us/library/ms187974.aspx

You can also see reports in SQL Management studio related to long queries: Right-click your servername > reports > standard reports > Performance - Top Queries by...