Chapter 5 Missing Indexes

Indexes are essential for most databases, they provide fast access paths to the underlying data. Missing indexes are indexes identified by SQL Server as being useful, but have not been created. The chapter describes index design, in terms of covering the relevant queries, the importance of column order, and the use of INCLUDEd columns. The impact of indexes is illustrated by running some queries with the AdventureWorks database, while this is useful, it would have been nice to see some output results too. No comparison is made between covered and included indexes.

Missing indexes can be discovered by running the Database Engine Tuning Advisor (DTA) and also by inspecting the missing indexes DMVs. Each method has advantages and disadvantages. It is also possible to search the plan cache for missing indexes, this has the advantage that you know how the missing index is linked to a query. It should be noted that missing indexes on foreign keys is quite a common problem, some code is given to identify these, but it is limited, better scripts exist elsewhere. Code is also given to identify unused and duplicate indexes.

The idea behind using DTA is you have your current database structures (heaps, clustered and non-clustered indexes), and a workload (e.g. a trace file). The workload can be a single SQL Statement or all the SQL that runs against the database. DTA analyses the workload and proposes what indexes should be created (and dropped!). Actually the DTA has a great many useful features (e.g. partitioning), and I recommend you explore it.

The missing indexes DMVs are perhaps easier to use and you get the results quicker. They are good for identifying the more obvious missing indexes, but have more disadvantages than using DTA (e.g. column order is not specified in the proposed index).

This chapter provides a very good overview of how to identify missing, unused, and duplicate indexes. Useful code is supplied, and the approach taken is methodological. In many ways this chapter could be read standalone. Unusually, no detail of the related topic of index statistics is given here.

Chapter 6 Blocking

In many ways, fixing blocking issues requires a trade-off between consistency and concurrency. The chapter starts with an overview of locks and concurrency, covering lock modes, types, escalation, and the impact of transaction isolation level. Also briefly covered is the related area of latches and latch contention. Various methods of monitoring blocking are discussed including using sysprocesses, wait stats, DMVs and PerfMon. The section on the automatic detection and notification of blocking is particularly good, and the code given for using Event Notification should be very useful in identifying real time blocking. The coverage of Extended Events is also good, and there’s a link to the author’s extremely useful blog entries.

Common causes of blocking are given (and solutions proposed). These include:

• Bad database design e.g. denormalization on OLTP system

• Inappropriate isolation level

• Poorly written queries e.g. long transactions

• Missing indexes

• Poor application design e.g. retrieving too much data

• Old hardware

This chapter is relatively short, but covers all the salient areas, and has many useful links to extended topics for greater depth.

Chapter 7 Handling Deadlocks

Deadlocks are due to a conflict in resource usage that cannot be resolved with time (unlike blocking), so SQL Server chooses to rollback one of the queries. The victim depends on the DEADLOCK PRIORITY value, if this is equal, then it depends on how much work is required to rollback the transaction. The lock monitor checks regularly (every 5 seconds?) for deadlocks.

Various methods of obtaining deadlock information are discussed, including trace flags 1204 and 1222, SQL Profiler XML deadlock graph event, event notification, WMI, and extended events. Typically the method you choose will depend on the version of SQL Server you have, and what features are implemented. I would suggest, if you’re using SQL Server 2008 or higher, you use Extended Events, it is by far the easiest method of getting the deadlock details.

There is some very useful sample code in this chapter (the event notification code could be very useful for real time notification of deadlocks). And there are some very good links to other related areas, especially Bart Duncan’s excellent blog entries that explain the content of the deadlock output.

Common types of deadlocks are given (and solutions proposed). These include:

• Bookmark lookup

• Range scans caused by SERIALIZABLE isolation level

• Cascading constraints

• Intra-query parallelism

• Accessing objects in different order

The chapter ends with code examples provided in both SQL and ADO.NET that detail how you can automatically recover from a deadlock (i.e. capture error 1202, wait a while, retry – repeat a few times). I’m surprised the use of READ COMMITTED SNAPSHOT wasn’t mentioned as a method of reducing deadlocks. This chapter covers a lot of ground, over multiple versions of SQL Server, providing useful sample code, and excellent links for further reading.

The book now switches to some common admin related problem areas, namely large or full transaction logs, and database accidents. Up until this point, this book has felt like a performance tuning book. It made me wonder if the next two chapters are in the wrong book, or maybe the book title is inaccurate.