Developer Update--Snapshot Isolation vs. NoLock--July 7, 2006

Developer Update--Snapshot Isolation vs. NoLock--July 7, 2006


This Issue Sponsored By




Shavlik Technologies


In This Issue


A Note from the Editor

Developer Perspectives

  • Snapshot Isolation vs. NoLock
  • eLibrary Short Takes

  • Cut to the Cache: Shortcut to ASP.NET 2.0 Performance
  • Achieve Compliance and Manage Threats Simultaneously
  • Tech Talk

  • Glad You Asked: Interested in a Programming Career? Here's How to Start
  • For 'um Developers
  • Letters to the Editor
  • Info to Go

    New and Improved

  • Localize Your Software in 3 Steps
  • Enter Frequently Used Text in a Flash

  • Sponsor: AVIcode

    Ensure successful application deployment, monitoring, and management when you share architectural and operational requirements. Learn how to use application models, use MOM to monitor the health of business applications, and explore a practical example of health modeling and monitoring application deployment. Download the free whitepaper now!


    A Note From the Editor


    You might notice that this newsletter looks a bit different. As Bill Sheldon described in his columns "Will .NET Survive" ( and "A Ripple Becomes a Wave" (, Developer .NET UPDATE is sporting a new name--Developer Update--and some new features. One notable new feature is the "Glad You Asked" column in which Bill will answer readers' questions. In addition to these changes, the newsletter will be published once a month (the first Friday of each month) instead of twice a month. This new schedule will start with the August 4 issue.


    Developer Perspectives

    by Bill Sheldon, [email protected]


    Snapshot Isolation vs. NoLock
    One of my least favorite features of Microsoft SQL Server is that it lets you apply a NoLock hint within a query. The NoLock hint tells SQL Server that, for the current query, you'd like to read the data from a given table while ignoring any locks. In systems in which there are multiple processes trying to simultaneously access the same resource, NoLock is advantageous because it allows user queries to operate without needing to wait for updates to finish. The disadvantage is that in many cases users read data that hasn't been committed to the database or is only partially in place, which can produce unexpected results.

    I usually find the NoLock hint in use by Web applications that are well behaved but the business tier has a custom-reporting capability, import capability, or both. For example, a common pattern is that in order to speed custom reporting, the system occasionally reads from five or six related tables, then inserts or updates all the data used to create reports into a single table. Rather than update the reporting table as changes occur on the source table, the developers thought they could speed the system by batching these updates. Similarly, for data import, there's one giant statement that takes a table or a file of data from another system and attempts to insert all the rows in a single SQL statement.

    The problem with batching updates and using giant statements is that SQL Server holds locks until the entire transaction completes. As a result, users' queries against the involved tables are suspended, making it seem like the application has frozen. The easy solution is to add the NoLock hint, which allows these queries to occur while these massive updates statements run their course.

    At this point, a quick review seems in order. When you're creating a transaction, you want to follow the Atomicity, Consistency, Isolation, and Durability (ACID) principle. Atomicity doesn't refer to an atom bomb but rather that a transaction should be processed completely or not at all. Thus, you should work with the smallest possible unit. For example, it's better to process one order and its accompanying set of order details rather than 10,000 orders and all of their accompanying details.

    Granted, NoLock usually lets organizations survive with few noticeable errors. However, I had been looking forward to SQL Server 2005 because it was supposed to provide an alternative to using NoLock. In SQL Server 2005, Microsoft introduced Snapshot Isolation. The basic idea is that transactions are held in suspense; thus, a long-running transaction (e.g., 10,000 orders) wouldn't hold locks during the transaction's update cycle, allowing reads to be done against clean data.

    Unfortunately, Snapshot Isolation has two problems that keep it from being a good replacement for NoLock. The first problem is that enabling the Snapshot Isolation option in a database requires an additional 14 bytes of space per row in every table. Given that most companies that have broken the transaction rules have also created some very wide tables, this space requirement can be a problem.

    The second and bigger problem is how this feature is implemented. Specifically, when data is written to a database, SQL Server 2005 writes all the data to a temporary table in TempDB instead of updating the actual database. When the transaction completes, SQL Server 2005 moves the data from TempDB storage to the database. This means that the system will likely need additional hardware to provide more I/O capacity.

    Unfortunately, I suspect that the majority of organizations that could really benefit from Snapshot Isolation won't be willing to pay the price to implement it. Of course, the real solution is to not have long-running transactions, but then everyone agrees with that in principle. For more information about Snapshot Isolation in SQL Server 2005, I suggest that you go to

    Sponsor: Thawte

    Test the Starter PKI Program to benefit your company with timesaving convenience and secure multiple domains and host names.


    eLibrary Short Takes


    Cut to the Cache: Shortcut to ASP.NET 2.0 Performance
    Whether you're an ASP .NET developer who works with data-based applications or a SQL Server DBA supporting ASP.NET applications, you'll find new capabilities in ASP.NET 2.0 to help you boost your Web site's performance. With the release of SQL Server 2005, Visual Studio 2005, and Microsoft .NET Framework 2.0, Microsoft continues to enhance its support at every tier of an ASP.NET application. Learn about the performance-boosting features that ASP.NET 2.0 introduces in "ASP.NET 2.0: Cache In on Performance Shortcuts " in the July issue of SQL Server Magazine.

    Achieve Compliance and Manage Threats Simultaneously
    Achieve compliance in today's complex regulatory environment while managing threats to the inward- and outward-bound communications vital to your business. Adopt a best-practices approach, such as the one outlined in the international information security standard ISO/IEC 17799:2005. Download the white paper today and secure the confidentiality, availability and integrity of your corporate information!


    Tech Talk


    Glad You Asked
    by Bill Sheldon, [email protected]

    Interested in a Programming Career? Here's How to Start
    Q: I read your "Back to Basics" article in Developer Update ( I've previously worked as a network/telecom/hardware/software support manager, but I'd now like to start a career in programming. I know almost nothing about programming except for some classes I took years ago. Can you suggest a starting point for me?

    A: Programming is more or less a generic task done by several different groups of IT professionals. So, I think the starting point for you is to look at some of the broad IT categories and see which one best fits your goals and expectations. The idea is to concentrate on an area in which you can be successful. So, let's look at four primary categories of IT professionals and what their skill sets are:

  • Database administrator (DBA). DBAs work with database systems, such as Microsoft SQL Server. They can focus on two areas: systems administration and programming. In the systems administration area, the tasks include setting up accounts and permissions for database users as well as monitoring a database's runtime performance. However, good DBAs go beyond just running a report. They optimize data access. For example, they evaluate stored procedures' execution plans, optimize stored procedures' code, and optimize databases' indexes. Good DBAs are proficient in the skills covered by the Microsoft Certified DBA (MCDBA) certification.
  • Traditional IT professional. The traditional IT professional is more of a system engineer. In large organizations, IT professionals might specialize in the setup and maintenance of a single server product (e.g., Windows Server 2003, Microsoft Exchange Server), whereas in small organizations, IT professionals might need to work with half a dozen or more server products. A talented IT professional not only knows how to open a user-management console to add a new user but also understands how to optimize products through administrative programming and other tools. Although the IT professionals' primary focus isn't programming, having some programming experience helps them be successful. These types of professionals become proficient in the skills covered in the Microsoft Certified Systems Engineer (MCSE) or Microsoft Certified Systems Administrator (MCSA) certification.
  • Information worker. Microsoft is promoting a new category of IT professionals known as information workers. At my company, information workers are specialists at customizing applications and client desktops. They typically customize products in the Microsoft Office suite. In Office 2007 that includes client applications such as Excel and server applications such as Office SharePoint Server. There are many certifications associated with information workers, so I'm not going to list them all but they include the Microsoft Office Specialist (MOS), Microsoft Certified Technology Specialist (MCTS), and Microsoft Certified Desktop Support Technician (MCDST). For more information about some of the training available to information workers, I suggest going to
  • Software developer. Software development is the traditional focus of programming. However, talented software developers do more than just write code. They also solve customer problems and design and document systems. In my opinion, a good software developer needs about 5 years of experience to be able to work independently. These developers are proficient in the skills covered by the Microsoft Certified Solution Developer (MCSD) and Microsoft Certified Application Developer (MCAD) certifications.
  • Taking into consideration these four categories of IT professionals, you should determine the type of development you want to pursue in your career. A good place to review the skill sets in all of the Microsoft certifications is at

    For 'um Developers
    Forum participant Gazzoid is performing full text searches in databases. He wants to whether it's possible to retrieve the name of the columns that the results were found in. If you can help Gazzoid, go to

    Letters to the Editor
    What's on your mind? Let us know at [email protected]


    Info to Go


    Are you protected company-wide against spyware, keyloggers, adware, and backdoor Trojans? Test the state of the art scanning engine that uses threat signatures from multiple sources to track down the culprits that antivirus solutions alone can't protect you against. Download your free 30 day trial of CounterSpy Enterprise today!

    Learn how a database utility for SQL Server can lower operational costs, simplify management, and increase the availability of your SQL Server deployment. Live event: Tuesday, June 20

    Take an up-to-date look at secure, remote access to corporate applications and stay ahead of the curve when making decisions about near- and long-term IT infrastructure. On-demand Web seminar.

    Gain control of your messaging data--and make your job easier--with these step-by-step instructions for complying with the law and ensuring your systems are working properly.

    Discounted Offer for the SQL Server Magazine Master CD
    Save 50% off the SQL Server Magazine Master CD! Order now and get portable, high-speed access to the entire SQL Server Magazine article database on CD--a searchable library that includes every issue ever published. The newest issue also includes BONUS SQL Server 2005 Tips. Subscribe now and save 50%:

    Save $80 off the Windows IT Security Newsletter
    Get endless solutions for building and maintaining a secure enterprise. Subscribe to the Windows IT Security newsletter today and save $80:

    Hot Spot

    Manage threats and vulnerabilities from adware and spyware in one console as a comprehensive approach to maximizing network security.


    New and Improved

    Send information about your developer-related products to [email protected]


    Localize Your Software in 3 Steps
    Sisulizer Ltd. has released Sisulizer, a Windows application that lets software developers localize their applications into multiple languages. Localization is a three-step process. First, you scan the application and locate all the text. Sisulizer works with Visual C++, Visual Basic (VB), Windows binary files, .NET assemblies, and more. Second, you translate the text with Sisulizer's visual editor. Third, you build the localized version. Sisulizer handles all languages, including right-to-left and double-byte languages. Sisulizer Professional Edition costs $1,499 and the Enterprise Edition costs $2,999. A Personal Edition is available for $299. For more information, go to

    Enter Frequently Used Text in a Flash
    Softvoile announced the release of Flashpaste 3.6, a customizable tool to enter frequently used text. It's easy to use. You just enter the frequently used text, then specify the cover string that will represent this text in the Flashpaste popup list. Then, anytime you need to enter your predefined text, you press a hot key to bring up the Flashpaste popup list and select the cover string from that list. You can also use Flashpaste to create macros. Flashpaste Professional costs $19.95. The company also offers Flashpaste Lite, a freeware tool. For more information, go to


    Contact Us



  • About Developer .NET Perspectives -- [email protected]
  • About technical questions --
  • 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.

    You can manage your SQL Server Magazine email newsletter subscriptions by going to

    You can view the SQL Server Magazine Privacy Policy at

    SQL Server Magazine is a division of Penton Media, Inc. 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

    Copyright 2006, 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.