10

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?

Improve this question

4 Answers 4

Reset to default
8

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.

Improve this answer
2
  • 1
    The MSSQL Database Engine Tuning Advisor is invaluable in chasing down those missing indexes and statistics to speed up the long-running queries (mind you, nothing will help if if it's long-running because of a locked row). If you've got a few hours to spare, capture a few minutes with the trace tool and then load the trace into the tuning advisor. It will go through every query and tell you if it can be sped up, and approximately how much by.
    – Mark Henderson
    Sep 21, 2010 at 22:42
  • The problem wth trace is that it is not a log. If the question was "how do I analyse sql server performance" I would certainly be responding with something about profiler.
    – Jim B
    Sep 22, 2010 at 15:29
3

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

Improve this answer
1
  • +1 - the trace facility is pretty good
    – Martin
    Sep 21, 2010 at 6:35
1

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

Improve this answer
0

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/

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .