Very Large Database (VLDB) and SQL Server: Raising the Bar
Published: March 26, 2003
Please note:Portions of this transcript have been edited for clarity
Hosts:
- Kurt Windisch, director of program development for PASS
- John H. Miller, Program Manager, SQL Server
- Richard Waymire, Program Manager for SQL Server Management Tool
- Stuart Ozer, member of the Customer Advisory Team in the SQL product group
- Patrick Conlan, Program Manager in the Project team
- Stephen Dybing, SQL Server Communities PM
Moderator: Stephen (Microsoft)
Welcome to today's Very Large Database (VLDB) and SQL Server: Raising the Bar chat. I will ask the hosts to introduce themselves.
Host: John (Microsoft)
John H. Miller - Program Manager, SQL Server - BI Practices & Strategy Team
Host: Richard (Microsoft)
Hi, I'm Richard Waymire. I'm the Group Program Manager for SQL Server Management Tools.
Host: Stuart (Microsoft)
I'm Stuart Ozer, member of the Customer Advisory Team in the SQL product group, frequently working in the field with large-scale deployments.
Host: Kurt (PASS)
Hi, I'm Kurt Windisch, PASS Director of Program Development. Thanks for joining us today!
Host: Patrick (Microsoft)
Patrick Conlan, a Program Manager in the Project team, and have been playing with databases since before they were relational....
Moderator: Stephen (Microsoft)
And... I am Stephen Dybing, SQL Server MVP Lead. Glad you all could make it today!
Moderator: Stephen (Microsoft)
The bottom window is where you can enter questions for our hosts today. We will read them and select questions to answer. The questions and answers will be posted in the upper window.
Let's get started! Please fire away with your questions for our hosts.
Host: Stuart (Microsoft)
Q: What is considered VLDB today?
A: 500 GB of data or larger.
Host: Richard (Microsoft)
Q: What's the best method for determining columns to index in my VLDB? (Assuming you'd consider my DB VL...)
A: Your best bet is to know your app design and look for join keys/columns and also to run the Index Tuning Wizard. There are a couple of great papers on MSDN about Indexing strategies as well: http://msdn.microsoft.com.
Host: Patrick (Microsoft)
Q: How do you recommend using filegroups to separate data and indexes if your storage is SAN, so you don't have control over "true" physical placement. Still a good idea to separate?
A: Work with your SAN engineer to break off some i/o bandwidth for your logs, you'll see benefit.
Host: Stuart (Microsoft)
Q: At what db size do you see using filegroups an advantage?
A: It's less a matter of size than manageability. You may use separate filegroups to stage filegroup backup, or to avoid mixing extents between tables. But the key design issue is to allocate multiple files per filegroup to allow you to span multiple volumes on a SAN. Multiple filegroups per se is less important.
Host: Patrick (Microsoft)
Q: How do you suggest partitioning logical drives across a number of raid sets?
A: Work two angles - performance & resilience to single drive failure.
Host: Richard (Microsoft)
Q: What are MS's guidelines for choosing the number and location of log files for a VLDB?
A: The number of log files isn't too critical since we'll write to them sequentially. So your best bet is to get a REALLY fast drive/set of striped drives that are optimized for write activity. This should probably be on its own separate controller and drive array for optimal performance.
Host: Stuart (Microsoft)
Q: For the interest of speed, would you recommend DBCC INDEXFRAG or CREATE INDEX...WITH DROP_EXISTING?
A: The key difference is that INDEXDEFRAG is an ONLINE operation and requires no additional storage space. It is slow but not intrusive CREATE INDEX.. DROP EXISTING still requires 2x space and blocks other query activity.
Host: Richard (Microsoft)
Q: On partitioning Swap file, SQL Log file, and data where there is only one Raid 1 and a Raid 5 (2 HDs + 3 HDs). Do you still recommend leaving the SQL Log on Raid 5 with the Data, or move it to Raid one with the system partition?
A: Seems like your first bet would be to buy separate drives for the log....
Host: Patrick (Microsoft)
Q: Building test environments becomes quite unwieldly when working with VLDB. Any recommendations to mitigate this problem?
A: Yes, the dba can test query plans without executing them; otherwise ensure that your test systems are sized as big as you can manage.
Host: Stuart (Microsoft)
Q: We use the BULK INSERT command to load millions of rows from large text files into our staging tables. Would it be faster to use DTS than the BULK INSERT command? If neither, can you reccomend a better option?
A: Bulk Insert should be fastest. Be sure to use the TABLOCK hint and, if possible, use multiple load threads in parallel directed to the same table especially if there are no indexes.
Host: Richard (Microsoft)
Q: At what point (data size) is migration to a SAN providing benefits over local storage? Or is it a question of I/Os?
A: I think your first consideration should be recovery strategies (extended mirroring, remote snapshots) rather than any particular data size.
Host: John (Microsoft)
Q: When is federation and scaling out a better choice than scaling up?
A: Scaling out is best suited for OLTP situations where your data lends itself to a partitoning strategy.Scale out is not something generally recommended for DWHS VLDB's. Query optimization challenges make doing joins that cross multiple servers problematic.
Host: Kurt (PASS)
Q: What is SAN?
A: SAN stands for storage area network. It is a high-speed network of shared storage devices that can be made available to all servers on a LAN or WAN.
Host: Richard (Microsoft)
Q: What if separate Drives are not an option at the moment?
A: Then you're probably best putting the log on the mirrored drive with your system partition but make sure you limit the size so it doesn't take down the system.
Host: Patrick (Microsoft)
Q: For the interest of speed, would you recommend DBCC INDEXFRAG or CREATE INDEX...WITH DROP_EXISTING?
A: Depends on the degree of fragmentation - if not much then DBCC INDEXDEFRAG, if lots then CREATE INDEX, see the link for more info: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Host: Richard (Microsoft)
Q: We've got replication defined for certain tables in a DB. When we add new, we need to stop and then restart replication. Is there any way to break up the files in the snapshot so we don't need one huge disk to restart?
A: Not sure I understand what you're doing that's causing you to stop replication. Add new what?
Host: Richard (Microsoft)
Q: As a developer, what can I do to make my application cluster-friendly?
A: Put restart/retry logic in your app (i.e. assume that your app may lose connectivity and have to restart its last transaction). That's the big thing.
Host: Patrick (Microsoft)
Q: Are there plans to allow dynamic drives in clusters? This is to allow dbs to grow on a SAN if we need to expand the base partition.
A: With Windows Server 2003 you get clusterable mountpoints that helps somewhat. As regard SAN extensibility it is vendor dependant as to what is supported.
Host: Richard (Microsoft)
Q: Can you expand upon the reasoning or provide a ref? (extended mirroring, remote snapshots)
A: Your best bet is to read the hardware literature of the vendors (EMC, HP, Hitachi, IBM, etc.) and also there are some papers on http://www.microsoft.com/technet...
Host: Richard (Microsoft)
Q: On the replication thing, are there plans to allow schema changes to propagate through replication?
A: That's already in with some restrictions in SQL Server 2000.
Host: Stuart (Microsoft)
Q: How best to handle update stats on VLDB if OLTP and update stats can block processes?
A: Depends on the workload. For example if you perform mass overnight updates, may be best to disable autostats and update stats manually as part of load cycle. For tables that change significantly at all times, autostats should be active, but you can schedule stats update at off hours for slowly-changing data.
Host: Patrick (Microsoft)
Q: In high volume OLTP environments, indexes get fragmented quickly. De-fragging mitigatesthe problem quite a bit, but re-indexing is still required. Are their any index/maintenance strategies that can further mitigate this problem?
A: Yes, obviously fillfactor & pad_index are your friend here (at the expense of space and scan density).
Host: Richard (Microsoft)
Q: We are looking at ways to maintain a copy of our database in a different office. Ideally we would like it updated every hour. What do you recommend for VLDBs?
A: Look at http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx for lots of info.
Host: John (Microsoft)
Q: What are the factors to consider when implementing backup strategy for VLDB?
A: Depends on many factors and nature of your DB. OLTP vs. DWHS.
For OLTP, impact on performance, uptime requirements, ability to partition data, old data purge strategy, etc. impact your planning.
For DWHS factors like data load frequency, load window, uptime requirements, how much of the data changes, partitioning fact tables, etc. impact the decisions. You may want to look at Inside SQL Server 2000 and SQL Server 2000 Resource Guide for specific recommendations.
Host: Stuart (Microsoft)
Q: We currently run update nightly, but I am concerned when the tables get too big.
A: With big tables, a smaller sample % may speed the operation. No plans soon for eliminating blocking.
Host: Patrick (Microsoft)
Q: Add new tables to our definition of replication. We are not replicating the entire database, just specific tables.
A: You can add articles to your publication.
Host: Richard (Microsoft)
Q: Any known plans to make update stats not have to block?
A: We're certainly looking at that for the next release.
Host: Stuart (Microsoft)
Q: For very large loads, does it make sense to distribute the DTS processes or packages to multiple clients talking to one server...
A: It depends on how much transformation processing the DTS package itself performs, compared to the insert operation itself. For tasks such as Bulk Insert, its best to keep them on the server. As rule of thumb, don't deploy more insert streams than processors.
Host: Richard (Microsoft)
Q: Are SAN snapshot backups considered a full database backup? If not, are log backups still needed?
A: If you use supported hardware it knows how to make this happen automatically. And log backups are always a good idea for other reasons - such as point in time recovery...
Host: Patrick (Microsoft)
Q: In future versions of SQL, will it be possible to add bit columns to composite indexes (I'm trying to get away from bookmark lookups)
A: You can already do this in SQL Server 2000 - the docs are wrong...
Host: Patrick (Microsoft)
Q: On the replication thing, are there plans to allow schema changes to propagate through replication?
A: You can already do this in SQL Server 2000 - take a look at sp_repladdcolumn.
Host: Richard (Microsoft)
Q: Prior to SQL2k SP3, we have work-around to grant certain developers to view Agent Jobs. SP3 specifically DENY some sys stored procs in TargetServerRoles. Is MS going to provide a feature letting non-SAs to view jobs in future versions?
A: Yes, we're looking at how best to address this in the next version...
Moderator: Stephen (Microsoft)
Q: Are there any strategies (beyond expensive hardware and network solutions) that can reduce latency in log shipping?
A: If you're asking how to move the data quicker without investing in your hardware or your network, the answer would be that we're not aware of a way.
Host: Richard (Microsoft)
Q: Have been asking for better statistics on indexes to Richard Waymire at TechEd. Has the data slices gone beyond 200 rows or 1x8k page. It is very important to have accurate stats (or query estimates) for tables over 1 billion rows. ad-hoc
A: I've passed your feedback along to the query processing team...
Host: Stuart (Microsoft)
Q: What's the largest in use database at MS? Elsewhere? Not something like Terraserver that's more of a demo, but a database that is being used for business. Be nice to hear about OLTP and DW
A: Largest single instance I've worked with is > 10TB (mainly DW) at a large telco managing customer service / billing history. Inside MS we use 2TB+ SAP for OLTP workload.
Host: Patrick (Microsoft)
Q: Transactional Replication - Is there a way to add articles to your publication without having to redefine your publication?
A: Yes, take a look in Books Online/Index and keyword: articles, modifying.
Host: John (Microsoft)
Q: With a VLDB using a SAN is it actually necessary to break the database into separate disk partitions on the SAN? Would you only need to consider partitioning the data because of memory and cpu bottlenecks rather than disk?
A: Always best to work with your hardware vendor. We often do see customers compliment HDWR segregation capabilities with software segregation. Advantage gained is they have finer grained control over placement of data and ability to control contiguous nature of data/indexes. See http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.doc for discussion of this.
Host: Patrick (Microsoft)
Q: What method do you recommend for determining the optimal block size for both the log and data files?
A: Block size = NTFS cluster size? Depends somewhat on the hardware, but a good setting for log is 4K, data is a multiple of 8K - 64K is a nice number
Host: Richard (Microsoft)
Q: To Richard Weymire: Better stats for Yukon. Is the answer Yes or No?
A: This conversation is not under NDA and we're not discussing Yukon features publicly at this point.
Host: Richard (Microsoft)
Q: What is the formal procedure for being on the Yukon alpha/beta before public beta. We have ULDB's with terra terra bytes inf each one.
A: Mail me your contact info and I'll pass it along to the beta coordinator - Name, Address, company, phone, email address. rwaymi@microsoft.com
Host: Patrick (Microsoft)
Q: What do you recommend as a starting point for fillfactor & pad_index for OLTP?
A: Very dependent on a lot of factors; our default kind of assumes you'll never update the data, I'd go with around fillfactor 75-80 to start and monitor fragmentation and adjust as required.
Host: Richard (Microsoft)
Q: I have a problem that is currently with the SQL dev tea regarding costing of complex stored procedures in SQL Server (they time out on a SMP environment). The way the sql is written is as a single large select rather than writing it programmatically. Is this the right approach as the dbms gets larger or am I likely to get more problems of this nature?
A: If you're already working with product support/our dev team your best bet is to get the recommendation from them. It'll probably depend on more factors than we can go into in this chat...
Host: Stuart (Microsoft)
Q: What monitoring tools work better at MS for VLDB? Any 3rd party tools you like?
A: Of course perfmon to ensure that the workload is balanced between CPU and IO. We have a lot of internal use of MOM. Precise has a very nice offering and we have external customers with NetIQ and MS Operations Mgr (MOM).
Moderator: Stephen (Microsoft)
Q: Is there a complete hard-copy set of SQL Server 2000 reference manuals that corresponds to SQL Server books online? I have set for SQL Server 7, but cannot find at MS Press for SQL Server 2000. The SQL Server 7 set was not advertised well.
A: There isn't a complete hard-copy set of the SQL Server 2000 BOL. The most complete set available that I'm aware of is the Microsoft SQL Server 2000 Reference Library, see http://www.microsoft.com/mspress/books/5001.aspx.
Host: Patrick (Microsoft)
Q: Block size: Can anybody make a wild guess regarding I/O speed 8K NTFS vs 64K NTFS. For example: if 64K is 100% how much is 8K sector size (when everything else is the same)...10%, 50%, 95%?
A: Hardware dependent (eg SAN's tend to have fixed block sizes & CHS style alignment); but I'd think 99% assuming you don't hit the need to move the head assembly.
Host: Stuart (Microsoft)
Q: How are indexing strategies (defrag, fill factor, etc) affecting by using a uniqueidentifier as PK? Any recommendations for this datatype?
A: Probably best to use a nonclustered index for the PK if using Uniqueidentifier since fragmentation is guaranteed. (Clustered Index is best when used to support range-scans anyway) If possible consider bigints as alternative since they will be more efficient as a PK.
Host: John (Microsoft)
Q: How easy or difficult it is to convert SAP database from Oracle to SQL Server? Knowing that it is very large SAP implementation using R3 3.1I version.
A: Actually this is relatively easy, but is likely to require assistance/guidance from SAP or a qualified implementation partner. This has become a very popular subject recently as many companies today look to SQL as a way to reduce costs in this rather challenging economy.
Host: Richard (Microsoft)
Q: 8 way Liberty or 32 way SS2K/ Yukon? Better performance for VLDB.
A: Totally depends on your workload and whether you're memory or CPU dependent...
Host: Patrick (Microsoft)
Q: Ehen is yukon beta start?
A: We're not talking about Yukon dates publicly at the moment.
Host: Richard (Microsoft)
Q: way0utwest: Any expansions for bigint? superbigint?
A: This conversation is not under NDA and we're not discussing Yukon features publicly at this point.
Moderator: Stephen (Microsoft)
Q: Is there support for similar identity value generator like sequence # in oracle in YUKON? This is to support scaling out across servers?
A: This conversation is not under NDA and we're not discussing Yukon features publicly at this point.
Host: Patrick (Microsoft)
Q: DBCC INDEXDEFRAG - When we run this on all of our tables, the transaction log will fill up every time unless we set the log to truncate - sometimes that doesn't work. I don't want to set the log to auto grow. Are there long running trans. occurring?
A: DBCC INDEXDEFRAG isn't an atomic transaction but it does log its activity (as it is moving data around) so you could trigger an Agent job to backup the log when it gets above a certain size. Note that we don't recommend just truncating the log, it should be an important part of your recovery strategy.
Host: Richard (Microsoft)
Q: Any change of more versions or SQL? More al la carte of features? For example, we'd like to go to 8GB on some servers, but we don't need all the enterprise features. Or any other of them. Cost is starting to become an issue, especially compared to dB2.
A: Anything > 2GB is Enterprise Edition only. On the cost issue are you including all features (tools, etc.)? Also look at http://www.microsoft.com/sql/default.mspx
Host: Richard (Microsoft)
Q: YUKON - what can we tell you today?
A:http://www.microsoft.com/sql/default.mspx
Host: Patrick (Microsoft)
Q: Does log shipping in SQL Server 2000 allow for easy primary server role switching?
A: Yes, a lot easier - see http://support.microsoft.com/default.aspx?scid=kb;en-us;323135
Host: Stuart (Microsoft)
Q: DBCC Reindex on very large table always blocks the user transactions. Any good suggestion to work around in 7x24 environment?
A: DBCC ** INDEXDEFRAG ** will not block.
Host: John (Microsoft)
Q: How would you recommend configuring a "hands off" VLDB environment that had a large numbers of changes occuring to it (new sps,tables)?
A: VLDB & Hands-off. Sounds like an Oxymoron ;-) Seriously, most folks use scripts to automate common mangement tasks. May want to consider log shipping since it will capture DDL as well as DML.
Moderator: Stephen (Microsoft)
Thanks for joining us today! You've asked some great questions but unfortunately, it's time to go.
Host: Richard (Microsoft)
Thanks for joining the chat today!
Host: Patrick (Microsoft)
Hey - thanks for the great questions!
Host: Kurt (PASS)
Thanks everybody! Look for the transcript on the PASS and TechNet sites.
Host: John (Microsoft)
Thanks for your participation today!
For further information on this topic or about SQL Server, please visit the following:
Newsgroups: SQL Server Newsgroups
SQL Transcripts: Read the archiveof past SQL chats.
Website: Visit the Microsoft SQL Website