Home / server / Questions / 182665
Accepted
Martin
Asked: 2010-09-20 10:21:41 +0800 CST

Does MSSQL Server have the equivalent of MySQL's query logs?

  • 772

I have used MySQL for a long time and have found the general query log and the 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? If so, what are they called?

sql-server sql-server-2005 logging log-files sql-server-2008

4 Answers

  1. Best Answer

    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 commands:

    This DBCC command clears the server cache and restarts logging of the query running time:

    DBCC FREEPROCCACHE

    Run this query to find the 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 the 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 guidance on how to look at execution plans and other optimizations.

    • 4

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

    • 3

  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...

    • 1

  4. Old slow queries are saved in MSSQL. This was already solved here:

    http://blog.brianhartsock.com/2008/12/16/quick-and-dirty-sql-server-slow-query-log/

    • 0