July 31, 2017 at 11:12 am
Okay... I started using a presentation tool to help document the application I am building. Noticed yesterday that I could create a quiz with it too. Have to share ya know 🙂
Not a lot about specific DMV questions. Most are general...and only 7 questions.
DM View quiz
July 31, 2017 at 12:56 pm
I had a question about your first question, in what databases are DMVs stored in. I selected ALL, which the quiz said was wrong. However, I can clearly see many of them in each database, even TEMPDB, through Object Explorer in SSMS. Any ideas why ALL is not the correct answer?
July 31, 2017 at 1:16 pm
Chris Harshman - Monday, July 31, 2017 12:56 PMI had a question about your first question, in what databases are DMVs stored in. I selected ALL, which the quiz said was wrong. However, I can clearly see many of them in each database, even TEMPDB, through Object Explorer in SSMS. Any ideas why ALL is not the correct answer?
"All DMV's/DMF's are stored in the master database, under the sys schema."
Right? Wrong? who knows! It wouldn't unnecessarily be impossible to have the view / function definition stored in master, but point at the database being set as current for at least the database specific dmv's right?
July 31, 2017 at 1:18 pm
Thanks for looking at it...this was my first pass at creating a quiz. New tool, and everything.
To answer your question: The answer was the mssqlsystemresource database. SSMS can be a misleading, although the intention is good. Log in to those databases and look at what is and what is not in sysobjects. Or just look at model.
I may be wrong with my answer on the quiz, but I don't think I am.
July 31, 2017 at 1:23 pm
Okay...Like the reference. I am still thinking mssqlsystemresource.... could be wrong. There is a way to get some empirical evidence. Away at the moment, but now I am curious. Good stuff.
July 31, 2017 at 1:31 pm
I guess the main point I'd like to make is the quiz UI in general is good, but you might want to give some explanation for your answers at the end, similar to how QOTD works here.
July 31, 2017 at 1:40 pm
Point taken! I was struggling while learning the tool this a.m.
Appreciate the feedback.
July 31, 2017 at 1:45 pm
I answered one wrong but never learned the right answer.
July 31, 2017 at 1:48 pm
nerdCat - Monday, July 31, 2017 1:23 PMOkay...Like the reference. I am still thinking mssqlsystemresource.... could be wrong. There is a way to get some empirical evidence. Away at the moment, but now I am curious. Good stuff.
I would think there is a difference between the structure and the data itself.
If everything is in the resource database then how can you restore XYZ database to a different server and get the same results from something like sys.sql_modules? Based on your assumption, wouldn't you have to restore the resource database to that other server as well?
Sue
July 31, 2017 at 1:48 pm
A definite flaw with my quiz. Will get that fixed and remediate in the future.
You know the question?
July 31, 2017 at 1:59 pm
Sue...
Second attempt at posting this ??
Think of the views as definitions that pull information real time. They do not have 'static' data that they reference (in theory).
Great question. I may later video a trick with the resource database that I pulled off years ago.
Here is a nice article written when someone bumped into a problem.
https://sqlstudies.com/2013/03/04/what-is-mssqlsystemresource/
July 31, 2017 at 2:03 pm
nerdCat - Monday, July 31, 2017 1:48 PMA definite flaw with my quiz. Will get that fixed and remediate in the future.You know the question?
For mine, I forgot already LOL, maybe display the correct one for a few seconds then go to the next one? Just a suggestion!
July 31, 2017 at 2:26 pm
nerdCat - Monday, July 31, 2017 1:59 PMSue...Second attempt at posting this 😳Think of the views as definitions that pull information real time. They do not have 'static' data that they reference (in theory).Great question. I may later video a trick with the resource database that I pulled off years ago.Here is a nice article written when someone bumped into a problem.https://sqlstudies.com/2013/03/04/what-is-mssqlsystemresource/
What I said is there is a difference between the data and the structure.
No offense to the author but that's a fairly wimpy article on the resource database. It's a little more educational to attach copies as a normal database, connect with the DAC and start playing around, going through the objects and watching with a trace or extended events.
Sue
July 31, 2017 at 2:53 pm
Hey Sue....
I am definitely missing something here. The article I shared wasn't meant to be an inclusive resource db topic. I did like the query examples.
A long time ago I did attach the resource database to an instance under a different name and started looking around in it. It's read only which I think you know already. Definitely not about storing data. The good old fashion sysobjects, sysindexes, etc. tables from the Sybase days are still in every database still. Not 100% sure, but believe the views reference these physical objects. Restoring db xyz to another instance will have the same DMV results.
It is my understanding that the resource database was put in place to assist with upgrades/patches back in SQL Server in 2005. Keeping the data and structure different..like you said?
~Jeff
July 31, 2017 at 3:09 pm
As it says:
It is a read-only database that contains system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
So to me it seems there could be different answers. Logically they are in every database. The definitions in the Resource database but no data. That is why I was referring to the difference between the definition vs the data.
I don't know if every dynamic management object is in the resource database or not though. If I had to guess I would say yes as one of the reasons was making it easier for SPs, CUs, etc since you don't have to drop and recreate the system objects as before.
Sue
Edit:typo
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply