SQL Server Magazine UPDATE, May 19, 2005--Performance Tuning and Scalability

Subscribe to SQL Server Magazine:
    http://lists.sqlmag.com/t?ctl=9C48:2621E7

Our email address has changed! 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 Issue Sponsored By 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.

Free Disaster Recovery Toolkit for the SQL DBA
     http://lists.sqlmag.com/t?ctl=70F5:2621E7

Learn SQL Server...5 courses, 36 CDs, JUST $995!
    http://lists.sqlmag.com/t?ctl=A5D6:2621E7

Instant Recovery and Data Protection for SQL Servers
    http://lists.sqlmag.com/t?ctl=A5D7:2621E7

May 19, 2005

1. Perspectives

2. News & Views

3. Events and Resources

4. Featured White Paper

5. Peer to Peer

6. Announcements

7. New & Improved

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://lists.sqlmag.com/t?ctl=70F5:2621E7

1. Perspectives

    by Brian Moran, [email protected]

Extreme performance is a term that a colleague and I coined 5 years ago to define the SQL Server performance-tuning approach that we recommended to our consulting customers. Understanding and implementing the extreme-performance philosophy might help you avoid performance-tuning mistakes down the road. One area in particular that our philosophy is helpful is in planning for scalability. Planning with scalability in mind lets you improve performance now and will help you take advantage of emerging technology, such as the Common Language Runtime (CLR) integration in SQL Server 2005.

First, be aware that everything you do with an application today needs to anticipate someone pushing your code beyond what you intended it to do in the future. It's easier to design an efficient application than do triage on a poorly performing application after deployment. You need to test your application with reasonably sized data sets and take adequate steps to ensure that your application can expand as needed. We all need to design systems with performance and scalability in mind.

There are two basic approaches to scalability: scale up or scale out. To scale up, you beef up a single SMP box. To scale out, you increase horsepower by adding new boxes to the system. Scaling out is easier than scaling up because it doesn't require you to anticipate future power needs; you simply buy another box when the time comes. And scaling out your Web farm is easier than scaling out the database layer. So how do you think about scalability when you're designing a system for extreme performance to meet unpredictable future demands? Deploy a component on the middle tier of a Web farm rather than as a stored procedure, even if the throughput is better when you deploy the component as a stored procedure. That approach might seem counterintuitive, but here's why it makes sense: You can easily add another box to your Web farm if you hit the edge of your scalability envelope. Scaling out the database layer can be difficult and expensive, especially when you didn't design the application with scaling out in mind.

Thinking about designing for scalability brings to mind a common dilemma. I've seen customers encounter significant back-end database performance decreases that require substantial code rewriting--a difficult and painful process--when they could have solved the problem by adding a commodity-priced Web server to the farm if they'd designed certain expensive stored procedures as middle-tier components. Sure, initial throughput might be better if they'd deployed the component as a procedure, but sometimes you need to sacrifice a small short-range performance gain for long-range performance and scalability requirements. Enter the CLR...

What if you didn't have to sacrifice short-term response time for long-term scalability? What if you could cram as much processing onto the database tier as possible, then easily re-deploy certain processes to a mid-tier application server when your database began to run out of CPU steam? What if the tradeoffs between scalability and response time became a runtime deployment issue rather than a fixed-in-stone design issue? That would be cool! Theoretically, the CLR gives us that option. We could deploy .NET-based code in the CLR to reduce round trips and keep data-oriented code near the data. But, if the code was carefully architected we might later be able to re-deploy the code on the application tier if the database server starts to become resource constrained. Yes, response time for transactions might go up a bit if that happens, but presumably we could achieve greater concurrency and throughput. The CLR also makes it easy for us to overburden our database server by pushing logic to SQL Server to cut down on round trips (or whatever reason you might come up with). But don't forget: scaling the Web tier is easy, whereas scaling the database often requires throwing your existing box away in favor of a new box. Indiscriminate deployment of code to the CLR is a recipe for an overburdened SQL Server and disaster. Carefully architected solutions that let us deploy in the CLR but readily re-deploy in a CLR hosted somewhere else might provide the ability to focus on response time in the short term and scalable throughput in the long term.

Undoubtedly, it will take some time to work out the best practices and architectural recommendations that let customers design systems in this way, but the benefits could be huge. I love when I get to have my cake and eat it, too!

Learn SQL Server...5 courses, 36 CDs, JUST $995!
For a limited time, get this unbelievable offer on our AWARD-WINNING SQL Server 2000 training...5 courses (covering SQL, OLAP, DTS, XML, and ADO.NET) on 36 CDs, plus hands-on labs, sample code, printable courseware, pre/post exams...all for JUST $995--our lowest price ever! Order now at
    http://lists.sqlmag.com/t?ctl=A5D6:2621E7

2. News & Views

"When do you plan to see Star Wars: Episode 3--Revenge of the Sith?" Here are the results from the 170 votes (deviations from 100 are due to a rounding error):

"Have you deployed SQL Server 2000 Service Pack 4 (SP4)?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=3D1:2621E7 ) and submit your vote for

3. Events and Resources

View this on-demand Web seminar and receive a complimentary 30-day software evaluation and industry white paper! Join industry expert David Chernicoff and learn how leading organizations are incorporating fax technologies to empower users and enhance existing investments in infrastructure and applications while providing substantial ROI. Register now!
    http://lists.sqlmag.com/t?ctl=A5D8:2621E7

In this free Web seminar, explore the new features associated with Microsoft SQL Server 2005 Reporting Services. You'll discover how to offer the "single version of truth" in your enterprise-reporting environment with the integration of Reporting Services 2005 and the Analysis Service 2005 Unified Dimensional Model (UDM). Plus, you'll discover "Report Builder," and more. Sign up today!
    http://lists.sqlmag.com/t?ctl=A5D9:2621E7

In this free Web seminar, get an in-depth understanding of the many new features and capabilities Microsoft has introduced in SQL Server 2005 Analysis Services. You'll learn about data source views, user-defined hierarchies, measure groups, KPIs and more! Plus--get all you need to know about integration with Integration Services and Reporting Services and the new deployment and synchronization capabilities in SQL Server 2005 Analysis Services.
    http://lists.sqlmag.com/t?ctl=A5DA:2621E7

Get the facts about migrating to SQL Server 2005. SQL Server experts will present real-world information about administration, development, and business intelligence to help you implement a best-practices migration to SQL Server 2005 and improve your database-computing environment. Receive a 1-year membership to PASS and 1-year subscription to SQL Server Magazine. Register now!     http://lists.sqlmag.com/t?ctl=A5DB:2621E7

See the complete Windows IT Pro Network guide to Web and live events.     http://lists.sqlmag.com/t?ctl=5778:2621E7

4. Featured White Paper

In this free white paper, learn how your small or midsized business can optimize disk-based backup. Discover how combining disk-based backups with automated backup technology can deliver easy-to-manage backups, fast restores, and simplified creation and tracking of tape for offsite media rotation. Download this free white paper today!
     http://lists.sqlmag.com/t?ctl=A5DC:2621E7

5. Peer to Peer

Hot Tip: Performance Metrics in SQL Server Stop Working When You Stop SQL Server by Brian Moran, [email protected]

I was diagnosing performance problems for a customer when several performance-related counters suddenly stopped working. The affected information included all SQL Server Performance Monitor counters, wait-type information from sysprocesses, and all I/O-related activity from the fn_virtualfilestats() function. I was confused until I ran across the Microsoft article "FIX: 'Performance monitor shared memory setup failed: -1' error message when you start SQL Server" at http://lists.sqlmag.com/t?ctl=A5DD:2621E7 . The article discusses a bug in SQL Server 2000 Service Pack 3 (SP3) that causes you to lose Performance Monitor counters if System Monitor is running when you stop SQL Server.

Read the full tip today at
    http://lists.sqlmag.com/t?ctl=A5DE:2621E7

Microsoft has announced four editions and pricing plans for SQL Server 2005. The company will offer three license plans for SQL Server 2005: processor licenses, server plus device Client Access Licenses (CALs), and server plus user CALs. Processor licenses require a separate license for each processor running SQL Server. You'll pay for a fixed server license plus additional CAL licenses for each device or user that connects to SQL Server under the server plus device CAL and server plus user CAL, respectively. In his May SELECT TOP(X) column "SQL Server 2005 Editions," Michael Otey covers the four editions that Microsoft will release for SQL Server 2005. Note that all higher editions include the same functionality as the editions below it. Read this article today at
    http://lists.sqlmag.com/t?ctl=8448:2621E7

Just in case you missed the news, SQL Server 2000 Service Pack 4 (SP4) shipped May 5. You can download SP4 at http://lists.sqlmag.com/t?ctl=9C4C:2621E7 . There's also a caveat from Microsoft: "Microsoft has found an issue with the final build of SP4 that impacts customers who run SQL Server with AWE support enabled. This issue only impacts machines with more than 2GB of memory where AWE has been explicitly enabled. Customers with this configuration should not install SP4. Microsoft is currently working on the problem and will issue an update soon." As I've tried to educate myself more about SQL Server 2005, one area that seems more gray than black and white is XQuery. From what I've gathered, XQuery is a sort of XML/SQL hybrid. There's a cool interview with Michael Rys at http://lists.sqlmag.com/t?ctl=A5DF:2621E7 . Rys is the program manager for Microsoft's SQL Server Engine team and has final say on all things XML, XQuery, etc. You can also read Rys's blog at http://lists.sqlmag.com/t?ctl=A5E0:2621E7 . It has lots of juicy tidbits as well. Let Kevin know what you think today at
    http://lists.sqlmag.com/t?ctl=A5E1:2621E7

Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.
    http://lists.sqlmag.com/t?ctl=3D8:2621E7

