From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Unlock this course with a free trial

Join today to access over 25,300 courses taught by industry experts.

Implement index maintenance tasks

Implement index maintenance tasks

From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Start my 1-month free trial Buy for my team

Implement index maintenance tasks

“

- Welcome back, this is lesson 9.1, implement index maintenance tasks. Index fragmentation is when there's too much free space on the page. Whenever SQL Server does an insert update or delete on a database, the associated index is also modified. So let's back up a bit. In an earlier video, we mentioned that tables are made up of 8K pages. These pages are made up of rows of data, and when SQL needs to add a row to a page which doesn't have any space available, it will split the page. When page splitting happens frequently, the pages become fragmented, ultimately resulting in diminished performance. The same happens if there's a lot of deletes. Now you have pages with gaps in them, this is also referred to as page density. A page that is completely full has a hundred percent page density. When that page is split into two pages in order to insert a row, both pages have roughly 50% page density. The lower the page density, the more pages SQL has to read. Now you can tell the level of…

Contents