SQL Server Performance Forum – Threads Archive

VLDB management

Hi There I have an OLTP DB currently at 50Gig. In a short period of time this will grow to at least a terrabyte. I am already looking into partitioning etc for performance, however i am wondering about more administrative functions. For example currently i generate a 10-15Gig transaction log rebuilding indexes. TO backup, reindex and update stats at night currently takes 2.5 hours. What are the strategies and methods for VLDB maintenance , how am i going to reindex, backup etc a terrabyte database overnight ? In a nutshell i am looking for a link to a decent whitepaper or something when it comes to VLDB database management. I am not looking for partitioning or VLDB high availabilty more on how to run maintenance on a VLDB in realistic maintenance windows. ANy help or pointers would be greatly appreciated. Thanx

we have a 2.7TB db and we run indexes every satday. takes about 10-12 hours. we have auto-update stats turned off so SQL server doesnt jump in at peak production time and decide to do an update stats blocking the table. I am not sure if there is any white paper but you learn as you go. Some settings work for some environments and same settings work differently for a diff env. You have to know your DB, your jobs to fine tune. ***********************
Dinakar Nethi
SQL Server MVP
***********************

Hi ndinakar How do you backup the database ? DO you use SAN snap/clone technology ? How long does your backup take ? Thanx

Just a thought… AFAIK, is MS always interested in such case studies. Might be worth talking to your local MS office. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

We use SQL Lite. MS already uses our DB for a lot of their benchmark tests. ***********************
Dinakar Nethi
SQL Server MVP
***********************

VLDB is about having disks bandwidth,
avoid SAN which generally stink at sequential IO with proper disk configuration, backups can run at 2GB/sec, or 7TB/hr
verify the table scan and random IO performance per my 2005 HW thread SQL 2005 can do table scans at >1.4GB/sec on a 2 x Xeon 5150.
On the big HP Itanium systems, 12-15GB/sec are seen random IO rates in certain queries should target 20-40K for VLDB (50-100 disks)
note this is not a true full disk random IOP, but rather a pseudo random from certain sql queries

HI JOe Interesting you say that, the instance is currently on a SAN. I was reccomending that it be taken off the SAN, do you reccomend , local RAID sets for the instance? SOrry alot of your commetn goes over my head. I thought 2GB/sec was only acheived through SAN fabric but you say avoid SAN ? DO you mind clarifying for me? Thanx

Hi ndinakar. If i search fro SQL Lite on the net, i only find an oracle/mysql tool, exactly what sql lite are you refferring to? Is this in terms of your backups ?

i do not know why people think high performance and SAN go together tell a SAN vendor that you need to do a SQL Server backup at 2GB/sec,
see what he comes back with I think it will be a $400K+ solution
(or it will be when they finally figure out how to do it). you could do this with DA at about $25K run my scripts from the link below, adjust size to 128GB
see where your SAN ranks in my rating scheme http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16995

Hi Joe Thank YOu i will run these scripts.
So what solution do you reccomend?

I meant to say LiteSpeed from IMCEDA. ***********************
Dinakar Nethi
SQL Server MVP
***********************

My strong preference for Data warehouse servers is direct attach storage
third party is not required
if you have a Dell server, 1st choice is the PowerVault MD1000 SAS storage system, 2nd choice is PV220 SCSI
if HP, again 1st choice SAS storage MSA50 ?, 2nd, one of the SCSI
and so on, important point,
1. set goals for random and sequential performance
2. get the vendor to provide technical expertise (since so few local reps have this type of expertise, they may need to go to the home office)
3. determine whether the vendor is good or bs
4. require the vendor guarantee and deliver the performance goal before finalizing the purchase

Thanx A million for your input Joe and ndinakar, i will be meeting with the Dell vendor soon. Cheers

]]>