SQL Server Magazine UPDATE, July 15, 2004--Profiler for Developers

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

Introducing the VIP Site--Now with SQL Server Content

Does IT Matter?

July 15, 2004—In this issue:

1. SQL Server Perspectives

  • SQL Server Profiler: For Developers, Too
  • 2. News and Views

  • Microsoft Article Shows How to Move a Reporting Services Database
  • Results of Previous Instant Poll: Microsoft's Express Products
  • New Instant Poll: SQL Server Profiler
  • 3. Announcements

  • Get 25% Off the SQL Server Magazine Master CD
  • Enter the 2004 SQL Server Magazine Innovator Awards
  • 4. Resources

  • What's New in SQL Server Magazine: More UDF Back Doors
  • Hot Thread: Reporting Errors
  • Tip: Pageiolatch Values in Sysprocesses
  • 5. New and Improved

  • Package SQL Server Databases as Executables
  • Simplify Development of Analytic Applications
  • Develop with SQL Server 2005
  • Sponsor: Introducing the VIP Site--Now with SQL Server Content
      The Windows & .NET Magazine VIP Web site/Super CD subscribers are used to getting online access to all of our publications, plus a print subscription to Windows & .NET Magazine and exclusive access to our banner-free VIP Web site. But now we've added even more content from the archives of SQL Server Magazine! You won't find a more complete and comprehensive resource anywhere--check it out!

    1. SQL Server Perspectives

  • SQL Server Profiler: For Developers, Too

  • by Brian Moran, [email protected]

    Most IT professionals recognize that addressing performance problems during development is faster, easier, and less expensive than waiting until after you roll out your application to production. But development teams that take performance tuning seriously during the early stages of development are rare in the business world for several reasons. One, performance-tuning and scalability-testing experts are scarce. Two, tools for managing database performance tests are expensive and difficult to use and configure. And finally, given that development projects frequently fall behind schedule, no one has time to add extra tasks to the process. I can't solve all these problems today, but I have a tip that might solve a surprising number of performance problems before they ever see the light of day.

    It's no secret that I'm a big SQL Server Profiler fan. In my opinion, Profiler is the single most powerful tool in your performance-tuning arsenal because it's the best way to see what's happening inside your system. However, the SQL Server community often sees Profiler as a DBA tool. You rarely see developers actively using Profiler to hunt down performance problems. I know that you might not want to give your developers the ability to run Profiler in production for many reasons--most importantly because Profiler requires the user to have sa rights. However, there are good reasons for developers to have sa control over the boxes they're developing for, and performance tuning is one of them.

    You're even less likely to find developers incorporating Profiler into their unit-testing strategies. Based on a decade of performance-tuning experience, I believe that easy-to-fix problems--such as missing indexes and excessive numbers of round-trips to the server from one screen--cause more than half of a production system's performance problems. These problems often go undiscovered because even if performance isn't perfect, it's typically good enough that no one bothers to improve it.

    Most IT shops investigate such problems only if they cause noticeable and serious performance problems for their user community. However, developers can find and solve many of these easy-to-fix problems by performing basic Profiler analysis against each of their use-case scenarios during unit testing. Too often I've conducted a performance audit for a company and heard, "Wow, I had no idea the application did that!" No matter what "that" is, you can usually easily avoid the problem by running Profiler as part of the unit test. For example, one customer recently had a single search result in more than 5000 round-trips to the server. Although 5000 round-trips from one screen might be fine for one user at a time, it doesn't scale well. Incorporating Profiler into a unit-test strategy will quickly and easily uncover problems like this one.

    Of course, running Profiler won't automatically solve all your performance-tuning problems. Developers need to know what to look for when analyzing Profiler output. Your internal SQL Server experts should be able to compile a simple list of tuning dos and don'ts that you can share with development teams seeking to integrate Profiler into their unit-testing strategies.

    I encourage every development team I work with to embrace the idea that tuning isn't simply the DBA's domain. The application doesn't work if it doesn't meet basic performance requirements. If the application doesn't work, it should pass through testing. I'm also not suggesting that DBAs should outsource performance tuning to developers and play golf for the rest of their lives. Not all tuning and scalability issues can be detected during unit testing. DBAs and developers need to learn to partner together more effectively to prevent performance problems. Integrating simple performance tests, based on Profiler analysis, into a unit-test strategy is a great first step toward that partnership.

  • Does IT Matter?

  •    One of the liveliest debates in executive circles and at leading business schools today stems from the Harvard Business Review article "IT Doesn't Matter" by Nicholas Carr. Now you can join the discussion--with Nicholas Carr himself--in a special video-streamed Webcast sponsored by SAS. Carr and SAS Senior Vice President Jim Davis will continue the dialogue and field questions from both a live studio audience and live Webcast audience. Register today for this important forum.

    2. News and Views

  • Microsoft Article Shows How to Move a Reporting Services Database

  •   Microsoft has released an article that tells you how to move a SQL Server 2000 Reporting Services database from a computer that's running Reporting Services to another computer. You might want to move a Reporting Services database from one computer to another for several reasons. For example, if you use Reporting Services to create reports on a computer that's used in an application's development environment, you might want to move those reports to a computer that's used in the production environment. To do so, you must move the Reporting Services database. You don't have to deploy the reports again if you move the Reporting Services database from one computer to another. You also don't have to reconfigure Reporting Services on the destination computer. To learn more, read the Microsoft article "How to move a Reporting Services database from a computer that is running Reporting Services to another computer" at

  • Results of Previous Instant Poll: Microsoft's Express Products

  •   The voting has closed in SQL Server Magazine's Instant Poll for the question, "Which of the new Express products from Microsoft will you download?" Here are the results (+/- 1 percent) from the 126 votes:

    • 51%   SQL Server Express
    • 7%    Visual Web Developer Express
    • 11%   Visual Basic Express
    • 6%    Visual C# Express
    • 25%   All of the above

  • New Instant Poll: SQL Server Profiler

  •    The next Instant Poll question is "Do you use SQL Server Profiler during application development?" Go to the SQL Server Magazine Web site and vote for 1) always, 2) sometimes, 3) no, but I plan to, or 4) no, and I don't plan to.

    3. Announcements

  • Get 25% Off the SQL Server Magazine Master CD

  •    The SQL Server Magazine Master CD offers portable, high-speed access to a library of searchable article archives, endless code listings, timesaving tips and tricks, and valuable expertise published in SQL Server Magazine and T-SQL Solutions. Let this helpful resource save you some time anywhere you are. Subscribe today and get 25% off!

  • Enter the 2004 SQL Server Magazine Innovator Awards

  •    This year's competition, sponsored by Idera, is calling for a cutting-edge SQL Server solution that solves a real-world problem and/or improves performance. One grand-prize winner will win a free trip to the 2004 SQL Server Magazine Connections Conference in Las Vegas, Nevada, and get a special trophy. Fill out an entry form today to get the recognition you deserve!

    4. Resources

  • What's New in SQL Server Magazine: More UDF Back Doors

  •   T-SQL programmers often use user-defined functions (UDFs) to encapsulate algorithm implementations. However, several aspects of UDFs constrain programmers' capabilities. In his July T-SQL Black Belt column, "More UDF Back Doors," Itzik Ben-Gan continues his coverage of UDFs by looking an aspect of UDFs--related to their atomicity-that limits programmers' capabilities and a couple of back doors that circumvent this limitation. He also discusses a supported UDF feature that most people don't know about: modifying data through inline UDFs. Read this article today at

  • Hot Thread: Reporting Errors

  • Craig HB uses SQL Server 2000 Reporting Services to develop reports on his PC and deploy those reports to a server. His PC has only Reporting Services' client components, whereas the server he's deploying to has the client and server components installed. When Craig HB deploys his reports to the server, he receives the error message "The underlying connection was closed: Could not establish trust relationship with remote server." But that's not Craig HB's only problem. He created an ASP.NET application on his PC that runs reports on the server using Reporting Services' Web Service. When Craig HB tries to render a report, he receives the error message "The request failed with HTTP status 404: Not Found." Offer your advice and see what other people have said on SQL Server Magazine's Reporting Services forum at

  • Tip: Pageiolatch Values in Sysprocesses

  • by Brian Moran, [email protected]

    Q. I'm seeing pageiolatch_sh values in the master..sysprocesses table's lastwaittype column. Many sysprocesses rows that have a pageiolatch lastwaittype also have nonzero values for waittime, so I know that the server process IDs (SPIDs) are waiting. What do the pageiolatch values mean?

    A. Searching SQL Server Books Online (BOL) turns up five hits for latches, and none of the hits provides much detail. The most detailed entry says that "latches are very lightweight, short-term synchronization objects protecting actions that need not be locked for the life of a transaction. They are primarily used to protect a row when read for a connection." We're all familiar with SQL Server locks and how they can affect concurrency if they start to block other processes. What most people don't understand is that SPIDs can wait a long time for different types of latches. As you said, latch wait times show up for a SPID in master..sysprocesses, but they don't cause a SPID to appear as blocked in the output of sp_who or similar investigative tools. Let me be clear about the distinction between a latch that's blocked and a latch that's waiting. A blocked connection generally means that another connection is holding a lock that the first connection needs. When processes wait on each other for locks to be released, it's called blocking. A process that's waiting doesn't involve a lock. I'll explain the pageiolatch_sh waittype so you can see what I mean...
         Read the full answer to this question at

    5. New and Improved

    by Dawn Cyr, [email protected]

  • Package SQL Server Databases as Executables

  • Red Gate Software announced SQL Packager, software that packages SQL Server databases as executables so that you can deploy the new or updated databases locally or at remote sites. The software, which relies on engines in Red Gate's SQL Compare and SQL Data Compare products, guides you through four basic packaging steps: deciding on the schema and data you want to package, scripting schema and data to preserve dependencies, packaging the database as an executable or .NET project, and creating a utility that lets administrators or customers at the new location install the database. Video demos, a PowerPoint presentation, a walkthrough, and a fully supported, 14-day trial version of SQL Packager are available for free on the Red Gate Web site. Pricing for SQL Packager starts at $690 for a single-user license, and discounts are available for multi-user versions. For more information, contact Red Gate Software at 866-733-4283, 44-870-160-0037, or [email protected]

  • Simplify Development of Analytic Applications

  • Enterprise Blocks announced Enterprise Blocks 2.0, an integrated set of ASP.NET Web controls, Web services, and Windows services that serve as the building blocks for developing analytic applications. The latest release features a new service provider for accessing SQL Server tables directly from Enterprise Blocks clients. The service provider uses the .NET managed provider classes in the System.Data.SQLClient namespace to communicate with SQL Server and support cross-tab and table analysis directly against tables and views in SQL Server. The software uses the field relationships defined in the database to support cross-tab analysis, and you can use SQL Server extended properties to set up additional field relationships; specify field formatting, aggregation functions, and visibility; and set up user-friendly names for fields, tables, and views. This capability lets you develop OLAP-style applications directly against SQL Server data or as a complement to using Analysis Services. Enterprise Blocks 2.0 costs $495. For more information, contact Enterprise Blocks at [email protected]

  • Develop with SQL Server 2005

  • Addison-Wesley Professional announced "A First Look at SQL Server 2005 for Developers," a book by Bob Beauchemin, Niels Berglund, and Dan Sullivan that shows developers how to take advantage of innovations in SQL Server 2005. The book includes practical explanations of SQL Server 2005's new data model, built-in .NET hosting, improved programmability, and SQL-99 compliance. Chapter topics include writing procedures, functions, and triggers in .NET languages; using T-SQL enhancements; the XML data type and XML query languages; SQL Server 2005 as a Web services platform; client-side coding; and using SQL Server 2005's built-in application server capabilities. "A First Look at SQL Server 2005 for Developers" costs $40.49. For more information or to purchase the book, contact Addison-Wesley Professional at 617-848-6000.

    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.

    Contact Us:

    • About SQL Server Perspectives — [email protected]
    • 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 — Kate Silvertooth([email protected])

    Manage Your Account

    You are subscribed as #EmailAddr#.

    To unsubscribe from this email newsletter, send an email message to mailto:#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.

    Copyright 2004, Penton Media, Inc.

    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.