What is the difference between DMV, DBCC and DMF

Question

Post reply

What is the difference between DMV, DBCC and DMF

rajeshjaiswalraj

SSCrazy

Points: 2574
More actions
May 28, 2015 at 11:54 pm

#301066

What is the difference between DMV, DBCC and DMF

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

rajeshjaiswalraj (5/28/2015)
What is the difference between DMV, DBCC and DMF

My broad answer to your equally broad question is that these are initialisms for different sets of functionality.

Perhaps you would take the time to prove that you have done at least some prep work yourself by asking questions which are more specific?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

DMV's are views, DMF's are functions. Dynamic Management Views and Functions were introduced in SQL 2005 and allow you to look at information that is stored in the Query Cache, query plans, CLR Data, Extended Events, Resource Governor info, etc.

DBCC is somewhat of a different animal but the functionality overlaps with DMV/DMF's. DBCC allows you to check for errors with your DB (e.g. DBCC checkdb, checktable, checkfilegroup, etc...) You can use DBCC SET STATISTICS to get I/O, CPU, etc information about queries, stored procs, etc... You can use DBCC to clear the query cache.

There is a lot to all three of these things. I do recommend a google search. I have also discovered Extended Events recently. I use that for a lot of things that I used to do with DBCC, and Dynamic Management functions/views.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

In general practice, there's little distinction between Dynamic Management Views and Dynamic Management Functions. Most of the time they're all referred to as DMVs collectively. DBCC now means Database Console Commands. It came out of the old Database Consistency Checks and you'll still hear that term used. As was described, there is some overlap, but reading the documentation on each type is the way to go to reach understanding.

"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

rajeshjaiswalraj SSCrazy Points: 2574 More actions · Answer 4

Thanks for your Reply. I want to know how it will work.

Where DBCC or DMV will store. What its working.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Grant Fritchey (5/29/2015)
In general practice, there's little distinction between Dynamic Management Views and Dynamic Management Functions. Most of the time they're all referred to as DMVs collectively. DBCC now means Database Console Commands. It came out of the old Database Consistency Checks and you'll still hear that term used. As was described, there is some overlap, but reading the documentation on each type is the way to go to reach understanding.

I believe Sybase still uses the DBCC commands too

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

rajeshjaiswalraj (5/29/2015)
Where DBCC or DMV will store. What its working.

DMVs don't store data, they're views into SQL's internal memory structures mostly, or database structures.

DBCC statements are very varied and there's little consistency there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

GilaMonster (5/29/2015)
rajeshjaiswalraj (5/29/2015)
Where DBCC or DMV will store. What its working.
DMVs don't store data, they're views into SQL's internal memory structures mostly, or database structures.
DBCC statements are very varied and there's little consistency there.

Yep. Not a single consistent thing depending on which DMV or DBCC command we're talking about.

"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