Category: DMVs

Favorite DMVs: sys.dm_exec_input_buffer

This is post 2 in my favorite DMVs series. I’m cheating a little and picking a Dynamic Management Function for this one.

DBCC INPUTBUFFER vs sys.dm_exec_input_buffer

If you’re still using DBCC INPUTBUFFER, that’s okay. I just want to show you why you should consider using dm_exec_input_buffer  instead. According to the documentation, you can start using this function as long as your server is SQL Server 2014 SP2 or higher.

Why it’s useful

So why do I like this function so much? I like it because it’s so fast and it allows you to check input SQL commands across many different sessions at once. If your server is experiencing pressure across CPU, memory or other resources, having something this fast in your toolbelt can be incredibly useful.

Useful columns

event_info: This nvarchar(max) column is the only output column that I care about it, and it’s the query that was submitted to your server

Examples

To set up an example for this post, I’ve created a parameter sniffing scenario that I use in my Plan Cache Performance Tuning session.

Essentially, there’s a plan in cache for PostType = 1, and I’m running a lot of queries looking for PostType = 3. The plan in cache isn’t optimal for the queries executing, and they take a long time to execute. That said, here’s the example:

SELECT 
	s.program_name, 
	input_buffer.event_info
FROM sys.dm_exec_sessions as s 
JOIN sys.dm_exec_requests as r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) as input_buffer
WHERE s.is_user_process = 1 
and s.status = 'running'

More details

I’ll be posting a full script using some of these DMVs in my blog later this week, so please stay tuned!

Favorite DMVs: sys.dm_exec_sessions

Here’s a link to the Microsoft documentation on this DMV.

This is part 1 in a series I’m writing about my favorite Dynamic Management Views in SQL Server. I’m going to talk about how they’re useful, and then build on each post in the series by showing they’re connected.

Why it’s useful

Well, as the name implies, this DMV shows all the current sessions connected to your system. When I think of current sessions, I often think about queries that users are running on the server, but there’s also system activity. When I’m troubleshooting, sometimes I want to see both user and system activity, and this DMV allows me visibility to both.

Useful columns to query

login_name: Find the user who’s executing the session on your server

host_name: Find the computer that’s hosting the session

reads, writes, and logical reads: Useful for judging the total activity of one session

is_user_process: 0 for system processes, 1 for user processes

status: There’s a few different status but the most important ones are sleeping or running

program_name: The name of the program used to create this session. See the example below

Example query using this DMV

SELECT host_name, login_name, writes, reads, logical_reads, program_name
FROM sys.dm_exec_sessions as s 
WHERE s.is_user_process = 1
and s.status = 'running'
ORDER BY s.writes DESC


By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close