From MSDN it looks like the database id will not be populated if the query is ad-hoc or prepared. I take the first one as run from SSMS and the 2nd one as run from some sort of tool. So what it logs the database id for is beyond me. If anyone knows the details, or a way to get the database id for a query that was run regardless of how, I would love to hear it.
Right now, here is what I am doing:
alter procedure DB_QUERY_HISTORY(@mins_ago int)
as
set @mins_ago = @mins_ago * -1
insert into DB_QUERY_HISTORY
select
max(cur.last_execution_time),
cur.sql_handle,
sum(cur.execution_count),
sum(cur.last_worker_time) cpu_time_microseconds,
sum(cur.last_elapsed_time) run_time_microseconds,
sum(cur.last_physical_reads) physical_reads,
sum(cur.last_logical_writes) logical_writes,
sum(cur.last_logical_reads) logical_reads
from
sys.dm_exec_query_stats cur left outer join DB_QUERY_HISTORY hist
on cur.sql_handle = hist.sql_handle
and cur.last_execution_time = hist.last_execution_time
where
cur.last_execution_time > DATEADD(minute,@mins_ago,getdate())
and hist.last_execution_time is null
group by cur.sql_handle
create table DB_QUERY_HISTORY(
last_execution_time datetime not null,
sql_handle varbinary(64) not null,
execution_count bigint,
cpu_time_microseconds bigint,
run_time_microseconds bigint,
physical_reads bigint,
logical_writes bigint,
logical_reads bigint,
primary key (last_execution_time,sql_handle)
)
However, this leaves me with loads of garbage in the table, mainly from SSRS and SQL Agent. But it will get what I want, for the most part.
index > SQL Server Database Engine > Monitoring activity with sys.dm_exec_query_stats