February 1, 2023 at 10:52 am
Hi ,
I have 2 databases 1)staging 2)prod. I am using following query to get index stats for rebuild. I will create that procedure in Prod database. it takes Database name as input and generate sql statements. it looks like following sql is not giving correct index names. either it skip some indexs (active one) or give name of index in other database. e,g. when I pass @DatabaseName = 'Staging' it is giving an index name that belongs to prod database. Do I need to male anyother join or I am missing or using incorrect tables ?
SELECT i.[name],
o.name,
sch.name,
s.avg_fragmentation_in_percent,
CASE WHEN s.avg_fragmentation_in_percent > 30
THEN 'Alter index [' + i.[name] +'] on ['+@DatabaseName+'].['+sch.name+'].[' + o.name +'] REBUILD WITH (ONLINE = OFF)'
ELSE 'Alter index [' + i.[name] +'] on ['+@DatabaseName+'].['+sch.name+'].[' + o.name +'] REORGANIZE'
END,
row_number() OVER ( ORDER BY s.avg_fragmentation_in_percent DESC) ,
0
FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND s.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS sch ON o.schema_id=sch.schema_id
WHERE (s.avg_fragmentation_in_percent > 15 )
AND i.name is not null
AND i.is_disabled = 0
February 1, 2023 at 11:37 am
all your sys.... tables are specific to the database you run this code on.
do do what you need you either use full dynamic sql (where you add "use [@DatabaseName]" before running the code above , or you manually change to that db before executing the code.
and more importantly - DO NOT use regorganize - and why reinvent the wheel? look at Ola Hallengren scripts for this type of things (https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html)
February 1, 2023 at 1:50 pm
Or, don't rebuild the indexes at all. Find a good fill factor, set that, and leave them alone. Just maintain statistics.
Different rules for columnstore indexes though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 1, 2023 at 1:50 pm
Or, don't rebuild the indexes at all. Find a good fill factor, set that, and leave them alone. Just maintain statistics.
Different rules for columnstore indexes though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 1, 2023 at 4:18 pm
I STRONGLY second the notion that index maintenance on rowstore indexes is pretty useless if based on logical fragmentation. I'll also tell you that it can cause major blocking on the proverbial "morning after" because you're using the generic supposed "Best Practices" methods, which were never meant to be considered a "Best Practice" and are actually a "Worst Practice" when used generically, especially with REORGANIZE in the mix. It doesn't work the way you think it does and will actually do things to the indexes that actually cause fragmentation and perpetuate the problem of pages splits.
The best thing to do is to rebuild statistics, if they need it.
Watch the following 'tube (and stay until after the QnA for an interesting outtake) and understand that, despite the title, it's NOT just about GUIDs. I just use those because they're the "Poster Child" for indexes that fragment.
https://www.youtube.com/watch?v=rvZwMNJxqVo
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2023 at 5:29 pm
thank you All for valuable directions. I will try to follow correct path as suggested.
one more question about locking, OFFLINE rebuild lock table for upcoming transactions. What about current transactions, does it rollback any DML or wait for them to complete ?
February 5, 2023 at 5:44 pm
thank you All for valuable directions. I will try to follow correct path as suggested.
one more question about locking, OFFLINE rebuild lock table for upcoming transactions. What about current transactions, does it rollback any DML or wait for them to complete ?
Rebuilding indexes does not rollback anything. Like everything else, it'll wait it's turn to lock the index/table.
Just another warning... if you are rebuilding indexes that are fragmenting and they have a zero fill factor and the fragmentation is occurring mid-index, you're setting yourself up for MASSIVE blocking due to page splits on the proverbial morning after. Like I've said before, it's better to do no index maintenance that it is to do it wrong. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply