Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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 ?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks. The magazine article is good too. –  Martin Sep 21 '10 at 6:33
    
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 '10 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 '10 at 15:29

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

share|improve this answer
    
+1 - the trace facility is pretty good –  Martin Sep 21 '10 at 6:35

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.