chapter ten

10 Optimization

 

This chapter covers

  • Instance-level optimizations
  • Query optimizations
  • Table optimizations
  • Transaction isolation levels
  • Throwing hardware at performance issues

In this chapter, we will dive into mistakes and misconceptions that relate to the optimization of SQL Server performance. We will start by looking at instance-level optimizations, where we will discuss the use of deprecated trace flags, misconceptions around instant file initialization, and memory configuration mistakes. Mistakes can come in two broad flavors: failure to optimize and incorrect optimization.

Failure to optimize

I witnessed a failure to optimize recently. I was asked to look at an extract, transform, and load process that was taking a significant time to load data into a data warehouse. After examination of the process, it was clear that the team had not undertaken capacity planning (see chapter 9). While the disk had space remaining, the data files had not been sized to account for the large amount of data that was pumped into the database each night. That was causing the data file to expand in small increments, multiple times per night. Instant file initialization had not been enabled, which meant that the additional file space was being zeroed out every time, which is discussed in the second mistake in this chapter. Enabling instant file initialization immediately resolved the issue, but I also sent the team away to plan their capacity and resize the disk accordingly.

10.1 #54 Turning on TF1117 and TF1118

10.2 #55 Not using instant file initialization

10.3 #56 Failing to leave enough memory for other applications

10.4 #57 Failing to lock pages in memory

10.5 #58 Working against the optimizer

10.6 #59 Not taking advantage of DOP feedback

10.7 #60 Not partitioning large tables

10.8 #61 Not understanding the limitations of partition elimination

10.9 #62 Not compressing large tables

10.10 #63 Using Read Uncommitted

10.11 #64 Using unnecessarily strong isolation levels

10.12 #65 Not considering optimistic isolation levels

10.13 #66 Throwing more hardware at the problem

Summary