My special area of interest and focus within the huge product that is SQL
Server is the product's internals. I want to know how SQL Server does what it
does, and I want to be able to use that knowledge to get SQL Server to do things
better and faster. Furthermore,I want to know all this without having to read
the source code. Knowing about SQL Server internals has given me this knowledge,
which for the past seven years I've shared with readers to help you improve
your own SQL Server installations.
How I Got Hooked
I first became interested in internals during my life as a technical support
engineer for Sybase, about 18 years ago.The Sybase database engine, as you might
know, was the original source for the Microsoft SQL Server product. I'd been
providing technical support for Sybase customers all over the world, on all
aspects of the product, after having had the bare minimum of training. After
a year of fumbling my way through complex, but often fascinating database-performance
issues, I attended a "Performance Tuning and Optimization" course through Sybase's
education department. I learned an enormous amount of information that week,
but my emotions as I sat through the course ran the gamut from elation to frustration.
I was delighted to learn so many details about what the database engine was
actually doing, but frustrated and at times even angry that I hadn't been given
this information when I first started supporting customers. As I thought about
it further, though, I realized that the concepts in the course wouldn't have
made sense to me if I hadn't already had experience with some of the problems
that I could use the course information to solve. In other words, I wouldn't
have understood the point of learning details about the product's internal workings
unless I could relate what I learned to specific situations I had encountered.
At the end of the week, my overwhelming feeling was of excitement. I realized
that it was possible to become an expert in this product, and I made it my goal
to become one.
Fleeting Expertise
A few years later, I left Sybase and started working with the Microsoft database
product. I started teaching the same course, and I continued my quest to learn
everything I could about how the database engine really worked. By the time
SQL Server 6.5 was in its full flower, I felt like an expert. But that feeling
was short-lived; Microsoft introduced SQL Server 7.0, and I was back to square
one. It seemed that everything about the internals changed in 7.0, including
the structure of indexes, the possible query plans that the optimizer could
devise, and the way that locks were acquired and held. In addition, the product
became so much bigger with the introduction of DTS, merge replication, the beginnings
of data warehousing and analysis services, and new programming interfaces such
as ADO and OLE DB. I did find a renewed excitement in learning new concepts
and behaviors, but I decided that I couldn't continue to be an expert in the
entire product. I decided to specialize, and my focus became the core engine,
in particular locking issues, indexes, and query optimization. I also specialize
in SQL Server's metadata, which provides information about locking and indexes,
as well as the metadata that describes the physical storage of the data and
indexes.
Around that time, I was invited to update Ron Soukup's Inside SQL Server to cover version 7.0, and I also started writing this monthly column. Although this more public exposure has let me meet many people who have similar interests, I've also met many people who maintain or develop large SQL Server systems who profess to have no interest in, or need to know about, SQL Server internals. So I ask them, why not?
Why Not Internals?
I propose a quiz, such as one you might find in Reader's Digest or the
Sunday newspaper supplement, to help you assess whether you're a prime candidate
to learn about internals:
- Has an application or query performed well sometimes, but not always, and
you couldn't figure out why?
- Has SQL Server seemed to run low on memory, although your system actually
had plenty of memory?
- Have you ever encountered a SQL Server error message that you didn't understand,
which contained the phrase Contact your system administrator—and
you are the sys admin?
- Has an application performed perfectly in your development and testing environments,
but when you tried to run it in production with dozens or hundreds of users,
essential queries and processes seemed unable to run to completion?
- Do you schedule maintenance jobs for your SQL Server system by using the
Maintenance Wizard and click Yes to questions about optimization and consistency
checks, without really knowing what steps SQL Server would actually perform
when running the job?
- Do you manually defragment or rebuild your indexes regularly without verifying
that doing so is really necessary?
- Have you added indexes to try to speed up queries, then found that your
data-modification operations are actually much slower?
- Have you ever followed someone's advice to change a database's recovery
model without knowing what the full impact of the change would be?
Can you answer yes to any of these questions? If so, learning about SQL Server internals would be well worth your while.
Even if some aspects of SQL Server internals might not seem immediately valuable, that information might help you solve a performance problem sometime. For example, you might read one of my articles about database-recovery modes and how SQL Server uses the transaction log differently in each mode.You might run scripts to perform data-loading operations and index maintenance that work perfectly fine now, so you don't find the relationship between recovery modes and the kinds of work you do in your database to be particularly relevant. But if someday you find that your log has grown too big for the disk on which it resides, and you find that efforts to shrink it don't seem to work, a good knowledge of exactly what's logged in each mode might be useful for troubleshooting.
Prev. page
[1]
2
next page 
