Too true that DMVs are, at least on the face of them, needlessly complex!  I'll still encourage their use as I believe they're two steps forward (in power) if also one step backward (in usability).  Maybe it's because the MS engineers figured "hey, we're giving this DMV stuff away and it's not driving revenue, so just make them a step up from our old undocumented stored procedures - complex but useful little Easter eggs."  

The only differences are these are documented and I'd argue they're starting to drive some revenue.

I'll readily admit to having a "spell book" for rooting out recently executed queries, long running transactions, high I/O queries, memory allocation, web server connections, and many more.  But just because I have to open up my spell book doesn't make me less of a wizard!

And the simple solution you seek may be one you have to build - create your own views based on the DMV information you want.  Put them in the model database so they propagate to new DBs nicely and keep the creation scripts handy for deployment to existing databases.

And one of my favorite tricks: put some of your frequently used DMV calls in your snippets in SQL Prompt.  You can even use parameters as if the snippet was a template and get the best of both worlds.  Type your snippet, allow SQL Prompt to auto complete the page of alphabet soup.  Then with a simple Ctrl-M, replace the parameters and off you go.  Sharing is as simple as cracking open the XML and distributing pieces.

Tony, I must say I have a love-hate relationship with the DMVs:

-----------------------------
AN OPEN LETTER TO THE SQL SERVER DMVs

Why oh why, DMVs do you fail to satisfy all my needs?  I tried to do what you asked; I left the system tables for you didn't I?  Not that you gave me much of a choice DMVs - your friend, Microsoft, removed access to them when they introduced you to me in SQL 2005.  But what am I supposed to do when I'm with you and I need to find out what database NAME your exposed database_id relates to?  How difficult would it have been for you to include database_name as a column in your returned results, rather than make us run off to the system catalog views (sys.sysdatabases) anytime we needed a peek at your database name?  You want us to work with you and only you, but you make us run to sys.sysprocesses if we need more metadata about transactions and sessions than you give us in any of your sys.dm_tran... DMVs.  In theory you've so much potential, but unfortunately you're only a shell of what you could be DMVs.  I still must rely on the the catalog views.  They complete me.

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

Seriously though, Microsoft had been warning the DBAs for years and multiple releases to avoid reliance upon the system tables.  The reason we made such deep use of these tables was to mine information and build useful processes that were not available to us via the management tools.  Something as simple as index rebuilds based upon existing fragmentation still requires use of the DMVs and custom Transact-SQL coding rather than exposure in the Maintenance Plans!  (It's not that I advocate reliance on Maintenance Plans, but for the smaller enterprises without an IT department or dedicated/trained DBA it's a necessity that they be simple, powerful, and a viable option, particularly if Microsoft wishes to expand their customer base.)

I alluded to my biggest complaints in my diatribe above.  What good is served when trying to review transaction information via dm_tran_active_transactions, dm_tran_database_transactions, or dm_tran_active_transaction if the sql_handle is not an exposed column in the DMV?  If you wish to see the query pertaining to the records returned from any of those DMVs (and countless others) you need to join them to dm_tran_session_transactions (in order to expose the session_id) then join to sys.sysprocesses on the spid column in order to return a sql_handle to feed to the sys.exec_sql_text() DMF.  Exhausting!  Include sql_handle in any DMV that exposes transaction information!  There is no additional overhead, it's a non-materialized view!  

The same goes for database_name exposure in the DMVs.  Firstly, any DMV that exposes database_id should also expose database_name.  Secondly, be consistent; if you're going to make us alway join back to sys.sysdatabases.dbid, then expose that information in the DMVs as dbid, not database_id.  That alone would make things easier when joining via use of Red Gate SQL Prompt.

Carter, great ideas in your comment.  I plan on trying them out at the next opportunity.  However I will still be forced to utilize less-than-optimal DMV functionality and structure, therefore I suspect that my time won't be freed up to do so until sometime in 2011 (if Microsoft cares to listen.)

So what would you have them give us instead?  Dumbed down versions that left us wanting for more and keep the "deep" information hidden in undocumented DBCC functionality?  (Not that the documentation of the DMV's in BOL can truly be considered documented.

The DMVs need to be as complex as they need to be, in order to tell the true story.  I think they have even tried to give us a "simple" view of the data in the Management Reports, but they are not rich enough.  The real complexities are in all the stuff that has recently been added to the product.  Partitions make disk info more difficult.  MARS makes it no longer simply sessions, but sessions and requests.  NUMA makes memory information have extra rows.  Multiple processors/cores... You get the point, I hope. DMVs are complex not just to make it hard to work with.  They are complex because the problem is complex.  Not unlike a well normalized database can seem complex if you don't care about some of the intricacies in the data, but without the complexity, you lose more in information than you gain in ease of use in the long run.

Tim Ford said: "Firstly, any DMV that exposes database_id should also expose database_name."

I have asked for that via connect for catalog functions and was denied.  The answer: "If we expose such names there, we would probably ends up keep the id columns there too. This is for end users that care about catalog query performance as the index keys on underlying system tables include id, not name. We feel that given we have handy intrinsic functions, it is sort of redundant effort."

In other words, it is a slippery slope. Add database name, they will have to add filegroup name, file name, table name, column name.... etc.  I completely agree with you on naming consistency. All of the intrisic functions need to keep up with the times, as do catalog tables/dmvs, even if it means having two database_id columns, with one dbid in a compatibility version.

But all of this is a different kind of complex. Clearly there is a slow progression from old style to new style.  The question is does Microsoft provide us "simple" versions of the DMV or is that the communities job?  I know I have a set of DMV queries that I use regularly that provide me more information than I could ever have gotten from 2000's catalog. And they should definitely do more to bring their system functions up to date by adding newly named versions to match the newer (and almost always better), names.  So instead of just having DB_ID(), have database_id() also.  DB_ID would be considered depricated (and assuming you have good code practices, as easy as pie to change to.)

So to answer the main question, I personally believe that it is their job to document the heck out of the complexities, and our job as a users/community/toolmakers to build the facade on all of the system functionality that Microsoft gives us.  They are involved too as tool creators, doing what they can to give us to use the data, but frankly I prefer something complex enough that it gives me EVERYTHING rather than a dumbed down version for two reasons:

1. If I need some info, it is there.  I just add it to the queries I have created.
2. So often the tool maker's vision is not exactly mine.  If I want to do something "different" with the data, then I can.  I like tools, and use Microsoft, CA and RedGate tools quite often.  But when I need something "interesting" done, well, there it is.

And one additional reason. Even if there was a dumbed down set of DMVs, I would still want to know about and write about the complex ones.  And once you learn the deep way of doing something, would you ever go back?  Not me :)

"Everything should be as simple as it is, but not simpler. "
--Albert Einstein


If I were a principal at Redgate, I believe I would see this complexity as a business opportunity.  The value of many of your products is that they successfully encapsulate this sort of complexity in SQL Server, making it simpler and faster for users to get useful information out of the system internals.  It is important for your business that your developers understand the DM objects very well and that the casual user does not.

Although it is difficult to imagine the casual user doing much with these DM objects, I don't believe they were created for the casual user.  It is not simply that the syntax is too difficult.  It is that few casual users would understand what the raw data means or how they could act on it.

A professional owes it to his clients to learn the things that differentiate a professional from an amateur.  There is a lot to learn, but it is conceptually not difficult. Data management views and functions are a tremendous resource, an absolutely essential set of tools for a SQL Server professional. I can think of no other area of SQL Server where a bit of study would pay such great dividends.

I am much more annoyed at the SQL Server team's attempts at simplification than at the complexities involved in viewing the internals of the database engine. The simplifications are based on the ridiculous proposition that anyone can create and manage a relational database without knowledge or training.  They encourage unqualified people shoot their foot off.

Ah, but *are* the DMVs/DMFs aimed at the casual, novice user?  I highly doubt such.  The issue is that Microsoft has made the concept more complex than it needs to be; my main issue with Oracle for many years.  

In Oracle's case it has always seemed that they make their product so complex because they are in the business of selling training much more than selling software.

I think in Microsoft's case it was due to not thinking the matter through fully before unleashing it.

PingBack from http://www.ford-it.com/sqlagentman/?p=149

For anyone struggling to come to terms with DMVs, the Performance Dashboard SSMS reports that query the DMVs on your behalf do a pretty good job of telling you what's going on within SQL Server:

http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Reading your article brought back memories of the days when I was a product manager dealing with engineering groups for Unix, groupware, data warehousing and other projects.

