Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Because your indexes will get updated whenever there is a change to your main table, if there is an insert, we have to change the index, if there is an update, we change the index, if there is a delete, we change the index. Or your indexes can become what's called fragmented. They can become inefficient and split across multiple parts of the files that SQL Server uses to organize itself. Well, we can find out if that's an issue. Though I will tell you in a typical production database, it will be an issue and you want to defragment, even rebuild your indexes from time to time.
Luckily, in SQL Server Management Studio, I can right-click any of my databases, come down to Reports and I will about find that in the Standard Reports section there's both an Index Usage Statistics, how much of these index is being used, and Index Physical Statistics. Both of these are great. If I click on Index Usage Statistics, it will tell me and this is going to be fairly recent information, how my indexes are being used. There's my new one that I created a moment ago. The index for the last name of the customer has been used a whole one time.
Well, of course, we are on a development server. I wouldn't expect to see huge amounts but it would let us know if a particular index is or is not being used. Doesn't tell us whether it's fragmented though. For that we need the other report. And to come down into Reports > Standard Reports and Index Physical Statistics, which will scan all the indexes of all the tables, clustered and non-clustered, and you will often see a recommendation here to Rebuild and an index needs to be rebuilt when it's above a certain level of fragmentation.
Now here's the issue. It's telling us that most of these indexes have to be rebuilt. The problem is, is this database and all its indexes are so small, so very, very tiny. There are only a few hundred rows in them, that these indexes will almost always show up as fragmented. Let's see if we can find something that's a little bit bigger. I am going to drop into the AdventureWorks Data Warehouse, AdventureWorksDW. Even though that's still technically a very small database, it's bigger than AdventureWorksLight. So I am going to go over into Standard Reports and look at our Index Physical Statistics.
And as I scan, I see some recommendations. One says Rebuild here, the fragmentation's at 50%. Another says Reorganize, the fragmentation's at 6. It's still not a very big index. I can tell here that it's says this. The number of pages this indexes made of is 32 and the pages only an 8K chunk of how SQL Server organizes itself. So there's still not a lot going on. I am going to come down a little bit further. And let's say here we have got FactResellerSales as one of the tables in this data warehouse.
If I expand that, it says it's recommending a rebuild on this FactReseller_Sales EmployeeKey- NonClusteredIndex. That's a 43% Fragmentation with 113 pages. We might see a difference here. So how do I do this? Well, I need to find the indexes for this table, FactResellerSales. So I will expand the Table section in Management Studio. I will find FactResellerSales. I will expand that and inside the table, we're mostly been working with Columns and Keys but there is an Indexes folder which shows me all those indexes.
I could find the FactReseller_Sales EmployeeKey, which is the one I'm looking at here, and I could right-click that and I have a choice to Rebuild or Reorganize. I can also right-click the Indexes folder and Rebuild All or Reorganize All. What's the difference? Well, If I say Reorganize, what it's going to do is take some of those pages, those little 8K chunk that are been used to store this index and shift them around in a bit of a better order. Reorganizing is quite quick and we will get you some of the way there.
If I say Rebuild, it actually dumps the index, completely drops it and rebuild it from scratch. A much better way of getting a better index but it will take a while. In fact if you have a very large database, it may be hours or even for terabytes of data, days to do this. This one shouldn't take anywhere near as long. I am going to right click this folder and say Rebuild All. What will happen is its scans all those Indexes, tells me the fragmentation, that we really don't have anything apart from this last one. But I am going to click OK anyway.
It bangs through all of them and just rebuilds those indexes. So, let's see if we can get a better report for FactResellerSales. I will run the report again, come to the Database, right-click, go to Reports > Standard Reports > Index Physical Statistics, and then come all the way down to FactResellerSales and I am not being recommended anymore to rebuild it. If I expand that one, it looks actually pretty good. We have a 0% fragmentation across 113 pages, nice.
Now typically, what you're going to look at when you get into index defragmentation and this will be part of your overall maintenance plan that you and/or your DBA are going to help build and create. Like a lot of other things when using Management Studio, if you are rebuilding all your indexes, you do have the opportunity to script that to a new query window and it will actually generate the SQL to rebuild those indexes if you want to. And that might be a starting point for creating say a larger SQL script that will execute a maintenance plan, the maintenance structure on your entire database.
When you're getting into that, you certainly want to go out to the Internet and start looking at some blog posts and finding some good routines. Because you'll find that certain people gather up their suggested scripts and make them available for general plans for database maintenance. But for us on our development server, this is how we approach monitoring and defragmenting our indexes.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.