Q&A with Itzik Ben-Gan
Beware the NOLOCK Hint and Other Tips
The NOLOCK hint can cause headaches. Short-running queries might need tuning the most. And avoid cursors like the plague—except when they are the best choice.
These are just some of the tips that SQL Server MVP Itzik Ben-Gan will share in his Nov. 6 PASS Summit Unite 2009 post-conference seminar, “Query and Index Tuning for SQL Server 2005 and 2008.” In this full-day seminar, the T-SQL and query-tuning master takes attendees on a tour of SQL Server’s internal data structures and index access methods, safer alternatives to NOLOCK, the differences between set-based and cursor-based solutions, and more. PASS recently caught up with Itzik for a quick preview.
Q: What are the biggest mistakes or misconceptions you see in the world of query and index tuning?
A: One very common mistake I see is using the NOLOCK hint (equivalent to the READ UNCOMMITTED isolation level) in an attempt to provide a quick remedy to performance problems instead of doing a more careful analysis of indexing strategies and query efficiency. In many cases, people just don’t realize the full implications of using this hint. Most are only aware that a shared lock is not acquired, and therefore, the query may return uncommitted reads. But there’s more to it. This hint changes the decision-making process of the storage engine and may cause a query to return the same row multiple times or skip rows. I also see lots of misconceptions regarding when to use temporary tables vs. table variables.
Q: What’s the payoff to successfully optimizing queries and indexes? Why do you have a passion for this subject?
A: For the organization, the payoff is that they get more with less. Without good index and query tuning, you have to purchase more expensive hardware. But with good index and query tuning, you can often optimize queries so that they finish in a fraction of the time they did before optimization.
As for the tuner, you get immense satisfaction from being able to reduce the runtime and resource consumption of queries. There’s so much to learn and explore in the internals as well as in understanding the logical aspects of querying and how query plans scale. If you get into this subject deeply, it will never bore you.
Q: What are some of the biggest query and index tuning enhancements between SQL Server 2005 and 2008?
A: Microsoft added most of the important query and index tuning features in SQL Server 2005, such as the index INCLUDE clause, dynamic management objects, improvements related to temporary tables, and many others. In my seminar, I also cover some new SQL Server 2008 features, including filtered indexes and statistics; new information available through dynamic management objects, such as the new query_hash and query_plan_hash attributes for query statistics; and others.
Q: What challenges do SQL Server pros face in keeping their queries and indexes properly tuned?
A: One challenge is identifying the queries that need to be tuned. It’s not just a matter of tracing the slow-running queries. You might have queries that run for a short time but so frequently that they are more important to tune than other queries. Once you isolate the most important queries to tune, there are so many indexing options in some cases that you really need to know your stuff to come up with a good strategy. And even when you do come up with a good strategy, different queries that logically do the same thing could yield different performance. In the seminar, I explain how to isolate the queries that are most important to tune, how to tune them with a good indexing strategy, and how to further tune them with query revisions.
Q: How does your seminar give attendees the practical tools they need for query and index tuning?
A: We start by looking at internal data structures, understanding how tables and indexes are organized. Then, we move to index access methods. With this understanding, you can better interpret what you see in the execution plan and come up with good indexing strategies. Next, we discuss when to use temporary tables vs. table variables. As I mentioned, there are many myths surrounding these objects, and in this seminar, I hope to give attendees the right picture and the right tools to make the best choices.
Then, we turn to cursor-based solutions vs. set-based solutions, learning why set-based solutions tend to perform better. But I also discuss uncommon cases where cursor solutions provide better performance. And we end up with query-tuning techniques based on query revisions.
Hear more from Itzik and other SQL Server experts at PASS Summit Unite 2009 in Seattle, WA. See the lineup of pre/post-conference seminars, review the full conference agenda, and register today!
Itzik Ben-Gan—a Mentor and co-founder of Solid Quality Mentors—writes, trains, and speaks worldwide about T-SQL querying, query tuning, and programming. His latest best-selling books are Microsoft SQL Server 2008: T-SQL Fundamentals (Microsoft Press 2008) and Inside Microsoft SQL Server 2008: T-SQL Querying (Microsoft Press 2009).