Notes

select * from ( select distinct v . value as vitalvalue , id . value as uom , date , Row_Number ( ) over ( order by DATE desc ) as RowCounter from VITALS V inner join VITALTYPES VT on V . VITALID = VT . ITEMID inner join ENC on ENC . ENCOUNTERID = V . ENCOUNTERID inner join ITEMDETAIL ID on V . VITALID = ID . ITEMID where ENC . PATIENTID = 84917 and VT . TYPE = 'WT' and LEN ( LTRIM ( RTRIM ( V . VALUE ) ) ) > 0 and ID . PROPID = 62 ) Temp where Temp . RowCounter between 1 and 1



select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
go


SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO





SELECT TOP 10
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%select * from ( select distinct v . value as vitalvalue , id . value as uom , date , Row_Number ( ) over ( order by DATE desc ) as RowCounter from VITALS V inner join VITALTYPES VT on V . VITALID = VT . ITEMID inner join ENC on ENC . ENCOUNTERID = V . ENCOUNTERID inner join ITEMDETAIL ID on V . VITALID = ID . ITEMID where ENC . PATIENTID = 84917 and VT . TYPE = 'WT' and LEN ( LTRIM ( RTRIM ( V . VALUE ) ) ) > 0 and ID . PROPID = 62 ) Temp where Temp . RowCounter between 1 and 1%';

select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
go