DBA Role Hand Book queries

-----------------------Most CPU consumpable objects------------------------------------------
SELECT TOP 10
    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,TextData           = qt.text
    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
    ,Executions         = qs.execution_count
    ,TotalCPUTime       = qs.total_worker_time
    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count
    ,DiskWaitAndCPUTime = qs.total_elapsed_time
    ,MemoryWrites       = qs.max_logical_writes
    ,DateCached         = qs.creation_time
    ,DatabaseName       = DB_Name(qt.dbid)
    ,LastExecutionTime  = qs.last_execution_time
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_worker_time DESC

---------------------------------------------------------------------------------------------------------------------------------

select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

--select text from sys.dm_exec_sql_text(0x050014002002917240038FCD000000000000000000000000)

--------------------------------- Find queries that have the highest average CPU usage------------------

SELECT TOP 10
    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,TextData           = qt.text
    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
    ,Executions         = qs.execution_count
    ,TotalCPUTime       = qs.total_worker_time
    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count
    ,DiskWaitAndCPUTime = qs.total_elapsed_time
    ,MemoryWrites       = qs.max_logical_writes
    ,DateCached         = qs.creation_time
    ,DatabaseName       = DB_Name(qt.dbid)
    ,LastExecutionTime  = qs.last_execution_time
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 --ORDER BY qs.total_worker_time/qs.execution_count DESC
--where convert(varchar(30),qs.last_execution_time,121)=convert(varchar(30),getdate(),121)
order by qs.last_execution_time desc

------------------------Long running objects----------------------------------------------------------

SELECT TOP 10
(select ltrim(rtrim(text)) from sys.dm_exec_sql_text(qs.sql_handle)) as sqltext,
ObjectName          = OBJECT_NAME(qt.objectid)
,DatabaseName       = DB_Name(qt.dbid)
,so.type_desc[Typedesc]
,DiskReads          = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads        = qs.total_logical_reads  --Logical Reads are memory reads
,Executions         = qs.execution_count
,AvgDuration        = qs.total_elapsed_time / qs.execution_count
,CPUTime            = qs.total_worker_time
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites       = qs.max_logical_writes
,DateCached         = qs.creation_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
join sys.objects so on so.object_id=qt.objectid
--WHERE qt.dbid = db_id() -- Filter by current database
--where (select ltrim(rtrim(text)) from sys.dm_exec_sql_text(qs.sql_handle))  like '%userdetails%'--search by proc name
ORDER BY qs.total_elapsed_time DESC

-----------------------------------------------------------------------------------------

SELECT TOP 5
    object_name(objectID)
    ,[Avg CPU Time] = total_worker_time/execution_count
    ,execution_count
    ,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;

select ltrim(rtrim(text)) from sys.dm_exec_sql_text(0x050008000914A56540A3480B050000000000000000000000)

---------Long Running--Quries---------------------------------------------------------------

SELECT top 10 st.text,es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
   
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec
        ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er
        ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50    -- < 50 system sessions
ORDER BY es.cpu_time DESC

--select * from sysprocesses

----------------------------------------------------------------------------------------------------------------------------------------------------