Troubleshooting database performance issues

I've a problem with database, and here is a scenario I'm dealing with every day: usually application can deal with traffic etc. but several times a day I'm dealing with performance issue. When it's happening stored procedures increase their execution time from 200%-1000% .

I have one, described here: https://stackoverflow.com/questions/5585726/query-with-large-table-joins-optimization-techniques . Usually its execution takes about 6-8seconds (I've already optimized it from 20seconds cos at the beginning I thought the procedure is the culprit), but when something is happening, that procedure can execute even 60seconds.

And today I realized that there are more places in application that freeze, and what is more, I accidentaly realized that sample queries issued from SQL Management Studio, that usually execute in the blink of an eye, start to slow down when something happens with database server. Good news is, that thanks to this finding I eliminated both application & web开发者_StackOverflow社区 servers as bottlenecks.

It's database server that's not able to handle requests. I wonder is there a way to troubleshoot live database and spot slow queries or other action like rebiulding indexes, statistics, memory problems etc. or perhaps other issues that could result in described performance decline?

Thanks, Pawel

This is a query that I use pretty much constantly.

It shows you currently executing code, Wait information, blocking information, what spids are running, how long they have been running, what the current command is, what the query text is, and the xml query plan if there is one in the cache:

SELECT 
d2.wait_type,
d1.session_id,
d2.blocking_session_id, 
d2.status,
d1.login_name,
d2.start_time, 
d2.command,
d3.[text] as Batch, 
SUBSTRING(d3.[text], (d2.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(d3.text)
        ELSE d2.statement_end_offset END 
            - d2.statement_start_offset)/2) + 1) AS Current_Statement,
qp.query_plan,
d1.login_time, 
d2.wait_time,
d2.cpu_time, 
d1.memory_usage,
d2.total_elapsed_time, 
d2.reads,d2.writes,
d2.logical_reads
FROM sys.dm_exec_sessions d1
JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id
CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3 
CROSS APPLY sys.dm_exec_query_plan(d2.plan_handle) AS qp
WHERE d1.session_id <> @@SPID

In SSMS if you right click on the Server name (not DB name) it will show you an Activity Monitor menu, click that and it would be a good place to start.