September 12, 2024 at 4:30 am
Jeff Moden wrote:I used to be a front-end Developer... that's why I don't rebuild indexes all the time ... The page splits that came after the rebuilds would slow down the code too much.
I thought reorganisation of indexes caused page splits but not rebuilds?
"It Depends". For Random GUIDs and similar indexes with a highly distributed insert pattern, REBUILDs will normally help a whole lot if you lower the Fill Factor and REBUILD at 1% Logical fragmentation (an indication for those types of indexes that the world will turn to massive page splits if you don't rebuild soon). If you rebuild that same type of index with no reduced Fill Factor, then you're removing all free space and virtually every insert or expansive update will cause a page split. It'll be even worse than using REORGANIZE in such cases.
Even if you have an index with page splits at the logical "end" of the index, the page splits usually come from INSERTs going in at 100% (regardless of Fill Factor) and then are those are later followed by ExpAnsive Updates on those 100% full pages. Rebuilding those means that the space made by other expansive update will suddenly disappear and all new ExpAnsive Updates will cause page splits for a while to a couple of hours. If you do that to ALL of those types of indexes, they will all suffer massive page splits and can (probably will) produce a shedload of blocking like they did for me on 18 Jan 2016... totally wasted the system in blocking and deaclocking for hours.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2024 at 4:36 am
(bumping the page 2 fault on this forum)
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2024 at 12:45 am
First thank you for the conversation. I haven't even been a DBA FOR 5+ years at this point and I ways try to review what I might discount. Sometimes I discount to quick! Last I was I was configuring underpowered active passive servers and maintaining a few relatively large databases. Not giant but very large. Fragmentation was always the issue when developers complained. I didn't have to go look at the fragmentation. I just had to run OHallengren and it would be fixed although sometimes it would take running the built in SQL server defragmentation to actually fix the performance issue. I had one server with an application that was coded to update every record a user touched when the logged out so a noon every day 3000 users would log out and the 100 records then had been looking at all got update at almost the same time. The server crashed, and crashed and fragmented horribly in short order. DTA wizard made it purr! Disk , processor and memory stopped hitting maximum usage during the week. I gave back 3/4 of the memory we had given it. DTA is the bomb! It was written to tune the server was it not? I had it completly automated years ago so I just got the recommendations in my email.
Now about the reorganize. Due to you're reply, and not wanting to discount you're input I tried reorg again. It did work for small indexes but its 22+ hours and counting on a 50 gb index. It hasn't bother anyone so as long as it fixes that nasty index. Might even have been better to do it taking more time so to hit the server softer but if I had a more powerful server I would be doing a rebuild online.
September 28, 2024 at 2:05 pm
I'm still wondering what bad advice I gave from my 26 years of hands-on DBA and development experience. I know I didn't talk about page splits, or leaf level, tries trees, or anything really smart people would think of. As a developer I don't have access to the server or disk so someone else will have to deal with proper disk maintenance whatever that is now days. When I was a DBA with access to the server, I got reports based on max usage of disk, memory and processor for the week showing if they were maxing out. If they add in a counter to watch stolen memory, they will have the info they can see improve drastically after using DTA. Even a button pushing monkey (non-programmer DBA) can use DTA wizard to make a server purr. I did confirm (again) using reorganization is a waste of time! Did it clean up a few heavily fragmented indexes very nicely without blocking anyone? Yes! Did it take over a day just for one of them? Yes. Lesson learned is as usually do what I know. Experience is usually better than an online article. First rule of thumb is assumed everyone around you is incompetent and will try their best from letting you succeed. If they are a DBA be even more weary of them! Unfortunately, jiujitsu doesn't apply to the business world or we could just choke them out and complete what needs to be done. Just do everything the server/compiler asks you until it runs into a problem. Don't act like you're smarter than the MS SQL Server developers who wrote the software! We do use development environments so that should be a big deal to do!
I do mean to be of good spirit so please don't take offense. I'm just an old guy who has learned to hate DBAs due to complete incompetence and lack of concern for the developers they thwart, not support! I don't hate the person, just the body standing between me and decent server performance. It really as simple as using the tools the developers created. Most people don't even do that.
<<Do you have coded proof that REBUILDing indexes causes substantial enough improvements in performance beyond what simply rebuilding the statistics will do to actually make the REBUILDs worthwhile?>>
Yes, it's called decades of experience, but I have to say you are the type of DBA I have done battle with and lost most the time. You won so the server still sucks, and I'm stuck with doing the DBAs job! Organizations spend 10 times what they need to for development because of DBAs that just won't listen or do what needs to be done. They might even write some nice code sometime to do defrag but then use the 'LIMITED' mode instead of 'SAMPLED' so they never see the fragmentation. Just because Grant Fritchey, Brent Ozar or Adam Mechanic mention something doesn't mean it applies to your situation like worrying about DTA wizard scripts when you are applying the in dev anyway and you're sever performance is in the tolilet in the first place. Note: Pagelife is supposed to be positive!
September 29, 2024 at 8:23 pm
I do mean to be of good spirit so please don't take offense.
You've certainly impressed us there.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2024 at 1:19 am
I thought I would mention again never use reorganize unless it's a tiny index and you don't have other things that need to be done. I was dumb enough to leave it on again and lost access to the table for another full day while it was reorganizing. The index wasn't even that big. If I had SA privileges I would kill it, but since I don't, I had to wait 24 hours to drop a few indexes on that table I wanted to drop. Reorganize is worse than a waste of time. To anyone who wants to keep a server running so that developers can actually use it, don't waste any CPU, disk or processor time you can be using to get ahBooksead with maintenance. Always be as far out front of the approaching Sunami so you may be able to outrun it and live! Yes, log files every 5 minutes! Whine to Ozar about how that's too often. How do you think you're going to be able to keep a server with 35 databases on it defragmented unless you're working at it around the clock? You log files will always be too big! Also, don't be some foolish organization that uses jobs with million's of steps. You've heard of DTS and SSIS right? Ever heard of parallel processing! Just don't code in them. Write TSQL code and execute it. DTA wizard, tune the databases, keep them defragmented, use the software the MS developers did such a good job at! Don't be incompetent, indigent and belligerent about your incompetence! Just use the software and look like a top notch professional! Again, this isn't directed at anyone specifically, more like everyone who calls themselves a MS SQL DBA, and only in the hope of alleviating the pain inflicted in developers and teaching DBA how not to be incompetent, indigent and belligerent when all they have to do is use the tools! And they say old people lose their filters! Ha!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply