p*********r
发帖数: 1440 | 1 A SQL server database, has more than 10K tables. Current size is about 800G,
after data compression. ETL process happens every night. Most of them get
updated data daily, some of them get updated data weekly, and the rest get
updated data monthly. The current maintenance plan is like this:
1. Sunday - Thursday, differential backup, including data integrity check.
2. Friday, rebuild index. As of last Friday, it took 18 hours to finish
3. Saturday, full database backup, including data integrity check and update
statistics - column only. As of last Saturday, it took 24 hours to finish:
5 hours data integrity check, 15 hours update statistics, and 4 hours full
database backup.
Is there a way I can improve the plan performance?
Thanks in advance. | i****a
发帖数: 36252 | 2 What version of sql server?
[发表自未名空间手机版 - m.mitbbs.com] | p*********r
发帖数: 1440 | 3 2008 Enterprise.
【在 i****a 的大作中提到】
: What version of sql server?
: [发表自未名空间手机版 - m.mitbbs.com]
| s**********o
发帖数: 14359 | 4 OLAP还做什么DIFF BACKUP，你的ETL JOB估计都RUN不完，不知道你的UPDATE DAILY
WEEKLY MONTHLY是指什么，注意DATABASE要用SIMPLE RECOVERY MODEL，关键还是看你
的ETL JOB怎么写的，如果去OLAP 做UPDATE大量数据，效果会很差。 | p*********r
发帖数: 1440 | 5 because people create store procedures and views day by day. and most of
them don't keep the scripts themselves.
btw, the database is in simple recovery model. that is why no transaction
backup. ETL jobs are developed by vendor.
【在 s**********o 的大作中提到】
: OLAP还做什么DIFF BACKUP，你的ETL JOB估计都RUN不完，不知道你的UPDATE DAILY
: WEEKLY MONTHLY是指什么，注意DATABASE要用SIMPLE RECOVERY MODEL，关键还是看你
: 的ETL JOB怎么写的，如果去OLAP 做UPDATE大量数据，效果会很差。
| s**********o
发帖数: 14359 | 6 就说VEDNOR的东西要看着，人哪管你有800G的东西，开发完了事。你不是PRODUCTION
SERVER么，怎么会有
people create store procedures and views day by day，那不就是DBA在RELEASE，
弄个SCRIPT JOB BACKUP一下SCHEMA就完了，DIFF BACKUP估计会越来越长，会跟ETL
JOB冲突。
【在 p*********r 的大作中提到】
: because people create store procedures and views day by day. and most of
: them don't keep the scripts themselves.
: btw, the database is in simple recovery model. that is why no transaction
: backup. ETL jobs are developed by vendor.
| i****a
发帖数: 36252 | 7 can see if table partitioning helps you
【在 p*********r 的大作中提到】
: 2008 Enterprise.
| p*********r
发帖数: 1440 | 8 Have not tried that one yet. Thanks.
Oh,there are 8 data files in the primary filegroup, each resides on a
different LUN.
【在 i****a 的大作中提到】
: can see if table partitioning helps you
| w*******e
发帖数: 1622 | 9 So, you use the "maintenance plan" to do all these jobs?
You should avoid to use SQL Server maintenance plan but uee your own script
to rebuild indexes and update stats.
800G,
get
get
update
【在 p*********r 的大作中提到】
: A SQL server database, has more than 10K tables. Current size is about 800G,
: after data compression. ETL process happens every night. Most of them get
: updated data daily, some of them get updated data weekly, and the rest get
: updated data monthly. The current maintenance plan is like this:
: 1. Sunday - Thursday, differential backup, including data integrity check.
: 2. Friday, rebuild index. As of last Friday, it took 18 hours to finish
: 3. Saturday, full database backup, including data integrity check and update
: statistics - column only. As of last Saturday, it took 24 hours to finish:
: 5 hours data integrity check, 15 hours update statistics, and 4 hours full
: database backup.
|
|
