As many of you know, I am passionate about healthy SharePoint Databases because of the huge difference it makes in the performance of your environment. A lot of times, people focus all their attention on the Web Servers and content, but leave the SQL Server to manage itself or pay little attention to it. Well, I wanted to explain how SharePoint stores it’s data and I thought this would help in better understanding why SQL Maintenance is so important when it comes to SharePoint.
Fundamentals of SQL Database Structures
So, let’s break down a SQL Database and the components of what makes up a database and how the data is stored. Whether we are talking about a .MDF or a .NDF, all SQL Data is logically divided into pages which are numbered 1 to infinity. Each page contains 8KB of data in it. A page can contain a variety of data types from Index data, BLOB data, or standard structured content.
What does this mean? Well, any disk intensive operations are going to occur at the page level whether we are talking about TEMPDB or a Content DB. That is just the way it works.
So, what does this mean with SharePoint? When you create a new List-Item in SharePoint, it is stored in the appropriate List-Item table in SQL down to an individual page. You can only store so many rows in an 8KB page, so depending on the type of list item, it may be stored across one or multiple pages. If the data is contained across multiple pages, it is referred to as a page-split.
So, from pages, we move to a term called an Extent. An Extent is a group of 8 Consecutive Pages and is stored in a 64KB block. Within this Data, you are going to have an Index that outlines what is in the Extent.
In review, your structure goes like this. ROW–>8KB Page–>64KB Extent.
Fragmentation occurs when data is deleted from rows or when individual pages are deleted and new data is added. Data may no longer be stored in sequential order and becomes fragmented obviously causing more IO to retrieve said data.
SharePoint Table Structure
MSDN has a great breakdown of all SharePoint Database Tables for the Configuration Database and a Content Database which can be found here: http://msdn.microsoft.com/en-us/library/dd587562(v=office.11).aspx
Using the knowledge above, this better helps us understand where data can be found when you put in a list, a web part, or some other piece of SharePoint data. Let’s look at a Content Database Diagram that I was able to find here:
So, if you store a new calendar item in SharePoint, not only is that individual list item putting data into the pages of the AllUserData table, as well as several other tables to reference that entry.
Now, Microsoft is very clear that you void support when you begin to run queries against or alter SharePoint Databases. As I have stated before, there are a number of maintenance jobs within SharePoint that help keep your databases healthy. However, I thought this article would help you better understand how the data is stored and where fragmentation results from.