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!