November 23, 2011 at 8:01 am
Gianluca Sartori (11/23/2011)
Dev (11/23/2011)
Roy Ernest (11/23/2011)
As far as I know when a Back up is done, it preserves the Stats as well.Can you please lead me to some evidence? (Curiosity kills the cat but I can't refrain from that :-D)
Not an official MSDN page, but still a good reference:
I have gone through it. See my post below...
November 23, 2011 at 8:50 am
Dev (11/23/2011)
Roy Ernest (11/23/2011)
As far as I know when a Back up is done, it preserves the Stats as well.Can you please lead me to some evidence? (Curiosity kills the cat but I can't refrain from that :-D)
This has to be some kind of joke.
For starters why in the world would a RDBMS development team exclude on purpose performance statistics during backup? Just to add overhead during backup then add overhead during restore/recovery by forcing the user to gather those again in case of a restore/recovery is needed? I really makes no sense at all.
Lastly - what prevents you to take a backup, restore it and check if performance stats are the same or not? I'm Okay with a curious cat but a lazy cat is a totally different story 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 23, 2011 at 8:57 am
A full backup is a complete copy of everything in that database at the point that the backup is taken. Distribution statistics are part of a database (they're hidden in the system tables). Hence a backup contains the distribution statistics as well as the tables, indexes, procedures and everything else that's in the 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
November 23, 2011 at 8:59 am
For starters why in the world would a RDBMS development team exclude on purpose performance statistics during backup? Just to add overhead during backup then add overhead during restore/recovery by forcing the user to gather those again in case of a restore/recovery is needed? I really makes no sense at all.
I am not sure it's for Roy or me, so I won't comment here.
Lastly - what prevents you to take a backup, restore it and check if performance stats are the same or not? I'm Okay with a curious cat but a lazy cat is a totally different story 😀
This cat is not lazy (curious though) but has limited tools in hand.
November 23, 2011 at 9:00 am
PaulB-TheOneAndOnly (11/23/2011)
For starters why in the world would a RDBMS development team exclude on purpose performance statistics during backup? Just to add overhead during backup then add overhead during restore/recovery by forcing the user to gather those again in case of a restore/recovery is needed? I really makes no sense at all.
Especially when you consider that the backup process doesn't crack the pages (why would it?) so it has no way at all of knowing what's on a page that it's copying to the backup file. As far as the backup process is concerned all it has to do is copy allocated extents to the backup buffers (and then onto the backup device), what those extents are part of is none of its concern and it would massively slow down a backup if it had to crack and interrogate each and every page that it was copying out.
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
November 23, 2011 at 9:01 am
Dev (11/23/2011)
This cat is not lazy (curious though) but has limited tools in hand.
You don't have a single SQL instance anywhere at hand and you don't have the option of downloading and installing express?
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
November 23, 2011 at 9:04 am
GilaMonster (11/23/2011)
Dev (11/23/2011)
This cat is not lazy (curious though) but has limited tools in hand.
You don't have a single SQL instance anywhere at hand and you don't have the option of downloading and installing express?
Unfortunately NO. But I will test if for sure.
November 23, 2011 at 9:33 am
FYI... I started my DB career with Oracle, currently working on SQL Server. I always try to match up the concepts in two DBs & it creates lot-of-confusion.
I found something that I would like to share with you.
Statistics in Oracle terms:
Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:
•Table statistics
oNumber of rows
oNumber of blocks
oAverage row length
•Column statistics
oNumber of distinct values (NDV) in column
oNumber of nulls in column
oData distribution (histogram)
•Index statistics
oNumber of leaf blocks
oLevels
oClustering factor
•System statistics
oI/O performance and utilization
oCPU performance and utilization[/u]
I believe SQL Server doesn't consider System Statistics (only distribution statistics) in backups and that's why it makes sense not having any control on statistics in Backup operation (default).
Oracle gives control to copy / not copy statistics in backup / restore. And it’s major issue when you restore a DB from low configuration system to high configuration system. It continues to behave as on low configuration.
This is the only reason; I was searching hard to get evidence on SQL BOL (I can't do it on my system).
p.s. After this post the first thought that you guys might have in your mind 'This forum is not for you Dev, it's SQL Server Central.'
November 23, 2011 at 9:57 am
Dev (11/23/2011)
Oracle gives control to copy / not copy statistics in backup / restore.
Not true.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 23, 2011 at 10:21 am
PaulB-TheOneAndOnly (11/23/2011)
Dev (11/23/2011)
Oracle gives control to copy / not copy statistics in backup / restore.Not true.
You mean "logical" backups (imp/exp), I suppose.
-- Gianluca Sartori
November 23, 2011 at 10:24 am
Note: Not relevant to SQL Server.
Export utility
STATISTICS
Default: ESTIMATE
Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE. See the Import parameter STATISTICS and Importing Statistics.
http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch01.htm
November 23, 2011 at 10:35 am
Dev (11/23/2011)
Note: Not relevant to SQL Server.Export utility
STATISTICS
Default: ESTIMATE
Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE. See the Import parameter STATISTICS and Importing Statistics.
http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch01.htm
As an Oracle DBA you know export and expdp are NOT a backup tool.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 23, 2011 at 11:32 pm
PaulB-TheOneAndOnly (11/23/2011)
Dev (11/23/2011)
Note: Not relevant to SQL Server.Export utility
STATISTICS
Default: ESTIMATE
Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE. See the Import parameter STATISTICS and Importing Statistics.
http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch01.htm
As an Oracle DBA you know export and expdp are NOT a backup tool.
Not Exactly. EXP is more than export utility. Schema Backup, Tablespace backup, Compression, binary dump files are few features that make it lot powerful. There are difficult workarounds in SQL Server to do the same tasks. It’s another point of difference in SQL Server. Gianluca used the proper term for EXP i.e. Logical Backups.
I don’t like when threads divert from the subject. The worst part here is I am responsible this time. I shouldn’t have used the word ‘Oracle’ at the first place. I just wanted you know the reason of my misunderstanding on term ‘statistics’ which is crystal clear now.
@Gianluca: there is a thread that needs your attention 😉
http://www.sqlservercentral.com/Forums/Topic1209949-61-1.aspx
November 24, 2011 at 12:44 am
Any specific reason you want statistics included in backup? If so, please update statistics and take backup, otherwise, you can do either option.
While taking backup, nothing is updated, Backup operation will take the database as it is.
If you find that, the statistics are outdated and want to backup with updated statistics, then go for it. You can use maintenance plan for making a sequence of activity and you can accomplish it in one plan.
-Johnson
November 24, 2011 at 4:48 am
We are converting our Database from ORACLE to SQL Server and I was to verify how it should be done in SQL server for best practices. I appreciate all of the feedback. Thanks.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply