Tracking MS SQL CPU Usage

--T-SQL to identify the Top 20 most costly queries in terms of Total CPU:

SELECT TOP 20

    st.dbid,

    DB_NAME(st.dbid) AS Database_Name,

    qs.sql_handle,

    qs.execution_count,

    qs.total_worker_time AS Total_CPU,

    total_CPU_inSeconds = --Converted from microseconds

    qs.total_worker_time/1000000,

    average_CPU_inSeconds = --Converted from microseconds

    (qs.total_worker_time/1000000) / qs.execution_count,

    qs.total_elapsed_time,

    total_elapsed_time_inSeconds = --Converted from microseconds

    qs.total_elapsed_time/1000000,

    st.text,

    qp.query_plan

FROM

    sys.dm_exec_query_stats AS qs

        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

        CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC

--This shows the number of connections per each DB:

SELECT

    DB_NAME(dbid) as DBName,

    COUNT(dbid) as NumberOfConnections,

    loginame as LoginName

FROM

    sys.sysprocesses

WHERE

    dbid > 0

GROUP BY

    dbid, loginame

--And this gives the total:

SELECT

    COUNT(dbid) as TotalConnections

FROM

    sys.sysprocesses

WHERE

    dbid > 0