August 8, 2012 at 3:52 pm
I am using the following query on multiple SQL Servers with great success. Some of them have Offline databases and I experience no issues.
There is one server, however, that always errors with this query because a database is Offline. I even have that database listing in the WHERE clause (IBilling).
Any ideas as to what may be different on this server? I have already put the database back online and taken it offline again with no success. Also, the powers that be want this database to remain connected and offline for now.
SELECT TOP 10 db.name,
[text],
last_execution_time,
( total_logical_reads + total_physical_reads + total_logical_writes ) / execution_count,
( total_worker_time / execution_count ) / 1000000.0,
( total_elapsed_time / execution_count ) / 1000000.0,
execution_count,
qp.query_plan,
@curDate
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.databases db
ON qp.dbid = db.database_id
WHERE db.name NOT IN ('master', 'msdb', 'model', 'tempdb', 'aaService', 'IBilling', 'iBillingRestore', 'IBilling_restore_2', 'iBillingV3_NotProd')
ORDER BY total_elapsed_time / execution_count DESC;
Thanks!
August 9, 2012 at 12:32 am
Could you please try this query
SELECT TOP 10 db.name,
[text],
last_execution_time,
( total_logical_reads + total_physical_reads + total_logical_writes ) / execution_count,
( total_worker_time / execution_count ) / 1000000.0,
( total_elapsed_time / execution_count ) / 1000000.0,
execution_count,
qp.query_plan
@curDate
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.databases db
ON qp.dbid = db.database_id
WHERE db.state_desc = 'ONLINE'
AND db.database_id > 4
ORDER BY total_elapsed_time / execution_count DESC;
Thanks
August 9, 2012 at 9:39 am
Same result. I tried looking for the ONLINE description yesterday, and I still get the error with your slightly different version.
I do like the dbid > 4 part. Much easier to eliminate the system dbs. Thanks!
August 9, 2012 at 10:05 am
The problem is that one of those queries involves that offline database. I would imagine that, on the servers where this query worked, none of the top 10 longest running queries involved an offline database.
Specifically, sys.dm_exec_query_plan is what's most likely causing the error. EDIT: Though you are trying to eliminate offline databases with the join back to sys.databases, the TVF is being evaluated before this join takes place.
Try the query without sys.dm_exec_query_plan:
SELECT TOP 10
qs.plan_handle,
[text],
last_execution_time,
( total_logical_reads + total_physical_reads + total_logical_writes ) / execution_count,
( total_worker_time / execution_count ) / 1000000.0,
( total_elapsed_time / execution_count ) / 1000000.0,
execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
--CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--INNER JOIN sys.databases db
--ON qp.dbid = db.database_id
--WHERE db.state_desc = 'ONLINE'
--AND db.database_id > 4
ORDER BY total_elapsed_time / execution_count DESC;
I'd bet that one of the sql statements includes the offline db.
August 9, 2012 at 11:49 am
In theory it seems to me you could simply add
AND st.dbid IS NOT NULL
to your where conditions and that would clear up the issue?
Erin
August 9, 2012 at 12:47 pm
I've edited my original response. With regards to filtering where st.dbid or qp.dbid is not null, there's still the same problem of the TVF being evaluated first.
But this actually got me thinking, since dm_exec_query_plan often returns NULL for the dbid, and the OP is performing an INNER join, he may in fact be getting incorrect results even on servers where there's no error. If I change that to a LEFT OUTER join to sys.databases, I get vastly different results.
SELECT TOP 10 db.name,
qs.plan_handle,
[text],
last_execution_time,
( total_logical_reads + total_physical_reads + total_logical_writes ) / execution_count,
( total_worker_time / execution_count ) / 1000000.0,
( total_elapsed_time / execution_count ) / 1000000.0,
execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
LEFT OUTER JOIN sys.databases db
ON qp.dbid = db.database_id
--WHERE db.state_desc = 'ONLINE'
--AND db.database_id > 4
--and qp.dbid is not null
ORDER BY total_elapsed_time / execution_count DESC;
August 10, 2012 at 10:54 am
Very nice! That last one solved my problem.
Thanks a ton!
Jerry
August 10, 2012 at 1:19 pm
I think you should add in the query_plan field from the dm_exec_query_plan DMV so you have a clickable link that will open up the actual execution plan for each top running query (might prove to be very helpful)
To add on to Colleen's post SELECT TOP 10
db.name,
qs.plan_handle,
[text],
last_execution_time,
(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count,
(total_worker_time / execution_count) / 1000000.0,
(total_elapsed_time / execution_count) / 1000000.0,
execution_count,
qp.query_plan
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
LEFT OUTER JOIN sys.databases db
ON qp.dbid = db.database_id
WHERE
db.database_id > 4
ORDER BY
total_elapsed_time / execution_count DESC;
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply