This Issue Sponsored By
In This Issue
A Note from the Editor
eLibrary Short Takes
Info to Go
New and Improved
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" (http://www.sqlmag.com/Article/ArticleID/49049/sql_server_49049.html) and "A Ripple Becomes a Wave" (http://www.sqlmag.com/Article/ArticleID/49304/sql_server_49304.html), 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.
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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp.
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!
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 (http://www.sqlmag.com/articles/index.cfm?articleid=49610&). 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:
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 http://www.microsoft.com/learning/mcp/default.mspx.
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:
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
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 http://www.sqlmag.com/email.
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.