I'd tend to lay 'the blame' for complexity shortcomings at the feet of Product Management instead of engineers.  Engineers tend to look at requirements through the lens of a functional specification.  It is a rare Product Manager that knows how to deliver requirements that are fully developed enough to include not only functionality but also important factors such as usability, reliability, serviceability, etc.

Also bear in mind that sometimes things have to 'appear' complex because of the incredible amount of functionality they offer.  When this is occurring, very verbose and comprehensive documentation on the use of the feature/function set is key to helping people over the learning curve.  

Sadly, Engineers tend to abhor documenting 'their baby'.  

Also sadly, the industry has developed to a state where most people think an over-hypertexted pile of sentences constitutes good documentation.  

It has been my experience that when I try to figure out a new function in SQL Server using BOL, I tend to exhaust myself trying to find out how to use a new function by first trying to find documentation on the new function.

When I finally arrive at the page that describes the new function, I am usually greeted with a 'railroad diagram' of the syntax.  Frequently so long I have to scrolll up and down to view the whole thing.  This is of course followed by a verbose discussion of every possible part of the syntax - which is useful when you need to know it, but frequently a set of good examples would suffice.  

This is where BOL many times falls apart, especially on the new functions - only the most basic examples are provided and one must use trial and error to expand them.
Sites like technet have the same structural flaws as BOL - sure the information is in there, somewhere.

I tend to find myself turning more and more to Simple-Talk (SHAMELESS PLUG) and Google.

The long and the short of this is that in my experience, things are not complex once you know how to use them.  But things that are very difficult, or more importantly TIME CONSUMING to learn tend to only get learned if there is not an alternative and one must learn them to survive.  Comprehensive documentation with a focus toward the practical usage of a feature/function set is key to quick, wide-spread adoption.

Hey Tony,

First off, no I don't think you're being wimpy on this one. DMV's are a real mixed bag. I've found the execution DMV's, such as sys.dm_exec_query_stats or sys.dm_exec_cached_plans, to be so incredibly powerful and simple to use that I can't believe there are so few people who know about them or reference them. Of course, then you run into the problems. Try pulling in the missing index information now... ah, life is not so sweet is it? Here MS has put together all this execution specific information and over on the side is also a set of execution specific information, but there's no way to link that data together, at all. Yet, under the covers, SQL Server is pulling that information in, just fine. Take a look at the plans in cache. You'll see in the compiled plans the missing index information, so somewhere, under the covers, there is a link between the compiled plan and the missing index stats.

So, while it is a mixed bag, some of it very easy and very powerful, some of it hard and powerful and some of it, hard and useless, you really do get the sense of increased power and flexibility over the old days with system tables storing some information and system procedures storing others with no easy way to join those two data sets together. Now, you query it all in the same fashion, even if some of it doesn't work quite like it should.

I think that the DMVs are placed well in SQL Server as a product. They are there for the technical DBA who will be concerned with monitoring and maintaining the factors that they report. eg. If you are worried about index fragmentation then you will more often than not be technically able to comprehend and effect an appropriate solution.

Where SQL Server is installed as MSDE/Express by a third party vendor then the application will (should) maintain the database as needed (from database backups, scheduled jobs etc to security and so on). In this case there possibly isnt a DBA for the system and it will function adequately for the lifetime of the product.

One could argue that working with DMVs currently is like using osql or working only in T-SQL rather than ever using the wizards/GUI to effect server management tasks. Should there be a section of the GUI developed to deal with DMV output/settings. Would this result in dumbing down the product? Some people expect enterprise solutions to be difficult to work with - almost counter intuitive - in order to feel the task they are performing has some skill attached to it. If the product looks like a 3yo child could work it then they may well walk away from the product as deeming it too simple to be truly effective at its proposed implementation.

In my experience (which I freely admit would rank as 'limited') many of the DMVs are only one (presentation/aggregation) layer away from usable/distributable. As Chris Howarth above mentions, adding some reports to their output gives an interpretation of the underlying data. Creating a few more (custom) reports or scheduled jobs to take actions (fragmented indexes) is not THAT tricky. If there isnt a DBA on site who can do it then the company is saving the £££ in that salary that the services could be bought in if there is a need.

Tim Ford said:
"In Oracle's case it has always seemed that they make their product so complex because they are in the business of selling training much more than selling software."

I wouldn't completely discount this for Microsoft.  I have a theory that Microsoft constantly makes unnecessary changes so that people with Microsoft certifications have to go back through training and certification in order to stay current.  Look at Exchange 2007 for example.  I personally know a Microsoft Certified Training, who was also a Microsoft Certified System Engineer specialized in Exchange.  He had YEARS of experience teaching and supporting Exchange.  When Exchange 2007 came out, he said it was like starting all over again.

Personally, I feel that way about SQL Server 2005.  I do not doubt, for one second, that it is a better DBMS than SQL Server 2000 is.  But in many ways, it feels like I have to start all over again.

We're an ISV whose product uses SQL Server for the backend.  98% of our customers have SQL Server 2000.  I still stumble every time I connect to a customer's server, attempt to run isqlw, and get a message about it not being found.  I LOVE Query Analyzer.  I'm extremely proficient in it, using shortcut keys for nearly everything.  SMSS is nice, but it is bulky.  There are things I can do in Query Analyzer that I can't find an equivalent in SMSS.

Here is another example.  We have to support SQL Server 2000, 2005, and 2008.  The last time I tried (it has been a while), I could not use SMSS 2005 to connect to a 2008 instance nor could I use SMSS 2008 to connect to a 2005 instance.  WTF?  I can use Query Analyzer to connect to any of the three versions.  So I have very little desire to USE SMSS, except when I have to.

In a way, this is the nature of software development.  You've got to add new features.  But adding new features almost never requires taking away the convenience of old features.  Or preventing you from using the old features without learning the new, and different, way to use them.  Unless, of course, the product is question is a Microsoft product.

A) The DMVs are not really all that complex.  They're deep, and there is a learning curve, but so what?  They're not designed for end users, but rather for IT professionals.  IT professionals should be able to handle this level of complexity.  It's really not that bad.

B) I've already done most of the work for you :-)

http://sqlblog.com/blogs/adam_machanic/archive/2009/02/18/who-is-active-v7-30.aspx

Tony,

While you are English and likely a wimp for that reason, I don't think that is the cause of your conclusion because you are correct. Microsoft screwed the pooch on SQL Server 2005 and 2008 by adding layers upon layers of needless complexity. There are 3 reasons for this. They are documented and there's nothing we can do about them.

1) Beginning programmers overcode everything. Microsoft hires beginners and uses them up over a 5 year period. By the time they learn to code, they are useless drooling gamers incapable of human speech.
2) Marketing people run the development teams and set the schedules. They don't understand good software design or good code. They know eye candy and all the coding is done to get the eye candy they want in the schedule they want. Nothing else matters than these.
3) Microsoft is an old company. When it started it was lean and aggressive and wanted to make fast, reliable software. It was cool. Microsoft became the IBM it defeated with Windows 3.11. It wouldn't surprise me if there were contests about who could write the most lines of code to create a development interface.

So, in spite of your Englishness, you are correct. We will have to abandon SQL Server or become like the Oracle DBAs we previously mocked. Now we know why it takes 6 Oracle DBAs to handle the same number of databases 1 SQL Server DBA used to handle; needless complexity. We are going to have to learn to be arcane and uncooperative. To be successful, we must master horribly ugly joins, demand 8-way processes and 128 Gig of Ram for our single terabyte databases, work in shifts to watch the systems when they crash because we didn't tweak that setting exactly right and on and on.

If I'm real lucky, I'll pay off all my bills in the next couple of years and become a janitor or a carpenter.

The thing that I find interesting is that the comments that say things are too complex don't list how they might make them less complex. (Tim's example of inconsistencies not withstanding.)  I have worked with them for several years now and I can't think of one example where the DMV is overly complex once I understand the reasons they are so complex.

The only way they could make them less complex is to make them self contained modules with no need for relationships to other catalog views or DMVs. DMVs are low level objects meant to provide information, and any one who has the skill/knowledge to use them should already have some basic querying skills to join to other data.  The fact is, computers are getting more and more complex, and to understand them you need to know more and more stuff.

The complexity should be documented, and there ought to be samples/modules that solve common problems, no question.  But for the most part, the DMVs themselves are fine just as they are.

I agree with Tony and am still using "sp_who2 active" to see what is going on on the server.  It gives me everything I want re processes and blocking. Luckily it still works on 2005.  Not sure if its been deprecated on 2008.  Hope not.