SQL Server STATISTICS

Question

SQL Server STATISTICS

Arsh

SSCertifiable

Points: 6354
More actions
July 27, 2017 at 2:16 am

#327689

Hi All,
We have a migration activity coming up where we backup our SQL server databases and restore on a different SAN in a different data center. My question is, does SQL server backup the STATISTICS data as well when we take a full backup ? Thanks in advance .

Viewing 15 posts - 1 through 14 (of 14 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Yes - everything that is part of the database is backed up in a full database backup. Is it really important, though? It's easy enough to rebuild the statistics after the restore.

John

Arsh SSCertifiable Points: 6354 More actions · Answer 2

Hi John ,
Thanks so much for the info. I understand that it shouldn't be really worrying as we can update stats after restore as well which I had mentioned. Our management had brought-in some former employee (very senior) for any comments before migration activity commences, who insisted that the database 'will not' perform well after migration even with stats update. Anyways , thanks again for your response.

Arshad

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Arshad

I'm sure (s)he'll be able to explain why (s)he thinks that! If it doesn't sound right to you, please post back here.

John

Arsh SSCertifiable Points: 6354 More actions · Answer 4

Hi John,
As I knew from my past experience of being part of migration activity for all the databases / apps from one data center to another , I was quite sure that the backups will suffice in terms of picking up almost every thing critical but that person disagrees on it. His point is , because new installation has new binaries , the meta data in the new servers will not have the stats data from the backups. I suppose , the restore operation does (and should) update the metadata in the new binaries and one doesn't even need to update stats actually .. please correct me if I am wrong in this sentence ...

Thank you
Arshad

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Arshad

I'm not sure what he means by the metadata in the new binaries. Everything that's in the database is backed up with the database. Sure, there are some things that the database depends on that exist at the server level, for example logins, that wouldn't be included in the backup. Statistics isn't an example of that, though. You can see for yourself easily enough, by doing DBCC SHOW_STATISTICS on a table of your (or his) choice, and comparing the output just before backup on the old database and just after restore on the new.

John

Brandie Tarvin SSC Guru Points: 173104 More actions · Answer 6

The only reason I can think of for a database restored to a different SAN to not work efficiently is because the SAN was misconfigured, causing the database to thrash when trying to read or write data. And that's a whole 'nuther problem that won't be solved by anything but rebuilding the SAN.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

Arsh - Thursday, July 27, 2017 4:13 AM
. His point is , because new installation has new binaries , the meta data in the new servers will not have the stats data from the backups. I suppose , the restore operation does (and should) update the metadata in the new binaries

What?

There's no metadata in the binaries. Database metadata (like statistics) are stored in the databases themselves, and hence included in backups and present when a DB is restored from a backup

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

Arsh SSCertifiable Points: 6354 More actions · Answer 8

Hi,
Thanks All for putting info on this .

Gail , aren't the statistics stored in system objects , like sys.sysrscols etc . am I missing something ?

Thank you
Arshad

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

Arsh - Thursday, July 27, 2017 6:53 AM
Hi,
Thanks All for putting info on this .

Gail , aren't the statistics stored in system objects , like sys.sysrscols etc . am I missing something ?

Yes they are, in the databases themselves. Those system objects are in the user databases, not in the SQL binaries.

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

Arsh SSCertifiable Points: 6354 More actions · Answer 10

But , don't the system objects physically exist in the resource database and only logically are they in each database ? this is what I've been believing in so far .. I'm surely missing something I suppose .

Thank you

Arshad

Brandie Tarvin SSC Guru Points: 173104 More actions · Answer 11

Arsh - Thursday, July 27, 2017 8:31 AM
But , don't the system objects physically exist in the resource database and only logically are they in each database ? this is what I've been believing in so far .. I'm surely missing something I suppose .
Thank you
Arshad

System objects exist on 2 levels. The server level, which is for everything that touches system and user databases, then the individual databases where the system objects only tell you about that particular database.

EDIT: And the database level ones are physically in each database. The server level ones exist in the system dbs, but certain master system objects can be called from any session context.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 12

The statistics for a user object are physically stored in the relevant user database's files. See https://dba.stackexchange.com/questions/27151/where-are-statistics-physically-stored-in-sql-server, for example.

Cheers!

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

Arsh - Thursday, July 27, 2017 8:31 AM
But , don't the system objects physically exist in the resource database and only logically are they in each database ? this is what I've been believing in so far .. I'm surely missing something I suppose .

The definition of system views and functions exists in the resourceDB. The system tables, which contain things like statistics and other database metadata, exist in the user databases.

If it worked as you believed, you would not be able to move a database from one instance to another. Since you can do that, the system tables obviously are not found in the resource DB.

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

Arsh SSCertifiable Points: 6354 More actions · Answer 14

Thank you Brandie , Jacob , Gail for such useful insights.

Thank you

Arshad

SQL Server STATISTICS

Cookies on SQLServerCentral