wrong index name

Question

wrong index name

thbaig

Hall of Fame

Points: 3003
More actions
February 1, 2023 at 10:52 am

#4144113

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

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)

This reply was modified 1 year, 2 months ago by frederico_fonseca.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thbaig Hall of Fame Points: 3003 More actions · Answer 5

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 ?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

thbaig wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)