Like This Blog 0

Added by Jeff Jones November 8, 2011

Interface Technical Training hosted a very special Advanced T-SQL course with SQL MVP Itzik Ben-Gan on October 17-21, 2011.

We sat down with Mr. Ben-Gan to get his insights on various topics related to Transact SQL. In this video, Itzik discusses how to optimize queries in your SQL Server Database.

Below is the transcription of the video.

“You need to understand how the SQL Server engine works. The language is logical and the standard language doesn’t care about the performance side of the query. Therefore, when the standard SQL describes a query construct, there is no standard way to optimize the query execution within any database platform. It’s the platforms responsibility to create a physical execution plan to process the query. Different database platforms can use different ways to process the query. Therefore, you need to be very familiar with the specifics of the platform you’re working with. There are some common physical structures that look similar in different platforms but there are also some unique structures and behaviors that are platform-dependent in terms of optimization.

People like to use ‘rules-of-thumb’. Statements like “use joins instead because they work better than sub-queries”. But reality can be different. The idea in query tuning; beyond gaining a lot of knowledge and experience with a particular platform is understanding what it does well and doesn’t do well. You must keep an open mind and try to reach solutions from your experience that tend to perform well. While expecting the fact that certain circumstances that you’re not thinking of could make the solution not perform well. Then understanding what you can do to help your code perform better both in terms of physical structure (such as indexing) as-well-as how you write the code.

So it could be that within the same physical environments, you write 5 different solutions and each of them will get a completely different plan. So you need to understand why even though you’re writing two different queries that logically achieve the same result, why is it that the engine can produce different plans and identifying the differences in the plan can lead you to the correct adjustments and get to the query that will perform better.

There is a lot to learn about the different query iterators or the query plan iterators and understanding the different types of access methods such as ordered scans, unordered scans, seek operations, look-ups, those that go against heaps and those that go against B-tree indexes. There are many different access methods as well as other kinds of iterators that you can find in the plan and understand how to analyze them, identify the more expensive ones and then figure out ways to avoid them.”

Upcoming live Microsoft SQL Server classes at Interface Technical Training:
SQL100: Introduction to Transact-SQL
SQL250: Transact-SQL for Developers
SQL400: Itzik Ben-Gan’s Advanced T-SQL Querying, Programming and Tuning for SQL Server 2005, 2008 and 2012
6231R2: Maintaining a Microsoft SQL Server 2008 R2 Database
6232R2: Implementing and Maintaining Microsoft SQL Server 2008 R2 Database
BI6234: Implementing and Maintaining Microsoft SQL Server 2008 Analysis Services
BI6235: Implementing and Maintaining Microsoft SQL Server 2008 Integration Services
BI6236: Implementing and Maintaining Microsoft SQL Server 2008 Reporting Services

Is there a DrillBit™ Video you would like to see?
Let us know in the comments section below. If it’s a popular technical problem, we’ll make a DrillBit™ video with the solution.