VLDB Question - What are the main issues
-
I would like to pose a question and gather opinions. Many times when I see job postings, one of the requirements is experience with large databases. By today's standards, I've never had the opportunity to work on what would be considered a VLDB. Most of the postings cite > 250gb.
I would like to know what are some of the issues that come along with a VLDB that requires different techniques and problem solving experience. I know that with greater size comes issues you don't have to deal with when you have a database of say 30gb. For starters, I'm sure you have to adjust backups and their schedules, introduce differentials into the schema and that kind of thing. I'm thinking maintenance and reindexing has got to become somewhat of a scheduling issue as well.
Any and all opinions, thoughts and advice are appreciated. Thank you in advance.
Mark
-
Hi!
Fiund this on Microsoft site - certainly does address issues, unfortunately does not provide much towards the solutions
http://www.microsoft.com/technet/community/chats/trans/sql/sql0326.mspx
Hope it will be of use.
Dmytro
-
One issue that we are currently facing is performance. Our database is approximately 190 GB and some of the tables are pretty large. I am running defrags on the tables instead of reindexing, so the tables are tuned pretty well but the design could have been better. Archiving data is not allowed at this time so we cannot purge data.
My goal is to upgrade to SQL 2005 but I am waiting for vendor approval (it's a 3rd party application). I want to partition 3 or 4 of the largest tables and their indexes which will help speed up queries and reports.
Another thing we've had to do is purchase a database backup solution. We did this for 3 reasons: speed, disk space, & tape backups. Many backup products will compress backup files by 50% which saves on disk space. The smaller files can also be backed up or recovered more quickly from a tape backup. Speed is self-explanitory.
These are just a few of the things that we've come across. Hope this helps.
-
Here is what I have encountered in the past and feel is important to look at:
- Design - a balance between normalized and de-normalized data is needed.
- Table Partitioning - If it is not done correctly, it hurts more than helps.
- Indexing - tables need to be indexed properly (wrong columns, wrong column order or a clustered index where you could have used a non-clustered index, can be very big issues.
- Performance - Queries. Need I say more ?
- Bulk data loading - usually some online performance hits occur during this. Remember you probably have to reindex/defrag after this event.
- Data Archival - most data has a 'shelf life' - a bad archival strategy is almost as bad as no archival strategy. Again performance hits occur. Oh do not forget reindex/defrag afterwards as well.
- Backup - disk space is an issue, transaction logging also consumes lots of space too.
- Downtime - usually there is none or the window is so small nothing of merit can really be accomplished.
- Index Maintenance - try rebuilding a clustered index on a 50 GB table that is online.
- Overall Maintenance - large size means things take a lot longer - minutes turn into hours for many activities.
- Test/QA environment - there usually is NONE. Get used to working on 'hot' databases all of the time. If there is a Test/QA environment, it is usually only good for structure and syntax checking !
I have saved the most important points for last (these are attributes you will need in your personal toolbox):
- Clear head - maintaining focus, and planning, planning, planning.
- Calm demeanor - Ability to work under pressure - large size DB issues generate exponentially larger 'heat' in problem situations than smaller, or more normal size databases.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply