Resource database is read-only

Question

Post reply

Resource database is read-only

Golfer22

SSCrazy

Points: 2256
More actions
December 25, 2012 at 8:43 pm

#267943

Even though the resource database is read-only, it is updated by SQL Server, right?
Does sys.dm_db_missing_index_details have information that results in "missing indexes" from ad hoc queries that have been ran once?
sys.dm_db_missing_index_details resides in the resource database, right?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Golfer22 (12/25/2012)
Even though the resource database is read-only, it is updated by SQL Server, right?

Yes

Golfer22 (12/25/2012)
Does sys.dm_db_missing_index_details have information that results in "missing indexes" from ad hoc queries that have been ran once?

Yes

Golfer22 (12/25/2012)
sys.dm_db_missing_index_details resides in the resource database, right?

Yes

Here is link http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Golfer22 (12/25/2012)
Does sys.dm_db_missing_index_details have information that results in "missing indexes" from ad hoc queries that have been ran once?

Also check About the Missing Indexes Feature.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Golfer22 (12/25/2012)
Even though the resource database is read-only, it is updated by SQL Server, right?

No. It contains no data that would require modifying, just the definitions of many of the system objects

Does sys.dm_db_missing_index_details have information that results in "missing indexes" from ad hoc queries that have been ran once?

Maybe. It keeps only a certain number of index suggestions.

sys.dm_db_missing_index_details resides in the resource database, right?

The definition might, the data most certainly does not. It's a DMV which means it's a view into an internal memory structure, it's not data stored in a database on disk.

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