SQL Server Magazine UPDATE, February 3, 2005--Getting to Know I/O

Subscribe to SQL Server Magazine:

Our domain name 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 Trial of Lumigent Log Explorer

MSDN Webcast: "Top Web App Hacking Tricks"

New SQL Server Special Report

February 3, 2005

1. Perspectives

  • Getting to Know I/O
  • 2. News and Views

  • Microsoft Fixes .NET Framework Problem
  • Microsoft Article Shows How to Move MPS Databases
  • Results of Previous Instant Poll: Data Integrity
  • New Instant Poll: Debugging T-SQL Code
  • 3. Events and Resources

  • SQL Server Administration for Oracle DBAs
  • 4. Peer to Peer

  • Hot Tip: Beware: Using sp_configure to Change a Value Involves DBCC FREEPROCCACHE
  • Hot Article: A Tale of Two CLRs
  • Kevin Kline's "In a Nutshell" Blog: Server Performance Advisor
  • Hot Threads
  • 5. Announcements

  • Nominate Yourself or a Friend for the MCP Hall of Fame
  • Get Access to Every SQL Server Magazine Article on CD-ROM
  • 6. New and Improved

  • Deliver Crystal Reports on the Web
  • Remotely Manage SQL Server
  • Sponsor: Free Trial of Lumigent Log Explorer
    Voted best software by SQL Server users, Log Explorer offers superior log reading and selective, online data recovery for SQL Server. With Log Explorer, you can:

  • Recover data fast and online without a backup
  • Repair data at various levels of granularity
  • Access the transaction log to easily query database activity
  • Export transaction information for follow-up analysis
  • Try an evaluation today:

    1. Perspectives

  • Getting to Know I/O

  •     by Brian Moran, [email protected]

    Two weeks ago, in "Zen and the Art of Hardware-Resource Distribution" ( http://lists.sqlmag.com/t?ctl=E5F:7B3DB ) I said many people have I/O problems they don't know about. If you think you might be one of those people (or even if you know SQL Server I/O Inside and Out—capitalization pun intended ), you can learn about I/O requirements for SQL Server database file operations in a new Microsoft white paper, "SQL Server 2000 I/O Basics" ( http://lists.sqlmag.com/t?ctl=E59:7B3DB ) by Bob Dorr, an escalation engineer with SQL Server Product Support Services. PSS produces some of the best information about SQL Server—after all, they're the ones who have to fix it when it's broken! Escalation engineers like Bob have the benefit of direct access to source code and lots of interaction with real-world customers. Bob's paper summarizes I/O-related issues that are important to SQL Server professionals and has a nice blend of helpful, practical SQL Server guidance. The paper doesn't cover some topics (e.g., RAID) in much detail, but it's a good primer that will help you increase system performance and avoid I/O-environment errors.

    I can't summarize all of the 28-page paper, but I'll share one interesting point about I/O as it relates to recovery intervals. Some people attempt to tweak the recovery interval to improve I/O efficiency or reduce contention during a checkpoint. Bob writes: "Several specific conditions (side effects) can arise when the recovery interval is adjusted. Weigh these carefully before adjusting the recovery interval." When you increase the checkpoint interval by increasing the recovery interval, you shift the pressure of handling dirty pages (pages on which data modifications have taken place) to the lazy writer code line. Because the lazy writer isn't designed to perform checkpoint-like activities, interval changes degrade performance. "The lazy writer does perform proper activity on the dirty pages to ensure data integrity and free list maintenance," Bob says, "but unlike the checkpoint process, it isn't designed to remove the dirty page I/O latency." He goes on to say that "Checkpoints allow dirty pages to be written more aggressively." If you leave the checkpointing actions to the lazy writer, you introduce latency because the lazy writer is forced to perform I/O to age a buffer.

    I doubt I'm the only person who's experimented with changing the recovery interval to increase the time between checkpoints. Sometimes we like to think we're smarter than SQL Server and tweak defaults for better performance—which is sometimes the right thing to do. But Bob's information is something I had never fully considered. Given the interaction between the recovery interval, checkpoints, and the lazy writer process, Bob says: "If you've adjusted the recovery interval, you should watch the lazy writer performance counter(s) activity closely." You owe it to yourself to read this new guide from Microsoft and learn more about SQL Server I/O. I enjoyed the piece, and I'm sure you will too.

    MSDN Webcast: "Top Web App Hacking Tricks" In this series, we define the various elements of security throughout the software-development lifecycle for Web applications and provide examples of real-world application-level attacks such as SQL injections, cross-site scripting, session hijacking, and error trapping.

    2. News & Views

  • Microsoft Fixes .NET Framework Problem

  • Microsoft has released a hotfix for a problem that occurs when you receive the SqlException object in the Microsoft .NET Framework. You can use the .NET Framework 1.0 and the .NET Framework 1.1 to create an instance of the SqlConnection class in SQL Server 2000. The Server property is supposed to contain the name of the computer that's running the SQL Server instance the SqlConnection class is connected to. However, if the SqlException object is raised, the SqlException object's Server property isn't set so the Server property returns an empty string. To find out more about this problem and the supported hotfix, read the Microsoft article "FIX: The Server property may return as an empty string when you receive the SQLExeception object in the .NET Framework 1.0 and in the .NET Framework 1.1" at

  • Microsoft Article Shows How to Move MPS Databases

  • Microsoft has released an article that describes how to migrate your Microsoft Provisioning Server (MPS) databases from a standalone SQL Server 2000 instance to a clustered virtual SQL Server 2000 named instance. The article walks you through assessing your environment, preparing the servers, moving the customer database (HECustomerDB), and moving the MPS databases. To find out more, read the Microsoft article "How to move Microsoft Provisioning Server (MPS) databases to a clustered virtual SQL Server 2000 named instance" at

  • Results of Previous Instant Poll: Data Integrity

  • "Where do you usually enforce data integrity?" Here are the results from the 173 votes (deviations from 100 are due to a rounding error):

  • 10% At the client
  • 35% At the server
  • 43% At the client and the server
  • 11% Varies according to efficiency
  • 1% I don't give much thought to data integrity
  • New Instant Poll: Debugging T-SQL Code

  • "How do you debug T-SQL code?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=E6C:7B3DB ) and submit your vote for

  • Using the Query Analyzer 2000 debugger
  • Using the Visual Studio T-SQL debugger
  • Using SQL Server Profiler
  • By analyzing the results and rereading the code
  • I never need to debug my code
  • 3. Events and Resources

  • SQL Server Administration for Oracle DBAs

  • In this free Web seminar, Oracle DBAs will get a quick start in mapping their Oracle database-management skills, knowledge, and experience to SQL Server database management. Learn the similarities and differences between Oracle and SQL Server and get a preview of the real-world tips and techniques for managing these technologies. Register now, and increase your value within your company!

    See the complete Windows IT Pro Network guide to Web and live events.

    4. Peer to Peer

  • Hot Tip: Beware: Using sp_configure to Change a Value Involves DBCC FREEPROCCACHE

  •     by Brian Moran, [email protected]

    I recently learned that when you call sp_configure to set a value for any option, SQL Server issues a DBCC FREEPROCCACHE command. This command invalidates all stored procedure plans currently cached and requires recompilation of new plans the next time the stored procedure runs. Sp_configure issues this DBCC command because changing some options that sp_configure supports can invalidate stored procedure plans. For example, if you change the max degree of parallelism option, SQL Server treats all parallel plans as invalid. Although issuing DBCC FREEPROCCACHE is a convenient, simple way to ensure that all plans stay consistent with the current settings of sp_configure, I think this behavior is overkill because sp_configure also lets you set options that don't require the invalidation of existing plans.

    Read the rest of this tip today at

  • Hot Article: A Tale of Two CLRs

  • It's no secret that one of the most important features of SQL Server 2005 is the integration of the Common Language Runtime (CLR) with the database. However, IBM's recent release of DB2 UDB 8.2, codenamed Stinger, surprised a lot of people and stole SQL Server 2005's thunder by supporting database-object creation through .NET. Although, IBM got CLR support out the door first, don't make the mistake of thinking that the CLR support DB2 UDB 8.2 offers is the same as the CLR support SQL Server 2005 will provide. In his February editorial, "A Tale of Two CLRs," Michael Otey covers some of the profound differences in the way each product implements support for the .NET CLR. Read this article today at

  • In a Nutshell: Server Performance Advisor

  • In this week's blog, "Server Performance Advisor," Kevin Kline talks about Microsoft's performance-diagnostic tool, which finds performance bottlenecks on Windows Server 2003. The tool is especially geared towards problems in Microsoft IIS 6.0 and Active Directory. However, it's also a useful general performance-measurement tool that tracks a variety of other areas of system utilization, such as hot files, top TCP clients, top CPU consumers, print spooling, and context-switching data. The tool also tracks preliminary File Server trace data. Share your thoughts about the tool and let Kevin know whether you think it offers any value over other native tools, such as Performance Monitor today at

    Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.

    T-SQL: Can't Get a Mystery Transaction to Commit
    Administration: Notification Without Outlook Client
    Development: Order Sums By Month
    DTS: Email When Package Output Isn't OK
    Performance: Logical and Extent Fragmentation
    T-SQL: Crosstab Query Question

    Hot Spot

  • New SQL Server Special Report

  • Get the inside scoop on database change management for SQL Server. Find out which product has been coined "the best tool and process seen yet for database change management" and how the associated source code integration and technical support beat the industry standard. Click here:

    5. Announcements

  • Nominate Yourself or a Friend for the MCP Hall of Fame

  • Are you a top-notch MCP who deserves to be a part of the first-ever MCP Hall of Fame? Get the fame you deserve by nominating yourself or a peer to become a part of this influential community of certified professionals. You could win a VIP trip to Microsoft and other valuable prizes. Enter now—it's easy:

  • Get Access to Every SQL Server Magazine Article on CD-ROM

  • Celebrate 2005 with the newest version of the SQL Server Magazine Master CD! You'll receive portable access to every SQL Server Magazine article ever written since 1999—online and in-hand on CD-ROM! As an added bonus, you'll also receive exclusive scripting and security content—a $30 value for free. Sign up now:

    6. New & Improved

        by Dawn Cyr, [email protected]

  • Share Your Product Success Story and Get a T-Shirt!

  • Have you used a product that saved you time or made your job easier? 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 [email protected]

  • Deliver Crystal Reports on the Web

  • Business Objects announced Crystal Reports XI, a reporting solution that lets developers and other IT professionals design, manage, and deliver reports over the Internet and embed reporting in enterprise applications. The most recent Crystal Reports release includes new features that enable easier report design, simplify report maintenance, expand data access, and integrate with the Business Objects XI platform. Enhanced data drivers (including XML and JDBC drivers) make it easy to access nearly all data sources. The new release gives developers intuitive design tools to create reports. For example, developers can now preview reports in HTML before publishing them to the Web. An Intelligent Charting feature automatically evaluates data in a report and gives developers the most appropriate charting options for viewing that type of data. And a new report-management workbench lets developers organize reports and decide how to schedule, manage, distribute, and secure access to reports. End users, too, have more control over the reports they view. Developers can distribute report templates with drop-down menus (aka dynamic and cascading prompts) that let end users customize reports. Users can also get their reports in RTF format so that they can modify their own reports instead of requesting changes from IT. Pricing for Crystal Reports XI ranges from $495 to $595 per user. For complete pricing and other information, contact Business Objects at 866-681-3435 or 604-681-3435.

  • Remotely Manage SQL Server

  • iAdmin Mobile announced its Mobile Management Pack (MMP) for Microsoft SQL Server release candidate 2 (RC2), a secure mobile systems-management and administration tool. The product lets DBAs manage multiple aspects of their companies' SQL Server infrastructure, including databases, tables, views, stored procedures, user accounts, roles, rules, defaults, and catalogs. DBAs can also execute custom queries to manage their databases from platform-independent mobile devices or Web browers. All iAdmin Mobile tools use standard 128-bit SSL encryption for secure connections. IAdmin Mobile MMP for Microsoft SQL Server RC2 supports SQL Server 2000 (Enterprise and Standard editions) and Microsoft SQL Server Desktop Engine (MSDE) and costs $120 per managed server. For more information, contact iAdmin Mobile at 402-617-0493 or [email protected]

    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 Medi

    Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.