SQL Server Magazine UPDATE, October 6, 2005--SQL Server 2005 Dynamic Management Views

Subscribe to SQL Server Magazine:

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:7B3DA

Free Visual C# .NET Training CD!

Quest Software--Quest Central for SQL Server

Readers' Choice for Scheduling and Notifications     http://lists.sqlmag.com/t?ctl=1587E:7B3DA

October 6, 2005

1. Perspectives

  • Dive Into DMVs
  • 2. SQL Server 2005 Watch

  • Microsoft Provides SQL Server 2005 Learning Opportunities
  • 3. News & Views

  • BIDS Can Regenerate a Duplicate SSIS Package ID
  • Results of Previous Instant Poll: User Groups
  • New Instant Poll: DBA Activities
  • 4. Events and Resources

  • Discover SQL Server 2005 for the Enterprise. Are you prepared?
  • Deploy VoIP and FoIP Technologies
  • Get Ready for the SQL Server 2005 Roadshow in Europe
  • 5. Featured White Paper

  • Build a Superior Windows File-Serving Environment
  • 6. Peer to Peer

  • Hot Tip: Communicating with Analysis Services
  • Hot Article: A More Versatile TOP
  • In a Nutshell: PASS Summit 2005: Day 3 for Me, Day 1 of Conference
  • Hot Threads
  • 7. Announcements

  • SQL Server Magazine Has Answers
  • Become a VIP Subscriber!
  • 8. New & Improved

  • Protect Databases and Data
  • Enhance Security with Change Auditing
  • Learn How and When to Upgrade to New ASP.NET 2.0 Features

  • 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:

    1. Perspectives

  • Dive Into DMVs

  •     by Brian Moran, [email protected]

    SQL Server 2005 Dynamic Management Views (DMVs) might not be as important to humankind as the invention of the wheel or the taming of fire, but in the life of a SQL Server professional, they'll probably rank pretty high on the scale of significant advancements. I've been excited about DMVs since I first learned about them a few years ago. At face value, DMVs might seem to be nothing more than a new-and-improved version of system tables--and who can get too excited about system tables? But during last week's Professional Association for SQL Server (PASS) Summit, I attended some really cool Microsoft presentations that helped me have a much better appreciation for the effect DMVs will have on my ability to support, manage, tune, and otherwise interact with SQL Server.

    DMVs essentially make your server more transparent. SQL Server 2000 and earlier editions don't make it easy to solve important tuning, troubleshooting, and supportability problems. As a consultant, I think that's wonderful, so I have mixed feelings about DMVs.

    I always knew that DMVs would eventually be a huge leap forward in solving practical problems, but I honestly thought the initial DMV model would be sorely lacking in real-world practicality. No offense to product development teams at Microsoft and elsewhere, but sometimes it can be easy to build something really cool but that doesn't necessarily solve a problem for a front-line DBA in the field.

    However, I was pleasantly surprised that Microsoft has made DMVs quite practical. I'm a performance-tuning geek at heart, so that's the DMV use I explored most at PASS. I was pleased with what I saw. I suspect that Microsoft did such a good job of hitting the target because it relied on input from two teams--Microsoft Product Support Services (PSS) and the SQL Server Customer Advisory Team--that truly understand the most pressing real-world problems that high-end customers face. Most people are familiar with PSS and its role in solving customer problems, but many customers have never heard of the SQL Server Customer Advisory Team. This team works to solve the biggest-of-the-big and hardest-of-the-hard SQL Server problems around the world. I know some of the smartest SQL Server people in the world, and every member of the SQL Customer Advisory Team is on that short list.

    PSS, the SQL Server Customer Advisory Team, and the SQL Server development team have done a great job of making DMVs practical instead of just cool. Think about how hard it is to figure out what indexes SQL Server isn't using; it's really hard in SQL Server 2000. But it's really easy with SQL Server 2005 DMVs. Are you interested in knowing the aggregate cost of a query, the affect of parallelization versus serialized query plans, or the cost of query compilations? Answering those questions is really hard today and really easy in SQL Server 2005 because of DMVs and supporting technology in SQL Server 2005.

    I could go on and on--and I probably will because I plan to make understanding DMVs inside and out one of my core specialties. For now, I'll simply paraphrase a quote from SQL Server Customer Advisor Team member Tom Davidson. In one of his PASS Summit presentations, Davidson summarized the possibilities of DMVs by saying, "Transparent servers are a reality in SQL Server 2005." I couldn't agree more. With DMVs, it's surprisingly easy to see what's really happening inside SQL Server 2005, and this visibility greatly improves supportability, manageability, and tenability. Although I understand that many of the DMVs won't be documented until RTM, all of them are included in the September CTP. So do yourself a favor and dive into the new DMV model.

    Free Visual C# .NET Training CD!
    Become more efficient and effective with AppDev's award-winning training. Get a FREE Visual C# .NET training CD ($95 value) and see why developers have voted AppDev THE BEST! Click the link below for your FREE Visual C# .NET CD!

    2. SQL Server 2005 Watch

    Microsoft Provides SQL Server 2005 Learning Opportunities
    As part of its ongoing educational efforts for SQL Server 2005, Microsoft has created a special Web site, Microsoft SQL Server TechCenter, where you can find SQL Server 2005-related articles, Webcasts, Virtual Labs, and e-learning courses. Each month, the site's landing page features information about a particular SQL Server 2005 topic. The October theme, Mission Critical High Availability, highlights the new SQL Server 2005 database-engine technologies that are designed to reduce planned and unplanned downtime, provide solutions for disaster recovery, and provide greater system availability to database users. To learn how SQL Server 2005 can help you improve the availability and reliability of your database solutions, visit the SQL Server TechCenter at

    Sponsor: Quest Software--Quest Central for SQL Server
    SQL changes slowing down your database? Identify and resolve performance issues with Quest Central for SQL Server Performance Analysis. Easily collect performance data without overhead. Correlate performance change with times of known poor performance. Then get the analysis you need to pinpoint the problem--all within Quest Central's integrated interface. Quickly diagnose and resolve even the hardest performance problems without manual trial and error. To learn more, download Kevin Kline's new technical brief.

    3. News & Views

    BIDS Can Regenerate a Duplicate SSIS Package ID
    When you create a package in SQL Server 2005 Integration Services, SSIS generates a 16-byte GUID and stores it as the package's PackageID property. After you run the package, you can write the package ID to a log file by using the SSIS log provider. This package ID differentiates log entries for different packages. However, you might encounter some confusing package behavior. For example, when an SSIS package is copied in a file system, the new copy might contain the same package ID as the original SSIS package. You might be able to successfully run the package that has the same package ID. However, you can't differentiate the two packages by using the logging data because the package IDs are the same. To learn how to regenerate a new package ID for each package, read the Microsoft article "How to use SQL Server Business Intelligence Development Studio or the dtutil utility to regenerate the package ID of an SSIS package that was duplicated" at

    Results of Previous Instant Poll: User Groups
    "What user group are you a member of?" Here are the results from the 46 votes (deviations from 100 are due to a rounding error):

  • 20%   Professional Association for SQL Server (PASS)
  • 22%   SQL Server Worldwide Users Group (SSWUG)
  •  7%   My local user group
  •   4%   Other
  • 48%   None
  • New Instant Poll: DBA Activities
    "How much time do you spend on DBA activities?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=3D1:7B3DA ) and submit your vote for

  • All of my time
  • Most of my time, but I also have other duties
  • Half of my time
  • Some of my time, but DBA activities aren't my primary job
  • None
  • 4. Events and Resources

  • Discover SQL Server 2005 for the Enterprise. Are you prepared?

  • In this free half-day event, you'll learn how the top new features of SQL Server 2005 will help you create and manage large-scale, mission-critical enterprise database applications and make your job easier. Find out how to leverage SQL Server 2005's new capabilities to best support your business initiatives. Register today!

  • Deploy VoIP and FoIP Technologies

  • Voice over Internet Protocol (VoIP) is the future of telecommunications, and many companies are already enjoying the benefits of transporting VoIP networks to significantly reduce telephone and facsimile costs. Join industry expert David Chernicoff for this free Web seminar to learn the ins and outs of boardless fax in IP environments, tips for rolling out fax and integrating fax with telephony technologies, and more!

  • Get Ready for the SQL Server 2005 Roadshow in Europe

  • Back By Popular Demand--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 one-year membership to PASS and one-year subscription to SQL Server Magazine. Register now.

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

    5. Featured White Paper

  • Build a Superior Windows File-Serving Environment

  • In this free white paper, you'll get the tools you need to provide a scalable, highly available CIFS file service. You can use inexpensive, industry-standard servers that you can add to incrementally as demands require while retaining the management simplicity of a single server and a single pool of exported file systems.

    6. Peer to Peer

  • Hot Tip: Communicating with Analysis Services

  •     by Carl Rabeler, [email protected]

    Do ADO MD.NET and XMLA clients communicate differently with a SQL Server 2000 Analysis Services instance than with a SQL Server 2005 Analysis Service instance?

    Read the answer to this question today at

  • Hot Article: A More Versatile TOP

  • SQL Server 2005 introduces two enhancements to the TOP clause. You can specify an expression as an input to the TOP keyword, and you can also use TOP in modification statements (INSERT, UPDATE, and DELETE). In his September T-SQL 2005 column, Itzik Ben-Gan looks at sample uses of these enhancements. Read this article today and post your comments at

  • In a Nutshell: PASS Summitt 2005: Day 3 for Me, Day 1 of Conference

  • The Professional Association for SQL Server (PASS) saw record attendance at its 2005 Summit, and Kevin Kline had to defeat the butterflies in his stomach to introduce the keynote speaker, Microsoft Group Program Manager for Business Intelligence Bill Baker. Kevin details the keynote's highlights in this week's blog "PASS Summitt 2005: Day 3 for Me, Day 1 of Conference." Get a link to streaming video of the speech, and tell Kevin what you think today at

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

    SQL Server General Discussion: Linked Server Issues
    Administration: SP4 Backout Plan
    T-SQL: My Stored Procedure Returns Repeated Records
    Performance: Race Conditions and Configuration Options
    Security: Authentication Mode Switched from SQL Server to Windows Security
    Development: Pattern Matching

    Hot Spot

  • Readers' Choice for Scheduling and Notifications

  • SQL Server Magazine readers honor sqlSentry as the Best Job Automation and Scheduling Tool and the Best Alerting/Notification Software. Try it now and see how you can significantly reduce the time spent on daily operational tasks. See why Brian Knight, SQL Server MVP says, "You'll never know how you lived without sqlSentry."

    7. Announcements

  • SQL Server Magazine Has Answers

  • You won't want to miss any of the fall issues! Subscribe now and discover the best tools to keep SQL Server tuned, the ins and outs of SQL Server 2005, ways ADO.NET 2.0 solves your problems, and much more. You'll also gain exclusive access to the entire SQL Server Magazine online article database (more than 2300 articles), and you'll SAVE 44% off the cover price. Click here:

    Become a VIP Subscriber!
    Get inside access to ALL the articles, tools, and helpful resources published in Windows IT Pro, SQL Server Magazine, Exchange and Outlook Administrator, Windows Scripting Solutions, and Windows IT Security--that's more than 26,000 articles at your fingertips. Your VIP subscription also includes a valuable one-year print subscription to Windows IT Pro and two VIP CDs (includes the entire article database on CD). Sign up now:

    8. New & Improved

       by Dawn Cyr, [email protected]

  • Share Your Story and Get a T-Shirt!

  • Have you used a product that saved you time or made your job easier? Tell us how your favorite product solved a SQL Server problem for you, and if we print your submission in the magazine's Hands On department we'll send you a SQL Server Magazine t-shirt. Send your email today to [email protected].com!

  • Protect Databases and Data

  • SoftTree Technologies announced DB Audit Expert 2.7, a complete set of auditing solutions for SQL Server, Oracle, IBM DB2, and Sybase database environments. The solutions provide complete access control and realtime actionable information that you need for effective enterprise security operations and IT governance requirements. You implement the solutions on the database back-end, thus eliminating backdoor access to the database. Then, the solutions let database and system administrators, security administrators, auditors, and operators track and analyze database activity, including data creation, change, or deletion. The latest release of the software provides transparent system-level and data-change auditing of existing applications without requiring application changes. In addition, the new release includes several new tools for database penetration testing, vulnerability assessment, and security auditing in heterogeneous environments. And the product's enterprise edition includes a new Alert Center, which automates checking of database audit-trail records. Pricing for DB Audit Expert 2.7 starts at $299 per server, and the new release is offered as a free upgrade to existing customers For more information, contact SoftTree Technologies at 800-289-9256 or [email protected]

  • Enhance Security with Change Auditing

  • Application Security announced AppDetective 5.2, a database vulnerability-assessment scanner that includes new distributed-management and granular security-change auditing capabilities. The AppDetective Distributed System comprises two main components, the Distribution manager and the Remote Engines, which work together to let you standardize and centralize reporting and analysis. In addition, the product's Security Change Auditing tracks and analyzes all database modifications. The latest release of the product lets you centrally track and log the precise location of database changes and triggers alerts in the event of a security breach. You can use the product to centrally manage patch levels of all databases. And the product lets you ensure database integrity by validating planned changes and tracking and reporting unintended or unauthorized changes. A free evaluation version of AppDetective 5.2 is available for download. For more information, contact Application Security at 866-927-7732 or [email protected]

  • Learn How and When to Upgrade to New ASP.NET 2.0 Features

  • Mike Murach and Associates announced "Murach's ASP.NET 2.0 Upgrader's Guide: VB Edition," a book that focuses on the practical aspects of upgrading to the new features of ASP.NET 2.0. Written for experienced developers, the book gives examples of how and when to apply the new ASP.NET features in everyday Web applications. Chapters cover key features such as SQL Server, Microsoft Access, and XML data sources; the GridView, DetailView, and FormView controls; object data sources that allow binding to custom business objects; login and site navigation controls; profiles; the MultiView and Wizard controls; themes; Web parts for building portals; and tools for configuring and deploying new applications. "Murach's ASP.NET 2.0 Upgrader's Guide: VB Edition" costs $39.50, and a free sample chapter is available at the publisher's Web site. For more information or to buy the book or its predecessor, "Murach's ASP.NET 2.0 Upgrader's Guide: C# Edition," contact Murach and Associates at 800-221-5528 or [email protected]

    Contact Us

  • About Developer .NET Perspectives [email protected]
  • About technical questions http://www.sqlmag.com/forums
  • About product news [email protected]
  • About your subscription [email protected]
  • About sponsoring an UPDATE contact Richard Resnick, [email protected], or Lisa Kling, [email protected]
  • Developer .NET 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.

    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 2005, Penton Media, Inc. All Rights Reserved.

    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.