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.

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