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

Subscribe to SQL Server Magazine:
    http://lists.sqlmag.com/t?ctl=E64:7B3DB

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
    http://lists.sqlmag.com/t?ctl=E68:7B3DB

MSDN Webcast: "Top Web App Hacking Tricks"
    http://lists.sqlmag.com/t?ctl=E5C:7B3DB

New SQL Server Special Report
    http://lists.sqlmag.com/t?ctl=E5A:7B3DB

February 3, 2005

1. Perspectives

2. News and Views

3. Events and Resources

4. Peer to Peer

5. Announcements

6. New and Improved

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:

Try an evaluation today:
     http://lists.sqlmag.com/t?ctl=E68:7B3DB

1. Perspectives

    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.
    http://lists.sqlmag.com/t?ctl=E5C:7B3DB

2. News & Views

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
    http://lists.sqlmag.com/t?ctl=E65:7B3DB

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
    http://lists.sqlmag.com/t?ctl=E61:7B3DB

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

"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

3. Events and Resources

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!
http://lists.sqlmag.com/t?ctl=E50:7B3DB

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

4. Peer to Peer

    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
    http://lists.sqlmag.com/t?ctl=E57:7B3DB

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
    http://lists.sqlmag.com/t?ctl=E58:7B3DB

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
    http://lists.sqlmag.com/t?ctl=E5E:7B3DB

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

T-SQL: Can't Get a Mystery Transaction to Commit
    http://lists.sqlmag.com/t?ctl=E56:7B3DB
Administration: Notification Without Outlook Client
    http://lists.sqlmag.com/t?ctl=E54:7B3DB
Development: Order Sums By Month
    http://lists.sqlmag.com/t?ctl=E53:7B3DB
DTS: Email When Package Output Isn't OK
    http://lists.sqlmag.com/t?ctl=E52:7B3DB
Performance: Logical and Extent Fragmentation
    http://lists.sqlmag.com/t?ctl=E55:7B3DB
T-SQL: Crosstab Query Question
    http://lists.sqlmag.com/t?ctl=E51:7B3DB

Hot Spot

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:
    http://lists.sqlmag.com/t?ctl=E5A:7B3DB

5. Announcements

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:
    http://lists.sqlmag.com/t?ctl=E5B:7B3DB

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:
    http://lists.sqlmag.com/t?ctl=E6D:7B3DB

6. New & Improved

    by Dawn Cyr, [email protected]

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].

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.
    http://lists.sqlmag.com/t?ctl=E69:7B3DB

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].
    http://lists.sqlmag.com/t?ctl=E6B:7B3DB

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 2004, Penton Medi