Nothing is more annoying than a slow and unresponsive application. Especially a slow and unresponsive web-application. Which are the hardest to tune, by the way. As opposed to desktop tools, where the only thing to optimize is your source code - web-applications have three things to tune:

• server performance (database, web-server and, finally, the application itself)
• network performance (latency, bandwidth, amounts of data being sent)
• client performance (HTML & Javascript speed)

As you might have already guessed, we spent the last couple of days tuning the response times for our hosted help-desk. Including the database. And here's a great SQL-script that I wanted to share with you, that lists the top 25 resource-intensive queries executed during the last 5 minutes. You can tune this script a bit, the code is self-explanatory:

SELECT TOP 25
creation_time, last_execution_time, total_worker_time,
total_worker_time / execution_count AS 'Avg worker Time',
last_worker_time,
execution_count, 
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
((CASE statement_end_offset 
  WHEN -1 THEN DATALENGTH(st.text)
  ELSE qs.statement_end_offset END 
  - qs.statement_start_offset)/2) + 1) as 'statement_text'
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
where last_execution_time>dateadd(minute, -5, getdate())
ORDER BY total_worker_time / execution_count DESC