Does a restored DB bring its virtual file stats with it?

Question

Post reply

Does a restored DB bring its virtual file stats with it?

winston Smith

SSCoach

Points: 19500
More actions
May 19, 2014 at 3:21 am

#286835

I have a server where a database is restored nightly.
I would assume when a DB is restored, until its queried, its virtual file stats will be showing low numbers, but this is showing very high numbers.
Do Virtual File Stats follow a database to wherever its restored?

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

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

winston Smith SSCoach Points: 19500 More actions · Answer 1

Ignore me and my laziness. I tested this and the Virtual File Stats do not follow a database after its restored.

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

If you have doubt and server is not production you can enable trace for the specific database and check the activities going on?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

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

The Virtual File Stats DMV, like many of the stats you get from DMVs are stored in memory, are specific to an instance and are cleared when SQL restarts.

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: 398882 More actions · Answer 4

free_mascot (5/19/2014)
If you have doubt and server is not production you can enable trace for the specific database and check the activities going on?

You can trace production too, surely. Just with appropriate filters in place, not using the Profiler GUI, etc.

"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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

A database restore performs mass write operations to data and log files, so should we really expect dm_io_virtual_file_stats to be near zero after a restore?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

Yes, because the restore doesn't use the same methods as normal queries do when they're reading/writing. Restore doesn't write much at all to the tran log.

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 7

Grant Fritchey (5/19/2014)
free_mascot (5/19/2014)
If you have doubt and server is not production you can enable trace for the specific database and check the activities going on?
You can trace production too, surely. Just with appropriate filters in place, not using the Profiler GUI, etc.

Thank you, Grant. I was just extra cautious. 🙂

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."