September 30, 2024 at 12:54 pm
Hi there! This should make some eyes roll for some of you DBA's out there. Disclaimer: I am not a DBA and shouldn't be trying to play one.
I desperately need help in trying to remedy a problem I caused on our 2016 SQL Server (Std Edition). We realized that the weekly maintenance job hadn't been running for years on this server and index fragmentation was off the charts. I was able to successfully drop and rebuild indexes on some tables showing up with > 90% fragmentation but one of the largest tables seems to be deadlocking everything else on the server when I try to re-add the constraint. I can also see that the table is now a heap and the old index size is now showing as unused space in the table. How in the heck can I rebuild the index/PK constraint without causing any locking of other processes? None of them were trying to hit the table in question. I did this during server downtime but there was still stuff running and I was worried that if I didn't kill the index rebuild it would cause other issues on the server.
Any help or suggestions would be greatly appreciated! I realize that this was likely a very junior mistake.
Table Stats:
# records: 100,378,478
reserved: 45,482,128
data: 44,683,544
indexes: 464 (after drop)
unused: 798,120
I just used the default drop/create statement that ssms generated for me:
/****** Object: Index [pk_tablename] Script Date: 9/25/2024 12:39:18 PM ******/ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [pk_tablename] WITH ( ONLINE = OFF )
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [pk_tablename] Script Date: 9/25/2024 12:39:20 PM ******/ALTER TABLE [dbo].[tablename] ADD CONSTRAINT [pk_tablename] PRIMARY KEY CLUSTERED
(
[cono] ASC,
[tstm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
September 30, 2024 at 5:47 pm
This was removed by the editor as SPAM
September 30, 2024 at 10:03 pm
I might get a few eye-rolls from you, in return, because I'm going to tell you to stop doing generic, catch-all, supposed "Best Practice" index maintenance. It's all causing more harm than good.
First, if you drop an index that's got unique keys, you're likely destroying referential integrity having to do with that and other tables. Simply rebuilding such an index won't restore that integrity. You'll need to rebuild FKs, as well.
For the most part, (there are some minor exceptions), you should NOT be basing your index maintenance on logical fragmentation. One of the most important things that index REBUILD does is that rebuild the statistics for the index. It's so important that you usually don't need to go through all that REBIULDs cause... you just need to update statistics. Notice that even REBUILDing indexes isn't going to help really bad code. You have to fix the code to get performance there.
For your current dilemma (unless you can afford to simply do a restore)... if you're getting ready to rebuild a HEAP into a CI (usually a PK of some sort but not always), take a full backup and, when that's done, set the database to the SIMPLE Recovery Model (I'm thinking you no longer have mirroring or anything else that requires the FULL Recovery Model). That will make the index rebuilds minimally logged (so would BULK LOGGED if you log file backups are taken often enough), which can be a fair bit faster. Build the CI first and then build the NCIs (non-clustered indexes). Stop DROPPING indexes that are CI's or are keyed for uniquer rows).
I hope you have a full backup of the original database so that you can salvage any FKs from a restore to a Scratch database there.
Also, if you got the advice that led you to this problem you're in from the internet, please provide a link so folks can try to talk them into some fixes for the article or post.
Working on things like which indexes can benefit from a FILL Factor is both art and science. Since most of your indexes were likely never assigned a Fill Factor, they'll most be the default of "0" and that means 100% full unless someone messed that up for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2024 at 11:08 pm
First and foremost, stop dropping and rebuilding indexes, I don't care how bad the fragmentation is. Even 90% logical fragmentation makes little difference. What DOES make a difference is the fact that REBUILD rebuilds the underlying statistics with the equivalent of a FULL SCAN. That also means that, with the exception of Page Density (for memory and backup/restore usage), you're wasting a lot of time and resources.
For your current dilemma... if you're getting ready to rebuild a HEAP into a CI (usually a PK of some sort but not always), take a full backup and, when that's done, set the database to the SIMPLE Recovery Model (I'm thinking you no longer have mirroring or anything else that requires the FULL Recovery Model). That will make the index rebuilds minimally logged (so would BULK LOGGED if you log file backups are taken often enough), which can be a fair bit faster. Build the CI first and then build the NCIs (non-clustered indexes).
I hope you have a full backup of the original database so that you can salvage any FKs from a restore to a Scratch database there.
Also, if you got the advice that led you to the bit of a problem you're in from the internet, please provide a link so I can try to talk them into some fixes for the article.
And, do you know which indexes were built with a FILL FACTOR other than 0 or 100?
Is there a general rule of thumb for the recommended FILL FACTOR when rebuilding indexes?
October 7, 2024 at 9:23 am
Regarding Fill Factor, you need to think through what is happening when this is applied.
Assume a FF of 10%, this means that after a rebuild every page, every page, will have 10% free space. If your read activity is mainly sequential, it means that for any sequence of reads, you will be reading 10% more pages than needed for a FF of zero. For random reads, you will end up with about 10% more pages in the buffet pool than would be the case with a FF of zero. A different FF will give a different efficiency profile.
When you start updating the table, if the update can fit into the free space, it will. If it needs more free space you get a page split. With each page having about 50% free space. Eventually even this free space will get filled and you get another page splits.
Therefore if you define read efficency as the ratio of pages read to get a given amount of data, you get a constant pattern. Read efficiency starts at 90%, increases as updates progress to near 100%, then starts dropping off. The old wisdom was to do index rebuilds at a frequency that would keep your read efficiency at about 90%.
The old wisdom assumes your updates hit your table throughout the whole data range. This rarely happens, and most tables have hot spots, and these can change over the course of the year. The old wisdom also formed when data was stored in linear format on spinning rust, and some of the efficiency theory no longer holds true with vector-orientated storage such as (now obsolete) SANs and (current) SSDs, M2, etc. Vector storage treats all data as WORM, so any update grabs the next available free block and writes to it. Physically, all data access becomes random and fragmented in nature, even if you are doing a sequential read of an entire table.
Back in the 1990s on DB2 a vendor built a rebuild tool that could identify hot spots and add more free space at those places, with less free space in areas of stable data (Unlike SQL Server, the DB2 rebuild API allowed this). Very cleaver, but ultimately seen as obsolete with the rise of vector storage.
This goes back to Jeff Moden's recommendation. Do not worry over much about index fragmentation in your stats. You might 'fix' this logically with a rebuild, but physically you make very little difference. The key is to keep your stats up to date, so the database knows the most it can about which block the data is in.
At my old place (I am now retired) we focused on stats updates, and only worried about rebuilds when CI fragments exceeded 50k, and often not even then. Performance (the key metric) remained good and database performance exceeded user expectations. We did have some poor performing processes in the tech debt queue, but these came down to poor SQL syntax and chatty application code, and not DB fragmentation.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply