SQL Server Magazine UPDATE, January 21, 2005--Hardware-Resource Distribution

Brian Moran encourages readers to seek Zen-like balane int he distribution of hardware resources, learn about running Profiler on a busy server, get the results of our latest instant poll, and more!

ITPro Today

January 19, 2005

12 Min Read
ITPro Today logo

Subscribe to SQL Server Magazine:

Our domain name is changing! To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

This Issue Sponsored By

Free Disaster Recovery Toolkit for the SQL DBA

ALERT: SQL Injection Attacks via Port 80 and 443!

Instant Recovery and Data Protection for SQL Servers

January 21, 2005

1. Perspectives

  • Zen and the Art of Hardware-Resource Distribution

2. News and Views

  • Share Your Product Success and Win a T-Shirt!

  • Results of Previous Instant Poll: Spyware

  • New Instant Poll: RAID Storage

3. Announcements

  • Get SQL Server Info on Your Schedule

  • Get a Free Digital Issue of SQL Server Magazine

4. Peer to Peer

  • Hot Tip: Running Profiler on a Busy Server

  • Hot Article: Calling All COM Objects

  • Kevin Kline's "In a Nutshell" Blog: Streambase—Stonebreaker's Newest Venture

  • Hot Threads

5. New and Improved

  • Centralize Backup-Server Management

  • Manage Diverse Data-Integration Requirements

Sponsor: Free Disaster Recovery Toolkit for the SQL DBA
Written by SQL Server expert Brian Knight, this handy, "how-to" toolkit contains comprehensive first-hand advice and scripts for SQL Server DBAs that need to build and implement a successful disaster recovery plan. With his tips and quips, Brian walks the DBA through real-world scenarios using an easy, step-by-step approach. And as part of the download, you'll receive four scripts, which will greatly speed your recovery time! Download it today, compliments of Lumigent:


