Things I Learned While Rebuilding Seriously Fragmented Indexes on SQL Server


I recently inherited some administration duties for a SQL Server instance.  The database was growing out of control and performing poorly.  The following is what I learned while trying to improve the state of the indexes.

The primary application database is ~ 85 GB in size and of that total amount there is a single table which is ~34 GB.  The single table includes an additional ~6 GB of indexes.  The total space on the data drive was ~90 GB.

Needless to say, the performance of the application depends entirely on the performance of that single table.  The rules were, I could not add, delete, or disable any of the indexes and I had zero control over the design the database.  It was a vendor product and any manipulation at that level would have violated our licensing and support agreement.

There were two goals in de-fragmenting the indexes: 1) Improve Performance and 2) Reclaim misused disk space.

Number 1:

If you have the licensing for Enterprise Edition, use it.  SQL Server 2012 Standard Edition does not have the ability to rebuild indexes online.  Rebuilding large indexes locks the table while the index is being rebuilt.

Number 2:

Script your index rebuild.  You will see all of the possible options and get to take advantage of them. In my scenario, the available disk space on the primary data drive was low.  I was afraid to rebuild all of the indexes at once because some of the indexes were larger than the available space.  I did have the advantage of a fairly healthy drive which contained only the tempdb.  I change the indexes to sort in tempdb (SORT_IN_TEMPDB = ON).  This sped up the rebuild and alleviated my concerns about using up the remaining space on the data drive.

Number 3:

Fragmented indexes can use up a lot of unnecessary space.  I recovered ~17 GB of space in the data file.  I do not think this is a normal result but is indicative of how large and badly fragmented the indexes were.

Number 4:

Do not shrink your data file.  I was so excited about the recovery of the space in the data file  that I wanted shrink the data file so the drive showed our system engineers how much space I had recovered.  Using 20/20 hindsight, this was an irrational thought. My only excuse is it was really early in the AM and I wasn't thinking clearly.  So I shrank the data file to recover the unused space and immediately my indexes were severely fragmented again.  Shrinking the data file places the data  anywhere available on the disk and does not care if it fragments indexes.  At least at this point I had the scripts set up from lesson number 2.

Number 5:

Fragmented indexes do cause performance problems, but indexes can not entirely overcome bad database design.  That can be another blog post.



Popular posts from this blog

Power BI Report Server: "An Error Has Occurred" or Power BI Report Server: "My Power BI Report is Coming up Blank" or Power BI Report Server: "401 and 403” Errors From Nowhere

Image
The information below is an extremely brief summary of the information collected to solve the elusive problem.  There are points I left out because I could not find supporting documentation and I did not want to release suppositions into the universe (why does an NTLM only issue impact a system using kerberos?).  I also want to thank my friend Josh Corrick for staying the course and seeing this through to the end.  See Josh's blog here:  https://corrick.io/blog/ Without warning, our Power BI Report Server began having issues presenting Power BI reports.   The reports would appear to render but would eventually return a blank report canvas.   There was not an error presented and this was impacting all Power BI reports.   An important note is the Power BI reports had worked as expected previously and no changes had been made to the report server such as upgrades or configuration changes. Subsequently, and seemingly unrelated, an error notification was presented when clicking
Read more

SQL SERVER 2017: STRING_AGG

STRING_AGG is a function released in SQL Server 2017.   The Oracle equivalent LIST_AGG has been available since 11gR2.  In the meantime, SQL Server users have resorted to black magic in order to get string values from separate rows. STRING_AGG is very welcome but there are some gotchas to be aware of.  Below is a simple example that works just like you think it would. SELECT STRING_AGG(FIrst_Name, ', ') WITHIN GROUP (ORDER BY First_Name) LIST FROM             (SELECT DISTINCT   First_name FROM [dbo].[Employee]     WHERE First_Name like 'A%' OR First_Name like 'B%') A ORDER BY LIST; You can run the query on any database you have string values and you will get something like this: A David, Aaren, Aaron, Abdul, Bob, Brian... If you want to split by the first letter of the name, do not make the mistake of using an over clause.  You will get an error stating STRING_AGG is not a window function. The function 'STRING_AGG' is not a valid win
Read more

Dynamic Label Positions With SSRS

Image
On a recent tabular "dashboard" with several bar charts in a small area our team found vertical positioning of the labels to be troublesome.  When the labels were set to "bottom" and the bar was too short the label would truncate the significant digits.  On the other hand, when the labels were set to "top" the numbers would be truncated if the column was too tall. Example of bar chart with label set to bottom. Same chart with the middle column label set to top. First we thought about using Smart Labels  but we quickly found that smart labels are not so smart when the labels are turned 90 degrees to make them vertical.  As you can see in the examples above the size of the numbers would make horizontal labels messy. So the obvious choice was to use a function to place the label on top when the bar was small and on the bottom once the bar was tall enough. In Report Builder, go to your chart and select a specific column - not just the chart.
Read more