One evening last week I sat down with Kimberly for 5 minutes to come up with a top-of-our heads list of VLDB maintenance concerns for a company migrating a multi-TB database to SQL Server 2005. This isn't in any way based on the VLDB survey I've been doing (see previous posts) but is a common-sense list of things that everyone should do. People really liked the list so I'm posting it here. Maybe we should turn this into a book???
Hope this helps.
This Blog ROCKS !
Any caveats on using DBCC Shrinkfile for tran log files (in Simple recovery mode) ?
Thanks! No issues wuth using shrinkfile on the log - let me know if you're having problems.
Sorry to react a little late on this one. When you say "If you defrag instead of rebuild, make sure you manually update stats", does it means that the stats are not recomputed after a ALTER INDEX REORGANIZE ?
In a way, it makes sense compared to REBUILD, as long as the index is not rebuilt with REORGANIZE, but I didn't find this information anywhere neither on MSDN nor SQLBOLs. To me, the impact is so huge it should be mentionned on top of the reorganize topics in the docs.
Along with KTS, I think your blog really rocks. I cant wait until the next post ;-)
Correct - stats are not recomputed as part of a REORGANIZE (or DBCC INDEXDEFRAG) operation. This is because the command does not see an overall snapshot of the index - only the few pages its working on at a time. This means that as soon as its through with some pages, the value distributions coul change dramatically.
Interesting - I was sure I'd put it into BOL for ALTER INDEX. I'll make sure its there in future.
We have some 200+GB databases and your "best practice" lists really would help us. I have a few questions and hoping you have time to answer..
-Logical fragmentation only affects read-ahead performance?
Do you mean Ordered Scan or in Execution Plan - Ordered: True
-Low page density affects IO throughput and memory usage?
-Low page density could be a sign of page-splits, so investigate the cause ?
Do you mean low Fillfactor value when you refer to low page density?
-If you defrag instead of rebuild, make sure you manually update stats?
So stats are recomputed as part of Rebuild in another word..
-Be wary of doing large index maintenance jobs if you use log shipping or DBM?
-They contribute to large log backups?
-Index rebuilds are always full-logged when DBM is present?
what is DBM? Database Maintenance? Index Defrag fully logged too?
-Don’t have multiple log files – no need ?
Do you mean no need to add addtional log file?
--Don’t let auto-grow go nuts – causes VLF fragmentation which leads to horrible perf
--Don’t rely on auto-grow
--Pro-actively manage file growth but have auto-grow on as a safeguard
why causes frag when auto grow db option is on. do you have suggestion on pro-actively manage file growth..?
Thank you very much for taking your time to answer these questions...
Bill - please send me an email with these questions in. Thanks
Thanks..I sent the mail to you this morning.
转载、解释一下来自SQL Server Storage Team的VLDB维护建议清单。 虽然主题是维护，但相信理解这些对于从事数据库开发的人员来说也有一定价值。