Top 4 Reasons to Study Internals
If you still aren't convinced that you need to know anything about SQL Server internals, here are my top four reasons why you should learn about them:

4. You can determine when a behavior is normal and when it's a bug. Here's an example in SQL Server 2000's Northwind database. Look at the plan for this query: 

SELECT * FROM [Order Details] 
WHERE ProductID in ( 9, -9)

The query plan shows a nonclustered index on ProductID being used to find the relevant rows.

However, this query, which returns the same rows, doesn't use the index on ProductID. Instead it uses a clustered index scan, and if you have a basic understanding of index internals, you'll know that a clustered index scan is a table scan: 

SELECT * FROM dbo.[Order Details] 
WHERE abs(ProductID) = 9

This query also uses a clustered index scan: 

SELECT * FROM dbo.[Order Details] 
WHERE ProductID in ( 5, -5)

There are two different reasons for the nonuse of the nonclustered index on ProductID, and they're both valid. That is, both are normal and expected behavior. Once you understand how indexes are organized and how SQL Server decides to use them, you'll be able to expect it, too, and make the best choices in writing queries and designing indexes.

3. You can determine whether a problem is really a bug. I've talked about covered queries in several of my columns. A common place to see them is in simple aggregate queries, such as this one in the Northwind database: 

SELECT avg(UnitsInStock) 
FROM dbo.Products

Suppose you've built a nonclustered index on UnitsInStock (because the Northwind database doesn't come with this index). SQL Server wouldn't have to touch the data pages at all.All the data needed to compute the average is in the index. So what would you do if the plan for the previous query did a table scan and didn't use the nonclustered index? I have a table in one of my databases, and a query exactly like the previous one, which returns the average on a column with a nonclustered index and doesn't use the index unless I add this WHERE clause: 

WHERE Quantity >=0

This condition is actually meaningless because every row in my table has a value for Quantity greater than or equal to 0, but I still need to add the WHERE clause in order for SQL Server to use my nonclustered index on Quantity. This is a bug.The more you know about what SQL Server should do, the faster you can tell whether it's really doing the wrong thing and avoid wasting time trying to fix it yourself.

2. You can explain your decisions with confidence. If you've been working with SQL Server for a while, you've probably learned from experience what works and what doesn't in many cases. But what do you do if your boss asks you to explain your reasoning or brings in an outside consultant who recommends a different solution. Can you explain why your ideas are valid?

A student from one of my recent classes sent me an email message after the class was over. He said that before he took the class he'd actually suggested to the database designers that they change their clustered index to columns other than the primary key. They ignored him. After taking the class, he was so sure he was correct, he told the engineers again that he thought they should change some of the clustered indexes.The designers didn't know he had just taken the class, but this time they took his advice. He felt he was more confident because he knew that he could explain his decision if asked about it.When you know what you're talking about,people listen to you.

1. You can get your own answers to questions. How can you find out everything you've ever wanted to know about SQL Server? Not by reading this one article, or even by reading one book. After you start developing your own expertise, you'll find that your knowledge will grow exponentially. The more you know, the more quickly you can find the best sources of information. You can describe your problems more precisely when you post them in a public Help forum. And you can quickly develop tests or write queries by using SQL Server metadata to provide even more information about what your SQL Server system is really doing.

End of Article

Prev. page     1 [2]     next page -->



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Hi Kalen,

Thanks for your advice about us learning about internals.

I ran these two queries:

SELECT * FROM [Order Details] WHERE ProductID in ( 9, -9)

SELECT * FROM dbo.[Order Details] WHERE ProductID in ( 5, -5)

and both query plans show a nonclustered index on ProductID being used to find the relevant rows.

I don't see why one would use non-clustered indexes and the other one a cluster index scan.

Thanks for your time and your teachings.

Alex

johnalexlopez- November 02, 2006

Article Rating 5 out of 5

Hi Alex

I'm sorry for the delay in responding.

It turns out that slight differences in your configuration might make SQL Server's optimizer choose different plans for these queries. I tested it on several different instances to confirm the behavior I was seeing, but apparently your machine was different. It's also possible that you have performed other testing in the Northwind database to change the values in the Order Details table.

Explaining the reason for the possible difference in behavior of these two queries was actually not the purpose of this article. In the article I was just pointing out that understanding this behavior would be one of the benefits of understanding how SQL Server really works.

I have written numerous articles on indexes, and the difference between clustered and nonclustered indexes, and what kinds of queries each is good for. I invite you to search the archives of SQL Server Magazine for my past articles on the use and behavior of indexes to get many more details.

However, the short answer is that nonclustered indexes are only useful if the optimizer determines that only a few rows will satisfy the query. One of these queries returns more rows than the other, and the optimizer determine that one of the queries returned to many rows to use the nonclustered index effectively and decided it would be more efficient to just scan the table.

Thanks for writing! Kalen Delaney

KDelaney- January 12, 2007

Article Rating 5 out of 5

I like the approach and style to problems/issues/subject. I have been a DBA for 10 years now and I love to know the internals,understand and apply them to resolve the issues.

dharmanambi- September 17, 2007

Article Rating 4 out of 5