1. Perspectives





  • Zen and the Art of Hardware-Resource Distribution

  •     by Brian Moran, [email protected]


    Do you have four processors, 4GB of memory, and RAID 5 in a single internal array? This week is for you. Many SQL Server professionals reading this commentary have I/O problems that they don't know about. For space reasons, I don't typically write in depth about bits and bytes here, and it will be difficult to discuss I/O problems in detail, but I'll do my best to enlighten you given the space that I have.

    First, let me grossly simplify the definition of a database-system hardware bottleneck: It's the hardware layer (typically I/O, CPU, memory, or network) that has the greatest limiting effect on server throughput. According to this definition, all systems have a bottleneck because at any point in time, some component of your hardware system is the slowest. It might not be terribly slow in relation to your workload, but there's always a weakest link in the chain. In a perfect world the hardware components would be nicely balanced so that you don't have some fast components matched to comparatively slow components; such an unbalanced setup wastes money because you don't get the full benefit of the fast components. Unfortunately, hardware with mismatched performance characteristics is all too common, and I/O subsystems typically draw the short straw.

    I frequently see customers hinder SQL Server performance by using a few—maybe 4 or 5—physical disks on a run-of-the-mill internal RAID array on a box that has four (or maybe 8) fast CPUs and lots of memory. In this scenario, the customer will most likely create an I/O bottleneck because the performance characteristics of the disks can't keep up with the throughput capabilities of the faster CPU and memory. These customers might have been better off spending more money on I/O and less on other parts of the system to ensure a more balanced set of hardware.

    Many customers compound the problem by using a single RAID 5 array and placing their data and logs on that single array. It's a well-recognized best practice to separate data and logs to ensure maximum throughput. I'm not saying that RAID 5 is a bad solution—sometimes it's the most cost-effective way to meet your I/O needs when you have a fixed amount of money. However, contrary to popular belief, RAID 5 isn't the most efficient choice for either reads or writes. RAID 10 will invariably be faster and RAID 1 is often better. But RAID 10 costs a lot more than RAID 5, so many customers waste hardware money by matching excessive CPU and memory resources to an underpowered I/O subsystem and that's a shame. As I said, you often misuse money by under-spending on I/O and overspending on other areas of the system. Seek Zen like balance in the distribution of your hardware resources.

    If you answered yes to my opening question about having four processors and 4GB of memory running on a single RAID 5 array, then you've probably introduced an I/O bottleneck into your system. Don't panic yet. In many cases, the server—even with the mismatched I/O-is still powerful enough in relation to the workload that no user-perceived performance problems exist. But I/O problems are the most common hardware problems that I solve.

    I started this commentary by saying that it would be difficult to cover I/O problems in this space, and that's true. In some ways I've probably raised more questions than I've answered. I'm not going to attempt to provide a comprehensive list of resources to peruse or methodologies to consider. Spend five minutes Googling and you'll uncover weeks' worth of reading material to help you better understand the complex issues behind building an optimized I/O subsystem and measuring if you have an I/O bottleneck. I encourage you to seek information about DBCC SQLPERF(waitstats) and the fn_virtualfilestats() function, which are my favorite ways to find out whether a customer has an I/O problem. Become one with your hardware resources, seek out the bottlenecks, and start your homework.

    ALERT: SQL Injection Attacks via Port 80 and 443!It's as simple as placing additional SQL commands into a Web Form input box giving hackers complete access to your backend systems! Firewalls, Access Controls and IDS don't stop such attacks because SQL Injections are NOT seen as intruders. Download this *FREE* white paper from SPI Dynamics!    http://ad.doubleclick.net/clk;13383950;8214395;l?https://download.spidynamics.com/1/ad/sql.asp?cs1_ContSupRef=I-N-sql1.20.05


    2. News & Views




  • Share Your Product Success and Win a T-Shirt!

  • Have you used a product that saved you time or made your job easier? SQL Server Magazine is starting a new department featuring readers' favorite products. The Hands On page will let you share successes you've had with SQL Server-related products and services. Tell us your product-success story, and if we print your submission in the Hands On department, we'll send you a SQL Server Magazine t-shirt. Send your product suggestion with information about how the product helped you to products editor Dawn Cyr at [email protected]. Please put the words "Hands On" in the subject line.    [email protected]



  • Results of Previous Instant Poll: Spyware

  • "Do you use multiple tools to handle your spyware problems?" Here are the results from the 95 votes:


  • 64%  Yes

  • 21%  No, I use only one tool

  • 15%  No, I don't have a spyware problem


  • New Instant Poll: RAID Storage

  • "What kind of RAID storage do you use for SQL Server?" Go to the SQL Server Magazine home page ( http://www.sqlmag.com ) and submit your vote for


  • I don't use RAID

  • RAID 1

  • RAID 5

  • RAID 10

  • Other


3. Announcements





  • Get SQL Server Info on Your Schedule

  • Introducing the SQL Server Magazine Monthly Pass—the golden key for the SQL Server Magazine Web site. As a subscriber, you'll get immediate online access to every article ever published in the magazine for an entire month—that's thousands of how-to articles, expert tips, and quick answers for just $5.95. Order now:    http://ad.doubleclick.net/clk;13380352;6134867;h?http://www.windowsitpro.com/sub/MonthlyAccess/index.cfm?promocode=ep2151sp


    Get a Free Digital Issue of SQL Server MagazineNow is the time to try the #1 SQL Server resource—SQL Server Magazine. Whether you're looking for novice or advanced-level SQL Server information, our experts provide helpful answers relevant to every SQL Server user. Stay on top of such topics as Reporting Services, SQL Server 2005, security, and much more! Click here to get your free issue:    http://ad.doubleclick.net/clk;13380457;6134867;n?http://www.windowsitpro.com/SQLServer/Digital/index.cfm?code=supdate0120


    4. Peer to Peer




  • Hot Tip: Running Profiler on a Busy Server

  •      by Brian Moran, [email protected]


    Q. I tried to use SQL Server Profiler to tune a busy SQL Server database for one of my customers. The 8GB database was running on an 8-CPU server running at 70 percent to 80 percent CPU utilization. The server promptly became nonresponsive, and I had to restart the server. Microsoft Developer Network (MSDN) support told me that Profiler can place a heavy burden on busy SQL Servers. To tune the database, I need the SQL text and reads that the queries generate. How do I gather this information from a busy, multiprocessor SQL Server without running the server into the ground?

    Read the answer to this question today at    http://www.windowsitpro.com/sqlserver/article/articleid/44306/sqlserver_44306.html


  • Hot Article: Calling All COM Objects

  • SQL Server system stored procedures and extended stored procedures perform a variety of database-related functions. However, one important area that even extended stored procedures don't address is COM interoperability. Most of Microsoft's core products have a built-in COM-object model that enables integrated interoperability. In his January SELECT TOP(X) column, "Calling All COM Objects," Michael Otey tells you about the seven sp_OA stored procedures for OLE Automation that SQL Server provides. Read this article today at    http://www.windowsitpro.com/sqlserver/article/articleid/44630/sqlserver_44630.html


    In a Nutshell: Streambase—Stonebreaker's Newest VentureIn this week's blog, "Streambase—Stonebreaker's Newest Venture," Kevin Kline talks about Michael Stonebreaker's new database technology, StreamBase, which is especially geared toward analyzing huge streams of incoming data. Because the product works at orders-of-magnitude faster than traditional database platforms, and because it looks at the data streams without storing them to disk like traditional database platforms, StreamBase looks to do something no other platform is currently able to handle. Get an overview of the product and tell Kevin what you think of this new way to approach streaming data today at    http://www.windowsitpro.com/blog/index.cfm?action=BlogIndex&DepartmentID=965

    Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.    http://www.windowsitpro.com/sqlserver/forums/

    Administration: Unable to Read Local Event Log Error    http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1664&threadid=129128T-SQL: Extracting Lastname from Fullname Field    http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1690&threadid=129328Performance: Mystery SQL Server Performance Problem    http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1666&threadid=128862DTS: SQL Server 2000 DTS Package Fails    http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1672&threadid=116088General Discussion: Estimating Query-Execution Time    http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1674&threadid=129198Reporting Services: Reporting Services and Visual Studio    http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1741&threadid=129295


    Hot Spot



  • Instant Recovery and Data Protection for SQL Servers

  • Database management systems (DBMS) are the hidden engines behind some of your most critical information applications. In most cases, all of the data for these business systems is stored in a DBMS. Like most database management systems, SQL Server stores all of the data in a handful of database containers, or files. If one of these containers is damaged or corrupted, all of the data it contains is lost. This free white paper discusses data protection strategies for SQL Server and helps answer questions to prevent serious failures in your systems. Get answers now!    http://ad.doubleclick.net/clk;13384011;8214395;a?http://www.windowsitpro.com/whitepapers/NSISoftware/SQLServerProtection/index.cfm?code=012005SQL_HS



    5. New & Improved

         by Dawn Cyr, [email protected]



  • Centralize Backup-Server Management

  • VERITAS Software announced Backup Exec 10.0 for Windows Servers, software that lets you simplify and centralize management of your backup servers through a single console. As part of the company's new integrated Backup Exec Suite, Backup Exec now integrates with VERITAS Replication Exec 3.1, which lets you replicate data from remote offices to a central location without adding hardware, and VERITAS Storage Exec 5.3, which uses quota management and file blocking to improve backup and restore times and help users manage and clean up their own storage. The Backup Exec Suite provides small to midsized businesses with the means to use replication to protect data without disrupting normal server operations. In addition, customers can choose an Advanced Disk-Based Backup Option, which significantly speeds restore times. Advanced Disk-Based Backup employs two new features: synthetic backup and off-host backup. Synthetic backup combines incremental backups into a synthetic full backup, which speeds data restores with minimal network bandwidth. Off-host backup automatically shifts the backup of data snapshots to alternate storage systems. The latest release of Backup Exec also features simplified management features and expanded platform support for Linux and UNIX servers. Pricing of VERITAS products starts at $895 for Backup Exec 10.0, $1495 for Replicator Exec, and $795 for Storage Exec. The Backup Exec Suite, which includes all three products and one full year of technical support, starts at $4280. For more information, contact VERITAS at 800-327-2232, or 650-527-2908.     http://www.veritas.com



  • Manage Diverse Data-Integration Requirements

  • Sunopsis announced Sunopsis Integration Suite, a comprehensive data- and application-integration solution. The suite gives companies an easy way to access, integrate, and manage data from business-critical applications running on a range of platforms, including all SQL Server releases, Oracle, and IBM DB2. The solution comprises four products that you can purchase separately or as a suite. Sunopsis ETL provides batch data movement and lets you load data warehouses and replicate and migrate databases. The product generates native SQL code for each source and target relational database and orchestrates the execution of those statements by using an Extract, Load, and Transform (ELT) approach in which transformations are performed as close as possible to the data. Sunopsis ActiveData is a data-driven application-integration environment that lets you bidirectionally replicate all data from production applications into a centralized database called the Active Data Hub. The Active Data Hub is a persistent repository for enterprise data and propagates updates to the production applications. You can use the hub for multiple tasks, including loading your data warehouse and as a repository for Business Activity Monitoring (BAM). Sunopsis DataBus is an inter-application bus that provides event-oriented integration. The DataBus exchanges data between applications by transforming messages and routing them based on their content. Sunopsis DataServices exposes enterprise data as Web Services, building a Service Oriented Architecture (SOA) on any database, independent of the applications using the database. The complete Sunopsis Integration Suite will be available by summer 2005, and pricing for the full suite starts at $35,000. For other information or questions about product availability, contact Sunopsis at 888-740-0300, 781-238-1770, or [email protected].    http://www.sunopsis.com



    Contact Us




  • About the newsletter—[email protected]

  • About technical questions—http://www.sqlmag.com/forums

  • About product news—[email protected]

  • About your subscription—[email protected]

  • About sponsoring SQL Server Magazine UPDATE—Richard Resnick, [email protected]

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.

Manage Your Account You are subscribed as #EmailAddr#. To unsubscribe from this email newsletter, click here /#Mailing:UnsubEmail

To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.

View the SQL Server Magazine Privacy Policy.

SQL Server Magazine is a division of Penton Media, Inc., 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

Copyright 2004, Penton Media, Inc. All Rights Reserved.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like