Administration: Any Problems With SP4?
    http://lists.sqlmag.com/t?ctl=A5E2:2621E7
T-SQL: Weird String Concatenation
    http://lists.sqlmag.com/t?ctl=A5E3:2621E7
Performance: Large Static Table With No PK or Clustered Index
    http://lists.sqlmag.com/t?ctl=A5E4:2621E7
Security: Can't Create Instance of DTS Package
    http://lists.sqlmag.com/t?ctl=A5E5:2621E7
Development: Passing Text Parameter to Stored Procedure
    http://lists.sqlmag.com/t?ctl=A5E6:2621E7
DTS: Package Fails With Oracle Transfer
    http://lists.sqlmag.com/t?ctl=A5E7:2621E7

Hot Spot

One of the most common database management systems (DBMS) for Microsoft Windows servers is Microsoft SQL Server. Depending on your environment, your SQL Server may be your most critical application. Like most database management systems, SQL Server stores all the data in a handful of database containers, or files. If one of these containers is damaged or corrupted, all the data it contains is lost. In this free white paper, learn the data protection strategies you need to really protect your database, compare the costs, evaluate alternatives and more!
    http://lists.sqlmag.com/t?ctl=A5D7:2621E7

6. Announcements

SQL Server Performance Tips, Articles, & Forums
Hundreds of free tips and articles on SQL Server performance tuning and clustering. And get quick and accurate answers to your performance- and cluster-related question in our forum. All from the SQL Server performance and clustering authority: SQL-Server-Performance.com.
    http://lists.sqlmag.com/t?ctl=1AFB:2621E7

Exclusive Offer--Save 44% off SQL Server Magazine
Get SQL Server Magazine and get answers! Act now to get an entire year for just $39.95--that's 44% off the cover price! You'll also gain exclusive access to the entire SQL Server Magazine article database (over 2300 articles). This is a limited-time, risk-free offer, so click here now:
    http://lists.sqlmag.com/t?ctl=A5E8:2621E7

7. New & Improved

    by Dawn Cyr, [email protected]

Have you used a product that saved you time or made your job easier? Tell us about it! If we print your story in the Hands On department, we'll send you a SQL Server Magazine t-shirt. Send your product success story to [email protected].

Heroix announced Longitude, a new agentless, multiplatform OS and application monitoring and reporting product. The product is designed to provide the best features of products that are made for small to midsized businesses and products made for enterprise organizations. Because it's agentless, the product deploys immediately and is easy to learn and easy to use, and the Web-based UI provides comprehensive monitoring and detailed, graphical reporting for your OS and applications. The product is self-maintaining and self-updating and supports multiple platforms, including Windows, Oracle, and Linux. Longitude provides more than 250 operational metrics, including role-based security, customizable views, and email notification. The reporting function provides more than 125 performance reports and graphs that you access through the dashboard. Equally suitable for management and technical users, the reports let you drill down to uncover essential information through HTML links. Heroix designed this new offering completely from scratch, producing a 100-percent Java-based product based on industry standards for SQL, XML, TCP-IP, HTML,JDBC, and JMX. Pricing for Longitude starts at $599 per monitored system for a base OS plus layered application monitoring. Support services and subscription pricing are also available. For more information, contact Heroix at 800-229-6500, 617-527-1550, or [email protected].
    http://lists.sqlmag.com/t?ctl=A5E9:2621E7

Panorama Software announced general availability of Panorama 4.5, a comprehensive, high-performance business intelligence (BI) solution that supports SQL Server 2005 and 2000. New features in the latest release include an enhanced dashboard that's customizable and provides live updates so that decision makers can access near realtime dynamic data; an exception ticker, which notifies users of problems or exceptions inside a scrolling ticker bar; expanded report-authoring capabilities that support building production reports as well as multi-level grouping and sorting for ad hoc queries; and support for large dimensions, which lets users navigate, search, and select relevant information from large data sets. Companies that are investigating SQL Server 2005 can take advantage of the improvements in the new SQL Server release by leveraging Panorama's new features to access data that already exists inside the organization. For pricing and other information, contact Panorama Software at 877-709-5848, 416-545-0990, or [email protected].
    http://lists.sqlmag.com/t?ctl=5784:2621E7

Contact Us

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 AccountYou 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 2005, Penton Media