Chapter 7. Memory Issues

Probably the first thing to note about SQL Server is it likes to use lots of memory – this reduces both IO and recompilations. 

The chapter opens with a look at memory usage and configuration. Memory usages can be seen via various Performance Monitor counters, DMVs, and the system_health XE, details of each are provided. There’s a very useful calculation for ‘max server memory (MB)’, but be aware of other software on the box that will also need some memory (e.g. SSRS). The author notes that adding more memory is often the fastest and cheapest way to improve performance, since memory is relatively cheap. Defragmentation and compression can also improve memory usage.

Next, there’s a very useful section on memory clerks, these are very useful for analyzing the different types of memory usage. For example:

• memoryclerk_sqlbufferpool – buffer pool memory, usually large
• cachestore_phdr – internal objects used during query compilation
• cachestore_objcp – stores compiled execution plans for stored procedures, functions etc
• cachestore_sqlcp - – stores compiled execution plans for ad-hoc queries, often CPU intensive

Code is provided to calculate how much space each memory clerk has. The value of cachestore_sqlcp should be small compared with memoryclerk_sqlbufferpool, else it suggests there are many plans for ad-hoc queries, and Forced Parameterization can help reduce the memory usage and improve performance. 

There’s a useful section on query execution and memory grants. Sometimes the optimizer calculates a bad value for memory to use for the query, this can result in spilling to tempdb with an associated decrease in performance. Later versions of SQL Server have a feedback mechanism to help correct this.

The chapter ends with a look at in-memory OLTP memory usage. It’s noted that in-memory will go readonly if there isn’t enough memory. Ideally, in-memory will keep only the latest data, and historical data can be archived off onto disk, and both in-memory and disk data can be joined via views – details of this are provided in the author’s other book concerning in-memory OLTP.

This chapter contains plenty of code to support the author’s discussions, and as always, there are plenty of useful incidental tips.

Chapter 8. Locking, Blocking, and Concurrency

Locks allow data consistency. This chapter focusses on disk-based b-tree tables. It opens with a look at the different types of lock, and the major ones are discussed (e.g. X, S, U, IS, IU, IX). There’s a helpful look at lock compatibility (e.g. exclusive locks are incompatible with other locks), before moving on to how transaction isolation levels impact locking behavior. There’s a useful recommendation about using READ COMMITTED SNAPSHOT instead of READ UNCOMMITTED (or NOLOCK), however this uses tempdb, so you need to monitor tempdb usage. 

Next, the chapter moves on to blocking issues, where multiple sessions want the same resource and the locks are not compatible. Often the problem is due to inefficient queries acquiring locks unnecessarily – the author gives some useful examples of this, together with some solutions. There’s a very good point about the statement that you see causing the blocking may be incorrect, since the lock may have been acquired earlier. There’s a good overview of the Blocked Process Report, and the author extends this with his own Event Notifications and Blocking Monitoring Framework.

Deadlocks are examined next. These are special cases of blocking, where SQL Server terminates one of the sessions. Perhaps the author could have added that deadlocks are blocking where time will not solve the problem. There’s an excellent look at troubleshooting deadlocks, together with suggestions on how to resolve them. Often, adding a non-clustered index can fix deadlocks. It might have been useful to include code that decodes the deadlock.

The chapter continues with a look at optimistic isolation levels, here old versions of the data that is being modified is stored in tempdb, and other sessions can read this, avoiding blocking. Again, there is a need to monitor tempdb usage. The optimistic isolation levels READ COMMITTED SNAPSHOT and SNAPSHOT are discussed, highlighting their differences. 

The chapter ends with a look at some of the more common lock wait types, together with suggestions for the waiting - often it is due to suboptimal queries.

This chapter looked at a very common area of concern. Useful code was provided to illustrate blocking and deadlocks, together with some very helpful solutions. For additional deeper information, it’s suggested you read the author’s book on concerning transactions and locking.

Chapter 9. tempdb Usage and Performance

The tempdb database is typically used by all the databases on the instance, it’s also used by SQL Server itself. If there are problems with tempdb, it can affect all the other database on the instance.

The chapter opens with a discussion of temporary objects, namely temporary tables, and table variables. Best practices for the objects are highlighted (e.g. take advantage of indexing on temporary tables – but be sure to test they are effective). There’s a useful point about using temp tables to break down complex queries, with the benefit that temp tables have automatic statistics created. 

Next, there’s a look at some internal tempdb consumers, including version store and spills. Tempdb can be used to store previous versions of rows that are currently being updated, this allows SELECTs to run without blocking modifications. Sometimes, the calculation for the size of memory required by a query is incorrect, this can lead to spilling data to tempdb at runtime, solutions to this are examined.

Some common tempdb issues are discussed, with system page contention being a typical problem, solutions to this are given (e.g. enable trace flag T118). Another issue can be the running out of space, so monitoring is needed.

The chapter ends with some useful tempdb configuration recommendations. These include putting tempdb on a separate fast drive, and create multiple data files (of the same initial size). A rule of thumb is provided to calculate the recommended number of tempdb data files. 

Since tempdb is shared by all the databases on the instance, and used by SQL Server itself, it’s important that it is optimized. This chapter provides a look at the common problems together with useful solutions.