May 25, 2022 at 5:11 am
Ahoi,
we are using the ola hallengren scripts to run the weekly index optimizer.
I was asked if i could write a script to run rebuilds of indexes for a subset of tables.
Its not beatiful, but it seems to do the work in theory.
This is the contents of the procedure i tried to make:
Questions:
I am asking because some of the alters take very long looking at my logging table. The ones that take very long are obviously those who have many indexes, rows, insert and updates (quel suprise). But i am assuming it might also be related to the way my procedure is executing the alter reorganize code.
--#######################################################################
--Get all "relevant" Table Names and Execute Rebuild/Reorganize
--> optional: add Database and Schema to Cursor + REbuild SQL Code
--> Todo: change information schema query to all relevant tables
--> alter schema/databasename in alter of @sql_query
--#######################################################################
DECLARE @CURRENT_TABLE NVARCHAR(200) = ' '
--DECLARE @CURRENT_DB NVARCHAR(200) = ''
DECLARE @params nvarchar(4000);
DECLARE @SQL_QUERY NVARCHAR(500);
SET @params = N'@CURRENT_TABLE NVARCHAR(200)
,@CURRENT_DB NVARCHAR(200)'
--List of Tables that need to be rebuild and placed into cursor
DECLARE curs CURSOR
FOR select TABLE_NAME--,Current_Database = DB_NAME()
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE != 'view'
and (table_name like '&List_of_Search_terms%')
OPEN curs;
FETCH NEXT FROM curs INTO
@CURRENT_TABLE;
WHILE @@FETCH_STATUS = 0
BEGIN
--Try Catch, in case Deadlock on Table due to Access, skip table
BEGIN TRY
--Logging: Table + Startms
SET @SQL_QUERY = 'insert into dbo.Index_Maintanance_Logging values('''+ @CURRENT_TABLE + ''',CURRENT_TIMESTAMP,NULL)'
EXEC sp_executesql @SQL_QUERY
--In every iteration execute query, redefine @sql variable, else it executes always the same
--PRINT 'ALTER INDEX ALL ON [prodmd01].[prodmd01].['+ @CURRENT_TABLE + '] REORGANIZE' --REBUILD
SET @SQL_QUERY = 'ALTER INDEX ALL ON [prodmd01].[prodmd01].['+ @CURRENT_TABLE + '] REORGANIZE' --REBUILD
EXEC sp_executesql @SQL_QUERY
--Annahme Job läuft nur ma ersten Tag, für den heutigen Tag, logge mir den Enzeitpunkt für: DATEDIFF(SECOND,StartTMS,EndTMS)
update dbo.Index_Maintanance_Logging
set EndTMS = CURRENT_TIMESTAMP
where TableName = @CURRENT_TABLE
and cast(StartTMS as date) = cast(CURRENT_TIMESTAMP as date)
--GET Next Table for next iteration through Cursor
FETCH NEXT FROM curs INTO @CURRENT_TABLE;
END TRY
BEGIN CATCH
select 'Skip due to ERROR'
END CATCH;
END;
CLOSE curs;
DEALLOCATE curs;
GO
CREATE TABLE [dbo].[Index_Maintanance_Logging](
[TableName] [nvarchar](100) NULL,
[StartTMS] [datetime] NULL,
[EndTMS] [datetime] NULL
) ON [PRIMARY]
GO
I want to be the very best
Like no one ever was
May 25, 2022 at 10:11 am
I'll let Jeff do some chopping but WHY Reorganize? this should be avoided almost at all costs (very few exceptions)
May 27, 2022 at 4:08 am
I'll let Jeff do some chopping but WHY Reorganize? this should be avoided almost at all costs (very few exceptions)
Not aware of that, also can't seem to find this opinion when on regular research of Rebuild vs Reorganize.
But that is why i made this post, because i wanted to know if there are core issues, when executing the script.
I want to be the very best
Like no one ever was
May 27, 2022 at 6:29 am
have a look at the following links for info
https://www.sqlservercentral.com/forums/topic/review-ola-hallengren-indexoptimize-parameters
https://www.youtube.com/watch?v=rvZwMNJxqVo
https://www.sqlservercentral.com/forums/topic/index-fragmentation-42
regarding your particular need to do it on a subset of tables - don't worry about creating a custom script for it.
Ola's script already allows you to do that by specifying the database and tables you wish the script to process.
EXECUTE dbo.IndexOptimize
@Databases = 'db1, db2, db3', -- or just 1 db
@Indexes = 'db1.table1, db1.table2, db1.table3, db2.table1, db2.table2, db3.table2', -- specify the list of db/tables you wish to do independently of normal process. if required you can also specify the specific indexes per table db1.table1.index1
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I avoid the rebuild online as it can be painfuly slow
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I avoid the rebuild online as it can be painfuly slow
@FragmentationLevel1 = 5, -- change to a better value
@FragmentationLevel2 = 30, -- change to a better value
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'
May 27, 2022 at 8:13 am
Thanks for the reply.
Weird that this is somehow not more public knowledge since the problem seems to be pretty impactful and somehow mentioned "no where" in most (popular) google results.
I want to be the very best
Like no one ever was
May 27, 2022 at 4:19 pm
frederico_fonseca wrote:I'll let Jeff do some chopping but WHY Reorganize? this should be avoided almost at all costs (very few exceptions)
Not aware of that, also can't seem to find this opinion when on regular research of Rebuild vs Reorganize.
That's because of some poor wording in a couple of Microsoft documents a long time ago that caused 98% of the world to adopt supposed "Best Practices" that actually aren't and were never actually meant to be even closely considered to a "Best Practice" and a serious misread about what REORGANIZE actually does and a shedload of mis-information and flat out bad information that came out after that.
I just gave (on GROUPBY.org on 24 May 2022) my latest version of the presentation that proves all of that using the proverbial "poster child" for fragmentation, Random Guides to test with. It's not just about Random GUIDs, though. I use those because if you can fix those, a lot of the techniques used can fix "normal" indexes.
The video for that latest presentation I did on the subject should be a ready on YouTube in a few days. They still have to render it out, insert and ad or two, and upload it. I don't know exactly when it'll come out but watch for it at the link below. If they don't post the zip file I included with it, you can ping me at the email address I provided in the video.
This is where the video will appear. You'll probably need to scroll through their playlist to find it. Look for "Black Arts" Index Maintenance.
https://www.youtube.com/channel/UCEHf_UKwG3GMkb9wIVBTeQA
In the meantime, I recommend that you simply stop using Reorganize until you know what it's NOT doing and how it may actually be perpetuating fragmentation all day every day in your databases even if you don't have a single GUID in the entire server and how it can be and frequently is anything but "less resource intensive" than Rebuild.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2022 at 4:24 am
Left a subcription
I want to be the very best
Like no one ever was
May 30, 2022 at 2:08 pm
Also, if you need something "right away", they do have a previous presentation of mine on the subject. I have a couple of "mis-speaks" that I made during the "heat of a very fast presentation" but those are covered in the "errata" notes I made for the post. There's also one small formula change where I forget to add the 2 bytes for the slot array position for calculating row length but, if that's not a concern (it actually isn't for most folks) and you don't mind having an "errata sheet", see the link for the presentation I made in the October of 2021 on Group_By below.
https://www.youtube.com/watch?v=rvZwMNJxqVo
I also have posted a warning there... here's that warning.
If you're listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they're sudden and they're loud! They WILL lift your headset!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2022 at 4:49 am
Ok i am confused, i was replacing my "alter index reorganize" part with the "execution dbo.indexoptimize" within my script that iteratates through the for me relevant tables, job seems to be quickly running through, alot faster than the index previous reorganize.
Is it intended that the index fragmentation is not affected by the execution of the the below, i tried different fragtmentation levels.
Even when i run the script below with a static @CURRENT_DB_SCHEMA_Table for a certain table (instead of iteration through all the tables) , the index fragmentation of the table indexes is uneffected at 99%.
Date and time: 2022-05-31 06:47:46
Database: [prodmd01]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Is accessible: Yes
Date and time: 2022-05-31 06:48:02
Database context: [prodmd01]
Command: UPDATE STATISTICS [prodmd01].[SEARCH] [PK__SEARCH__D6E6400EC8A7B454]
Comment: ObjectType: Table, IndexType: Index, IndexType: Clustered, Incremental: N, RowCount: 119, ModificationCounter: 37
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 06:48:02
Date and time: 2022-05-31 06:48:02
Database context: [prodmd01]
Command: UPDATE STATISTICS [prodmd01].[SEARCH] [b84344d60da464bb3d]
Comment: ObjectType: Table, IndexType: Index, IndexType: NonClustered, Incremental: N, RowCount: 119, ModificationCounter: 37
Outcome: Succeeded
Duration: 00:00:00
EXECUTE [MASTER].dbo.IndexOptimize
@Databases = 'prodmd01', -- or just 1 db
@Indexes = @CURRENT_DB_SCHEMA_Table, -- specify the list of db/tables you wish
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I
--@FragmentationHigh = 'INDEX_REBUILD_OFFLINE', -- do not use the reorg options - and I
@FragmentationLevel1 = 10, -- change to a better value
--@FragmentationLevel2 = 50, -- change to a better value
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'
I want to be the very best
Like no one ever was
May 31, 2022 at 6:06 am
It is normal on the case of that table - it is a very small table and even if you rebuild it manually the fragmentation may not change - or if it changes it really will not have an impact on performance.
One of the parameters for IndexOptimize (omitted by default) has a default value of 1000 (see the code for the proc)
@MinNumberOfPages int = 1000
that index with just 119 records likely has less than 1k pages hence is not selected to be rebuild - and may not be an issue either.
But if you do find its an issue then change the parameter to a lower value.
(And I would suggest you get your developers to explicitly name the constraints on the tables - nothing worst than seeing a name like "PK__SEARCH__D6E6400EC8A7B454")
May 31, 2022 at 8:04 am
Referring to the following page, search for the number 128. It turns out that's a threshold and its 128 extents. Since an extent is 8 pages and there 128 of them, that's 1024.
In some cases where I want to get into some small stuff that someone has whacked out, I'll sometimes go with as little as just > 8 pages (helps avoid mixed extents that won't defrag) or > 128.
Whenever I can, if the database is in FULL Recovery, I'll take a log file backup first, shift into the BULK LOGGED Recovery Model, and the shift back to FULL and take another log file backup to decrease the exposure of non-Point-in--Time restores. That also serious helps the peformance of the REBUILDs and seriously decreases the log file usage. And multi-threaded rebuilds rock... if you have the CPU's for it, Set MAXDOP to 8 for your REBUILDs. That seems to be the "sweetspot" on my system (1 Numa node for me). More or less makes things slower for me. YMMV.
Use sp_IndexDNA™ to look at your larger and more troubling indexes about a week after you do a rebuild and you'll, see that they might not be behaving like you think. And "exploding broom-tip" is a sure sign of "ExpAnsive" updates in the hot-spot of ever-increasing indexes. If you can't repair the "ExpAnsive" update issue, the rebuild them a 97% to flag them for what they are.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2022 at 2:01 pm
Thanks for the reply.
Weird that this is somehow not more public knowledge since the problem seems to be pretty impactful and somehow mentioned "no where" in most (popular) google results.
Even MS didn't/doesn't understand, even though Paul Randal tried to tell them back when he first made the numbers up when MS held a gun to his head about 2 decades ago. And, with that, he may also not know the full impact of what REORGANIZE actually doesn't do (it's one of the main reasonw why people think that Random GUIDs are bad). MS finally got the message and changed the page that used to hold the 10/30 recommendation but they still don't get it about Reorganize. Some folks had been talking a bit about it for a while but they didn't have really good proof. I didn't develop sp_IndexDNA™ until early 2017 and started teaching it at SQL Saturday's but not many bought into it and those that did haven't been real verbal about it.
I'm considering submitting a white paper to MS with a copy of sp_IndexDNA™ and a copy of the flick I posted the link to (with the errata sheet).
I'm also slowly developing and testing an auto-magic way to identify what type of index an index is by pattern and using only the Fill Factor to identify the index. Rowstore only for now, which is what most people have a majority of.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2022 at 4:14 am
This was removed by the editor as SPAM
June 3, 2022 at 5:46 am
HMHMHMH
Job has been doing fine for the few days i used the dbo.IndexOptimize
Now the job has been stuck for 4 hours on a single index rebuild while the Job previously took 30mins for the whole thing including all tables.
Dumb me should have taken a look at why he was stuck before i killed the job, but since im not a DBA and i got yelled at why the system isnt running anymore i stopped the execution of the job, instead of checking what the alter/job currently does.
Defenite lesson learned...
According to the log in the dbo.CommandLog from OLLA Hallengreen, he was doing the following:
ALTER INDEX [PK__EVENT__556FC09B9CFE71DE] ON [prodmd01].[EVENT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)
Gonna keep an eye on that.
I want to be the very best
Like no one ever was
June 3, 2022 at 6:12 am
Why are using this setting: SORT_IN_TEMPDB = OFF?
That's generally a very bad idea overall, esp. for larger indexes.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply