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.
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
Subscribe to SQL Server Magazine:
http://www.sqlmag.com/rd.cfm?code=00eu215xsL
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
http://ad.doubleclick.net/clk;13383911;8214395;i?http://www.lumigent.com/go/hl18
ALERT: SQL Injection Attacks via Port 80 and 443!
http://ad.doubleclick.net/clk;13383950;8214395;l?https://download.spidynamics.com/1/ad/sql.asp?cs1_ContSupRef=I-N-sql1.20.05
Instant Recovery and Data Protection for SQL Servers
http://ad.doubleclick.net/clk;13384011;8214395;a?http://www.windowsitpro.com/whitepapers/NSISoftware/SQLServerProtection/index.cfm?code=012005SQL_HS
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:
http://ad.doubleclick.net/clk;13383911;8214395;i?http://www.lumigent.com/go/hl18
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.
http://www.sqlmag.com/rd.cfm?code=00ep214xeb
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.
http://email.winnetmag.com/winnetmag/winnetmag_prefctr.asp
View the SQL Server Magazine Privacy Policy.
http://www.winnetmag.com/aboutus/index.cfm?action=privacy
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.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
