Database Reference
In-Depth Information
Excessive Use or Improper Configuration of tempdb
There is only one
tempdb
for any SQL Server instance. Since temporary storage (such as operations involving user
objects such as temporary tables and table variables), system objects such as cursors or hash tables for joins), and
operations including sorts and row versioning all use the
tempdb
database,
tempdb
can become quite a bottleneck.
All these options and others lead to space, I/O, and contention issues within
tempdb
. I cover some configuration
options to help with this in Chapter 3 and other options in other chapters appropriate to the issues addressed by
that chapter.
Summary
In this introductory chapter, you have seen that SQL Server performance tuning is an iterative process, consisting
of identifying performance bottlenecks, troubleshooting their cause, applying different resolutions, quantifying
performance improvements, and then repeating these steps until your required performance level is reached.
To assist in this process, you should create a system baseline to compare with your modifications. Throughout the
performance tuning process, you need to be objective about the amount of tuning you want to perform—you can
always make a query run a little bit faster, but is the effort worth the cost? Finally, since performance depends on the
pattern of user activity and data, you must reevaluate the database server performance on a regular basis.
To derive the optimal performance from a SQL Server database system, it is extremely important that you
understand the stresses on the server created by the database application. In the next two chapters, I discuss how
to analyze these stresses, both at a higher system level and at a lower SQL Server activities level. Then I show how to
combine the two.
In the rest of the topic, you will examine in depth the biggest SQL Server performance killers, as mentioned
earlier in the chapter. You will learn how these individual factors can affect performance if used incorrectly and how
to resolve or avoid these traps.
