May 2022
Intermediate to advanced
497 pages
13h 4m
English
Content preview from SQL Server Advanced Troubleshooting and Performance Tuning
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access
More than 5,000 organizations count on O’Reilly
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.Julian F.
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.Addison B.
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.Amir M.
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.