SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 14. Database Schema and Index Analysis

Until this point, most of the troubleshooting efforts described in this book have treated users’ databases and applications as black boxes. I’ve focused on performance improvements that do not require any changes in the databases and applications beyond indexing and simple T-SQL code changes. This approach provides easier and faster ROI; however, it also limits the results you can accomplish.

Don’t take this the wrong way: in many cases, you can achieve good enough results without needing to make significant database and application changes. Nevertheless, it may be beneficial to perform a high-level review of your database schema and index usage and address some of the problems you find.

I’ll start this chapter with an overview of several SQL Server catalog views and show you how to detect a few database design issues. Then I’ll demonstrate how to identify inefficient indexing through analysis of index usage and its operational statistics and provide a handful of scripts for this analysis, including one that lets you view at a glance several consolidated index metrics.

Database Schema Analysis

SQL Server provides quite a few catalog views that expose information about server- and database-level objects. They are extremely useful when you need to analyze and detect inefficiencies in the database schema.

Figure 14-1 shows several database object–related catalog views with their dependencies and key attributes. This is just a small ...