How to find slow Sql Server queries

We can run the queries

EXEC dbo.sp_who 

or 

EXEC dbo.sp_who2

These are the typical commands where who2 is a newer version that gives us some more info.

who2 tells us the SPID of any blocking process and we can then run,

 DBCC INPUTBUFFER(put SPID here)

to tell us what it was doing.

But better than that...

A cool free command written by Adam Mechanic called sp_WhoIsActive

http://sqlblog.com/files/folders/beta/entry42453.aspx

It has some parameters you can switch on but be careful about running this with all execution plans being returned on a busy server.

SQL Server 2005 Waits and Queues

Really, really useful. Everywhere I go the same problems exist. SQL Server slow and no one knows why. It's a useful topic and this is a good part of it.

SQL Server 2005 Waits and Queues - SQL Server Best Practices Article, by Tom Davidson, 2005

It can be downloaded from http://bit.ly/aUh6S


"wait time" - the total time taken for a thread to go from Running, to being "put on the queue", or to being "queued, and then back to Running again.

"resource wait time" - the time a thread is waiting on the Waiter List with the state of Suspended.

"signal wait time" - time waiting for the processor once the resource has become available.

So wait time = resource wait time + signal wait time

When a thread has server time, and then becomes blocked, it is set as Suspended and then placed on the Waiter list.

When it receives a signal to say the resource is available, it is set to Runnable and it is place on the Runnable Queue. It will wait here until it is given to the Processor.

sys.dm_os_waiting_tasksDMV
Will show all threads that are currently suspended.