Get CPU utilization by database

Ever wonder which database on your SQL Server instance is consuming the most CPU power? This handy SQL query helps you pinpoint those resource-intensive databases.

WITH DB_CPU
AS (
 SELECT pa.DatabaseID
  ,DB_Name(pa.DatabaseID) AS [Database Name]
  ,SUM(qs.total_worker_time / 1000) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY (
  SELECT CONVERT(INT, value) AS [DatabaseID]
  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
  WHERE attribute = N'dbid'
  ) AS pa
 GROUP BY DatabaseID
 )
SELECT ROW_NUMBER() OVER (
  ORDER BY [CPU_Time_Ms] DESC
  ) AS [CPU Rank]
 ,[Database Name]
 ,[CPU_Time_Ms] AS [CPU Time (ms)]
 ,CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank]
OPTION (RECOMPILE);

Keep in mind that the results of this query depend on the currently cached query plans.

For more in-depth information on the Dynamic Management Views (DMVs) used in this query, check out the official Microsoft documentation:

• sys.dm_exec_query_stats
• sys.dm_exec_plan_attributes

SQL Server Interview Question Reference

	Essential SQL Interview Questions to Prepare For
	SQL Workload Migration: Data Engineer Interview Questions & Answers for Microsoft Azure Database Platforms
	SQL Server Connectivity Interview Questions & Answers for Database Administrators
	Senior SQL Server Database Administrator Interview Questions & Answers
	Scenario-Based Interview Questions for Junior SQL Server Database Administrators
	Microsoft Azure Administrator: Scenario-Based Interview Questions & Answers
	SQL Server Replication: Interview Questions & Answers
	SQL Server Always On Availability Groups: Interview Questions & Answers
	SQL Server Transaction Log: Interview Questions
	SQL Server Auditing: Interview Questions
	SQL Server Execution Plan: Interview Questions
	Azure Data Factory: Interview Questions & Answers
	Top 25 SQL Interview Questions & Answers on Indexes
	Phone Interview Questions for SQL Server DBAs
	SQL DBA: Interview Questions & Answers (Part 2)

Get input buffer information for the current database

Exploring Input Buffer Information for Non-System Sessions in SQL Server

Ever needed to peek into what all your active, non-system SQL Server sessions are actually doing? This handy SQL query allows you to retrieve input buffer information for all user sessions within the current database, giving you insight into the commands or queries they are executing.

SQL Query to Get Session Input Buffer Details

SELECT
    es.session_id,
    DB_NAME(es.database_id) AS [Database Name],
    es.login_time,
    es.cpu_time,
    es.logical_reads,
    es.memory_usage,
    es.[status],
    ib.event_info AS [Input Buffer]
FROM
    sys.dm_exec_sessions AS es WITH (NOLOCK)
CROSS APPLY
    sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE
    es.database_id = DB_ID()
    AND es.session_id > 50 -- Excludes system sessions
    AND es.session_id <> @@SPID -- Excludes the current session
OPTION (RECOMPILE);

Related Commands and Resources

For quick checks, you can also use the DBCC INPUTBUFFER command:

DBCC INPUTBUFFER

For more in-depth information, check out these Microsoft documentation pages:

• sys.dm_exec_input_buffer (Transact-SQL)
• New DMF for retrieving input buffer in SQL Server

Last VLF status of the current database

When you're working with SQL Server, understanding your transaction log files (VLFs) is crucial for database performance and management. This quick T-SQL snippet helps you get a snapshot of the most recent Virtual Log File (VLF) for your current database.

Understanding Your Latest VLF

This SQL query leverages the sys.dm_db_log_info dynamic management function, which provides detailed information about the virtual log files of the transaction log. By ordering the results by vlf_sequence_number in descending order and selecting only the top 1, we can quickly pinpoint the most recently created VLF.

SELECT TOP (1) DB_NAME(li.database_id) AS [Database Name]
 ,li.[file_id]
 ,li.vlf_size_mb
 ,li.vlf_sequence_number
 ,li.vlf_active
 ,li.vlf_status
FROM sys.dm_db_log_info(DB_ID()) AS li
ORDER BY vlf_sequence_number DESC
OPTION (RECOMPILE);

What the Columns Mean:

• Database Name: The name of the database.
• file_id: The ID of the log file.
• vlf_size_mb: The size of the VLF in megabytes.
• vlf_sequence_number: The sequence number of the VLF, indicating its order of creation.
• vlf_active: Indicates if the VLF is currently active (1 = active, 0 = inactive).
• vlf_status: The status of the VLF (e.g., 2 for active, 0 for reusable).

This query is a handy tool for database administrators looking to quickly assess the state of their transaction log and identify potential issues related to VLF fragmentation.