New SelecTViews Show
Meredith Ryan Smith on the show today, plus the latest news for the database world. Also, what do fingers have to do with injection and SQL Server? Find out on the show…
[Watch the Show]
Also: Watch last week’s show here
Index Defragmentation
Indexes are one of the key components impacting the performance of a relational database. You have to have the right indexes addressing the patterns of data maintenance and retrieval. Just having the right indexes is not enough, however. The efficiency of an index degrades over time. This is a problem occurring in any relational data engine…not just SQL Server, which I will be using as today’s example.
Most of us are familiar with the need to defragment our hard disk. We sometimes have scheduled tasks that run when the computer is idle, moving files around on the disk so that the contents of a file are stored in sequential order on contiguous locations of the physical disk. This improves performance for file retrieval because the head on the disk does not have to physically move to get to the next portion of a file.
SQL Server database files benefit from disk defragmentation. For this reason, it is best to pre-allocate space in your database files so that it doesn’t have to be grown over time, causing the file to be scattered between spaces allocated for other files.
Internally SQL Server also has the behavior of fragmentation. Fragmentation inside SQL Server is at the table and/or index level. Fragmentation within a table occurs as records are inserted, updated or deleted. As space is required for data, SQL Server locates the next open location, allocating the necessary space. The process is more complicated than that based on the existence of a clustered index on a table. But you get the basic picture.
Unlike having a defragment utility for your files, SQL Server defragments table through the use of a clustered index. As you may know, a table may have a single clustered index. That clustered index differs from all non-clustered indexes in that the actual data for the table is stored in the leaf nodes of the clustered index. Since the data can be stored in only one location, you may only have one clustered index on a table.
If you re-organize that clustered index, the index is defragmented, placed in sequential order, in sequential locations within the database file on which the clustered index resides. Because the data also resides physically within the clustered index, it also is defragmented.
On a server containing large tables having more data than server cache, de-fragmenting your data increases performance in a dramatic way.
Next time we’ll talk about some of the ways you can re-organize your SQL Server indexes. You can’t defragment your data without impacting database consumers. We’ll look at some of the methods available to you in different versions of SQL Server.
If you have any tips you’d like to share for any other data engines, feel free to send those in as well. I’d love to post your comments.
Readers Comments Regarding Index Maintenance Processes
Joe:
I was just reading your post on Index maintenance and we are currently working on ways to improve our own indexing.
So we are experimenting with fill factor to increase the size of our pages to over 1k on some of our non-clustered indexes. Mainly because of everything I’ve read points to not getting optimal fragmentation unless the page size is over the 1k mark. So on one of the tables we set the fill factor to 80% and have seen an improvement in fragmentation and performance.
Dale:
I have one database that is about 110GB. I did a rebuild on one index a couple days ago and it grew my transaction log by 40gb. Is that normal?
Is there a way to rebuild or reorg an index without growing the transaction log?
Editor’s note…I’ll pass this one on to Stephen Wynkoop as a topic to cover in a future SelecTViews editorial.
Stephen:
I use the following that is run every week at 2am on Sunday:
1. Check Database Integrity
2. Shrink Database
3. Rebuild Index
4. Update Statistics
5. Clean up History
I am a developer and have responsibility for this Database that is on the system I developed many years ago.
Editor’s note: beware of shrinking databases. If you needed the space at one time, you will need it again. By constantly growing and shrinking your database you have to potential to increase the database file fragmentation at the operating system level. It is not always possible to defragment a database file.
Do you have comments you’d like to add to the discussion? Send them to me at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Questions Raised during SharePoint Basics Webcast
Recently, SSWUG.ORG hosted the first of a four-part series on SharePoint 2010 Basics, presented by Rebecca Isserman. As part of the ensuing chat session, a few very interesting questions were raised from the attendees. The good folks at SSWUG asked if I could help answer their questions, and I obliged.
Featured White Paper(s)
Sharepoint Adoption
Written by AvePoint
Microsoft SharePoint is quickly becoming the platform of … (read more)