July 31, 2023 at 8:59 am
Hi everybody,
I used this job to rebuild the index in the msdb / system_databases:
EXECUTE [MaintenanceDB].[dbo].[IndexOptimize]
@Databases = 'SYSTEM_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y',
@MSShippedObjects = 'Y',
@OnlyModifiedStatistics = 'N'
In the msdb database is one table with this index:
table: sysjobhistory
pages: 1033
fragementation: 90 %
I thougt, the job above would fix this. Red Gate SQL Monitor raised a warning. I have no idea, how to change the job or how to fix it.
And, if I run the script / job, there are no entries in the log table from Ola.
Thanks,
Kind regards,
Andreas
July 31, 2023 at 1:35 pm
Why are you trying to rebuild or reorg the indexes? What problem are you attempting to solve?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 31, 2023 at 5:54 pm
Hi Andreas,
how large is the sysjobhistory? Can you manually rebuild the index?
July 31, 2023 at 6:10 pm
Hi Andreas, how large is the sysjobhistory? Can you manually rebuild the index?
Same question. What problem is being solved by rebuilding the indexes?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 31, 2023 at 6:28 pm
@ Andreas,
How are you verifying the fragmentation on that table after you've executed the OLA script?
Also, Michael's question is valid... your answer might be "to defragment the bloody index!" but that's not what he's actually asking. He's asking you why you think de-fragmenting the indexes based on logical fragmentation is actually going to help in any way, shape, or form, especially on a table that uses RBAR inserts and updates.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2023 at 7:09 pm
Its 1033 pages, I already started going down that thought train then saw he posted its size.
That close to the lower limit for it rebuilding in indexOptimize, (which is actually probably excessively low) I would just ignore it until there is more data. system db indexes need to be maintained periodically, but not often, and never if they never get much bigger than this.
July 31, 2023 at 10:40 pm
Its 1033 pages, I already started going down that thought train then saw he posted its size.
That close to the lower limit for it rebuilding in indexOptimize, (which is actually probably excessively low) I would just ignore it until there is more data. system db indexes need to be maintained periodically, but not often, and never if they never get much bigger than this.
These recommended setting for reindexing are not longer valid, and have not been for a few years.
Also, I would say that reindexing the table sysjobhistory is a complete waste of time. I would assume that this table is being purged on a regular basis. The normal operation of this table with endless cycle of inserts and deletes will fragment the indexes quickly.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 1, 2023 at 3:11 am
deubel_m wrote:Hi Andreas, how large is the sysjobhistory? Can you manually rebuild the index?
Same question. What problem is being solved by rebuilding the indexes?
The problem is not solved, if you repeat your question which has nothing to do with Andreas problem.
So again @Andreas.kreuzberg, can you manually rebuild the index?
August 1, 2023 at 3:48 am
Michael L John wrote:deubel_m wrote:Hi Andreas, how large is the sysjobhistory? Can you manually rebuild the index?
Same question. What problem is being solved by rebuilding the indexes?
The problem is not solved, if you repeat your question which has nothing to do with Andreas problem. So again @Andreas.kreuzberg, can you manually rebuild the index?
The question was raised because there is likely no benefit to rebuilding an index. In this case, as I said before, rebuilding sysjobhistory is most likely a waste of processing power.
Why this is not working is likely because the indexes are not fragmented within the parameters specified. You have taken Ola's code apart, and actually learned how it works so that you can explain the code in the event that you need to support it, correct?
This answer was modified. You originally stated 'I recommend reading some articles about index maintenance."
I have.
Please tell me what or who's recommendations you are following that says you need to do index maintenance?
It's not Microsoft. From their documentation: "Index maintenance, performed by either reorganizing or rebuilding an index, is resource-intensive. It causes a significant increase in CPU utilization, memory used, and storage I/O. However, depending on the database workload and other factors, the benefits it provides range from vitally important to minuscule."
and
"Index maintenance decisions should be made after considering multiple factors in the specific context of each workload, including the resource cost of maintenance. They should not be based on fixed fragmentation or page density thresholds alone.'
So, if you are rebulding indexes after 30% fragmentation, and reorg after 5% fragmentation, then maybe you need to read some articles about index fragmentation. I would do a google search for "Jeff Moden Black Arts Index Maintenance". Take the time and watch those presentations. It may be eye opening.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 1, 2023 at 4:48 am
Good morning,
I am able to rebuild this index. And thats the problem, when I could rebuild the index, why wouldn't the Ola script rebuild the index?
I thinks, this is just a problem in my eyes, but I'd like to solve it.
Otherwise I will check the settings in red gate sql monitor, that I will not get further warnings with this index "problem".
Kind regards,
Andreas
August 1, 2023 at 4:51 am
Hello Jeff,
I check the fragmentation with this script, I found some years ago in the internet:
SELECT
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
August 1, 2023 at 12:51 pm
This is the code being used by Ola's scripts to get the fragmentation. In the proc, this is dynamic SQL.
SELECT MAX(avg_fragmentation_in_percent), SUM(page_count)
FROM sys.dm_db_index_physical_stats(DB_ID(@ParamDatabaseName), @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 1, 2023 at 5:02 pm
CreateIndexNonclustered wrote:Its 1033 pages, I already started going down that thought train then saw he posted its size.
That close to the lower limit for it rebuilding in indexOptimize, (which is actually probably excessively low) I would just ignore it until there is more data. system db indexes need to be maintained periodically, but not often, and never if they never get much bigger than this.
These recommended setting for reindexing are not longer valid, and have not been for a few years.
Also, I would say that reindexing the table sysjobhistory is a complete waste of time. I would assume that this table is being purged on a regular basis. The normal operation of this table with endless cycle of inserts and deletes will fragment the indexes quickly.
Whether that guidance is valid or not, is irrelevant. Though I already pointed that out in my original comment. Ola Hallengren's IndexOptimize stored procedure is still configured with a default to match that guidance. with only 33 pages of difference between however the table size was measured and the guidance, there is a lot that could cause it to be ignored.
OP isn't asking whether sysjobhistory should or shouldn't be rebuilt. Every time the subject of index maintenance comes up on this forum there is a lot of chest puffing and thumping about it and the point gets argued to death even well after it is established the issue is political or for vendor support and sometimes even when it is actually technical, it still gets argued to death. There are certainly tables in msdb that periodically require maintenance. some of the integration services tables if there are a lot of maintenance plans and database mail especially.
Andreas, not enough is known about your environment to say for sure, but after thinking about it a little bit, most likely IndexOptimize is ignoring the indexes on sysjobhistory because they are too small. I have two indexes on mine, the clustered index and a non-clustered index on job_id which is a 16 byte guid. If that is the case and if your monitoring software is evaluating indexes that do not meet the size threshold for maintenance, something needs to get adjusted somewhere, preferably on the monitoring side to increase the threshold to monitor. None of the columns are potential LOB types to have out of row pages, the [message] column is the largest and it's only an nvarchar(4000). If it isn't a size per index thing, you are going to have to dig into the procedure to see how it is being filtered, if you require documentation for why it isn't being maintained.
If you don't need to document why it isn't being maintained, just ignore it. Concurring with everyone else's points about index maintenace, there is seldom a technical reason to rebuild them and near zero technical reason to maintain indexes that are so small.
August 1, 2023 at 6:44 pm
Good morning,
I am able to rebuild this index. And thats the problem, when I could rebuild the index, why wouldn't the Ola script rebuild the index?
I thinks, this is just a problem in my eyes, but I'd like to solve it.
Otherwise I will check the settings in red gate sql monitor, that I will not get further warnings with this index "problem".
Kind regards,
Andreas
This is going to sound a bit crazy to the uninitiated...
Let me ask you this... You have 90% fragmentation on the dbo.sysJobHistory table. Why do you believe that's an issue? The answer is because the RedGate monitoring says so. It's based on supposed "Best Practices" that actually aren't best practices and were never meant to be best practices. In fact, they're actually worst practices. MS finally rewote the page (although still incorrectly, IMHO) where those supposed "Best Practices" used to be documented.
Worse yet, that particular table is RBAR in nature. Usually, only 1 row is read, written, or updated and that means no range scan and that means that even 99.9999% fragmentation will hurt the performance at all.
I'd be more interested in why Ola's code doesn't pick it up but the only time I do any index maintenance is to recover disk space if the page density gets low. In fact, I went for almost 4 years (Jan 2016 'til Nov 2019) without doing any index maintenance. Not only did that stop the "Morning After Blocking" because the "Best Practices" Index Maintenance is wrong (especially when it comes to REORGANIZE) but performance actually got a lot better in the first 3 months (long couple of stories there on how that works).
Michael John (on this thread), Ed Wagner, and several other folks in this good world (like the ones that helped get MS to change the document including the guy that first suggested the numbers that got misinterpreted as a "Best Practice") don't actually do index maintenance except for space recovery or, in a particular instance, to prevent page splits on Random GUIDs and other evenly distributed indexes.
And, except for a very particular fragmentation pattern, REORGANIZE doesn't work the way people think it does and it frequently perpetuates and is actually the cause massive page splits.
And, just so you don't think that I'm a crackpot, here's a link to a presentation I gave that explains the real reason behind Random GUID fragmentation and destroys the old myth of what "Best Practice" index maintenance was (and, unfortunately, people are still following). It's the beginning of a much longer series that I'm still working on. It's also not just about GUIDs... it explains how REORGANIZE is the poison for this and other types of indexes.
https://www.youtube.com/watch?v=rvZwMNJxqVo
That 'tube also contains a link to Paul Randal's 2009 article on where the "Best Practice" numbers actually came from and a recommendation to take the those numbers "with a grain of salt".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2023 at 4:53 am
Good morning,
thanks for your answers and your thoughts. I will change the check in sql monitor, so I will not get a warning with an index < 2.000 pages.
So nobody said it would be easy.
Every environment is different, and I had to try some solution.
We got about 200 sql server I think, and everything is on a virtuell server, VM.
And we got only SSD disk in the background. So the random access on the disk will not matter in the index.
I read a lot about index maintenance, but I think, I need a second life to understand everything.
Kind regards,
Andreas
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply