System DMV's Cause Blocking

Question

Post reply

System DMV's Cause Blocking

lmarkum

SSCertifiable

Points: 7985
More actions
March 25, 2015 at 4:39 pm

#299074

Has anyone ever heard of queries against DMVs like sys.dm_XXX causing blocking in user databases? I have never read anyone who has mentioned that querying DMVs has caused blocking in user databases but that question recently came up in my environment. I am curious what the experience of others has been.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

No. But now I'm curious about why you ask. Have you seen such a thing or are you "just" concerned?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lmarkum SSCertifiable Points: 7985 More actions · Answer 2

Jeff, I have been told that querying such DMV's caused an issue in a production environment and so there is now caution about such queries.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

lmarkum (3/25/2015)
Jeff, I have been told that querying such DMV's caused an issue in a production environment and so there is now caution about such queries.

Understood. It would be interesting to see what they found to be the problem. Do they have any documentation on the problem?

It is possible to have poor queries on the DMVs just like any other view that could cause problems. I've just not seen any yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

The index DMV, sys.dm_db_index_physical_stats, could cause blocking if you're asking for a detailed report. It's very intrusive.

I haven't seen or or heard about other DMVs causing blocking, yet.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

lmarkum SSCertifiable Points: 7985 More actions · Answer 5

Jeff, there is no documentation on what specific query they believed was the cause of the problem.

Grant, thanks for the reminder about the index DMVs.

I appreciate the replies from both of you.