August 23, 2021 at 6:08 pm
Experts, I am just trying to understand something. Auto Update statistics is enabled on one of the DB. I have index rebuild job running thru a maintenance plan which also update stats as well for 1 specific DB. That DB is almost 4.5 TB in size. The job runs for almost 36+ hours. Now I am going to be implementing Ola's script and use some logic to it so I am not rebuilding all indexes. So going back to the question. Query optimizer updates stats for objects if those objects are being used , if Auto Update statistics option is enabled. So why update stats thru maintenance plan or Ola's script? Do I have an option within the script to update stats that are stale? I am also thinking about dropping unused indexes. That's my plan on making index optimize job runs faster......and successfully of course. Any advise? Also fill factor is set to 90%..
August 23, 2021 at 7:59 pm
Specifically this section which indicates what triggers an auto-update on the statistics:
- There was a data change on an empty table.
- The number of rows in the table was 500 or less at the time of statistics creation and the column modification counter of the leading column of the statistics object has changed by more than 500 since then.
- The table had more than 500 rows when the statistics were gathered, and the column modification counter of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.
- A table in TempDB with less than 6 rows had at least 6 row modifications.
It is the 3rd point that is important. If you have a table with 1 billion rows in it, 20% of them need to be modified, which is 200 million rows. You may need to wait too long for them to auto-update.
My opinion, if you are looking to use Ola's scripts JUST for index maintenance, you may get better bang for your buck writing your own. The biggest reason I would recommend writing your own - support. In the event Ola's script breaks in your environment for any reason, you are on the hook to fix it and it is a long, complex script that handles a lot of things. If you write it yourself, you understand how it works and can (hopefully) quickly and easily debug and tweak it as needed such as rebuilding at 15% fragmentation or 30% or whatever number makes sense to your environment. Plus, if you are ONLY using it for rebuilding indexes, the script should be a lot shorter and easier to maintain.
There are a lot of scripts out there that you could use to get an idea of what the fragmentation level looks like on your system and you can use that to determine which ones need to be rebuilt.
Dropping unused indexes is a good idea until it isn't. What I mean is if you have a process that runs once per year (for example), you may have an index that helps that process. If you drop it because it appears to be unused, you will have a mess to fix later.
I would probably start by looking for duplicate indexes and removing duplicates. I would also write up my own script for index maintenance that only tackles indexes that are high enough fragmentation to make my list and for those indexes if they pop up frequently on my list, I'd be investigating a better fill factor.
Just my 2 cents. Ola's scripts I know are widely used and highly recommended. I just prefer my scripts to be shorter and to the point. For example, in my environment I designed our backup and maintenance stored procedures. I did it with a bunch of small, easy to test, stored procedures. One does backups of a single database, one does backups of all databases by calling the single database backup one as an example. My script for backups is targeted to RedGate SQL Backup as that is our backup tool of choice. Ola's scripts target multiple backup solutions but include RedGate.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 23, 2021 at 10:42 pm
If you are using REORGANIZE on any rowstore index that is fragmenting, STOP IT! PERIOD!. REORGANIZE does not work like you probably think it works. It can and will only compress an index UP TO the Fill Factor. It cannot and will not create new pages to "reimplement" the Fill Factor to create Free Space above the Fill Factor.
If all of your tables have been built with a 90% Fill Factor, you're made a huge and terrible mistake. You're just totally wasting 11.11% [(100/90-1)*100] of disk and memory for ALL of your static and "usually static" tables and all of your "Insert-Only, Ever-Increasing" tables such as Audit Tables, History Tables, Log Tables and Invoice tables that contain mostly legacy data that will never ever be changed again after a week or two.
If the reason why you're using a 90% Fill Factor everywhere is because you're using Random GUIDs for the keys of all your indexes, then you're going to need some more help. They can be made to be fragment free with <1% logical fragmentation for MONTHS at a time, regardless of daily insert rate but, again, YOU MUST STOP USING REORGANIZE and then talk with me about it.
Let me know if your Clustered Indexes are normally based on ever-increasing stuff like an IDENTITY column, a SEQUENCE, or a NEWSEQUENTIALID(). Those have both a serious INSERT hotspot issue and a huge fragmentation problem that CANNOT be fixed by lowering the Fill Factor if they suffer "ExpAnsive" updates soon after a row is inserted. There are multiple fixes for that but just having a 90% Fill Factor is an absolute and total waste on those types of indexes. Oddly enough, this is the one of only two places that that REORGANIZE works "correctly" to resolve fragmentation but it's much better to fix the fragmentation problem on these so that they no longer fragment.
In case you're wondering about the ol' REORGANIZE between 5 and 30% and REBUILD at >30% supposed "Best Practices", STOP DOING THAT! Those are NOT "Best Practices" and they never were meant to be "Best Practices". They actually suck so bad that they should be labeled as a "Worst Practice". Even the guy that originally came up with those number states the we should, and I quote, "...take those numbers with a pinch of salt and don't treat them as absolute", which is "magic-decoder-ring-speak" for "they're NOT Best Practices... stop using them like they were".
Just in case you haven't understood the main point here, it's actually better to do no index maintenance than it is to do it wrong and if you're using REORGANIZE to defrag RowStore indexes, then you're doing it WRONG! In fact, it's responsible for making people think that Random GUIDs are the "poster child" for fragmentation and that's completely incorrect! If you know the correct way to maintain them, YOU CAN ACTUALLY USE RANDOM GUIDS TO PREVENT FRAGMENTATION!!!
Heh... and, no... not even a complete mad-man could make up such a fantastic story. Only the TRUTH can be more strange! For an introduction to the truth about REORGANIZE (it's just wrong in so many ways), the supposed "BEST PRACTICES" that are actually a a worst practice (again, in more ways than 1), and how they all led to the "Myth of Random GIUD Fragmentation", please see the introductory video below. The "lessons learned" in this video are also applicable to non-GUID indexes but we didn't have time to deep dive those (although the fragmentation issues with ever-increasing indexes was graphically introduced".
https://www.youtube.com/watch?v=qfQtY17bPQ4&list=PLr9ab4Dj3ObuaHAUA9JJz-GUbfbwXEyS5&index=4
You say you have a 4.5TB database. I'm thinking you need some help with index maintenance. The video only "introduces" you to the problems. There are a ton of problems that few have ever addressed and most good folks aren't even aware of, not because they're stupid by any means. They just don't know things and neither do the people that taught them. Heh... and then there are things like LOBs that have defaulted to "in-row" since 2005 and people just don't understand how that is absolutely crushing performance on their Clustered Indexes. That's a separate presentation.
But, no matter... based on what you've posted, you need to move away from "Best Practices" that aren't and really start to figure out what's truly going on with your indexes and your index maintenance.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply