Question

Explain how Dynamic Management Views could be useful for monitoring performance of a database.  As part of your answer, provide an example of a Dynamic Management View that you may choose to query and why.

Answer 1

Dynamic Management Views are basically the virtual tables which are derived dynamically from the real tables or actual databases. These views can be formed from a single table or even multiple tables of the database.

Here, I would be quoting various scenarios where the Dynamic Management Views are of greater importance with the help of very simple examples:

1. Let us suppose, we have a college database. It has a separate table for every department in the college containing the details of all the students present in that department/branch. Let us now consider a Branch CSE of the college, whose table consists the details(name, id no. , marks, gpa) of all of its students. Now, if we want the list of the students whose gpa>8, then here, creating a different table is not a good option. This is because if a new student with gpa>8 is added in the CSE Branch, then unnecessarily the details of this student has to be made in 2 tables, and we may have data redundancy. Also, even data inconsistency is also possible as if any update occurs and is made only in 1 table and not the other table. Then, in such a case, a View is the solution which comes into play.

A View creates a dynamic view of the original CSE branch table with the students having gpa>8. In other words, a virtual table of such students will be formed. The advantage of this will be that no new table will be created and also if a new student in entered with gpa>8, then its details will automatically or dynamically be updated in this virtual table.

2. Also, the creation of these Dynamic Management Views not only ensures Data consistency, but also inculcates the security to the database. This is because, now through views, it is not the actual tables of the databases which are being viewed to the user, but they are the virtual tables in the form of views. So it provides data abstraction sort of thing by displaying only the essential details to the users and hiding all the background details from the user, to ensure more level of security.

In this way, these dynamic management views are useful for monitoring the performance of the database.

Hope it helps.....