- 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