Index fragmentation effects on performance

Before jumping into index fragmentation I’ll first explain what indexes are and how they are used by the SQL Server Engine. I tried to compare it to a real world way of searching data, to paint a clear picture in your head. I’m not going into the differences between clustered and non-clustered indexes as that would be a nice new blog. How do indexes work? Indexes are used by SQL Server to search for records in tables as we use indexes in books to get to certain chapters/words/authors etc. The way an index in SQL Server works is similar to the way we lookup a phone number in a (old fashioned and hard copy) phonebook. We decide we need a phonebook for this search not a cookbook. We pick up the phonebook of the state we need. We look at the index to find the pages that have the correct city. We go to the page that contains the first letter of the last name of whose phone number we need. We find the last name and see that there are in fact several. We look at the address and find the right number. SQL Server will do the same […]

The post Index fragmentation effects on performance appeared first on Kaya Consulting | Microsoft Dynamics AX.