SQL Server 2005 longest running query per database

Question

Hi all,

I am using this

SELECT *
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

to get the longest running queries on the server level.

How do I get the longest running queries per database? I would like to focus only on one database at a time.

Thanks, Edi

Answer 1

SELECT * FROM database1.dbo.tale JOIN database2.dbo.table

In which database did the query above run? What if the current context at the moment of execution was database3? What if it was executed once in database4 and once in database5?

Te idea is that execution plans are no tied to a database.

Answer 2

The BOL entry for sys.dm_exec_query_stats mentions sys.dm_exec_query_plan

SELECT *
FROM
    sys.dm_exec_query_stats s
    CROSS APPLY
    sys.dm_exec_sql_text( s.sql_handle ) t
    CROSS APPLY
    sys.dm_exec_query_plan(s.plan_handle) foo
WHERE
    foo.dbid = DB_ID('My_DB')
ORDER BY
    s.max_elapsed_time DESC

Edit: I've not tried it. I generally worry about server load...

Answer 3

It sounds like you are trying to tune the databases. Your longest running queries may not be your costliest ones, or the ones that you will benefit from the most.

You should read up on the SQL Server Profiler to identify the queries that use the most resources and work on them.