IX fragmentation and Updating Stats

Question

IX fragmentation and Updating Stats

sqlguy80

SSCrazy

Points: 2765
More actions
November 7, 2023 at 6:32 pm

#4323241

Though its Azure MI Business critical , I see fragmentation, Any script to identify which IX's need to be rebuilt ,as Frag is over 50%.
I will add REBUILD WITH (ONLINE=ON) as we cannot have downtime , Its a 24 x 7 DB. I see failed inserts on dynatrace every now and then, possibly as the table is large in size 72 m rows ( 2019 -2023 data )

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

sqlguy80 wrote:

Though its Azure MI Business critical , I see fragmentation, Any script to identify which IX's need to be rebuilt ,as Frag is over 50%.
I will add REBUILD WITH (ONLINE=ON) as we cannot have downtime , Its a 24 x 7 DB. I see failed inserts on dynatrace every now and then, possibly as the table is large in size 72 m rows ( 2019 -2023 data )

Let me ask... how much of a performance improvement was there when you rebuilt that index?

--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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

What is actually the question ?

Why are inserts failing ? ( trace it if it's not available in the application logs )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Michael L John One Orange Chip Points: 27054 More actions · Answer 3

sqlguy80 wrote:

Though its Azure MI Business critical , I see fragmentation, Any script to identify which IX's need to be rebuilt ,as Frag is over 50%.
I will add REBUILD WITH (ONLINE=ON) as we cannot have downtime , Its a 24 x 7 DB. I see failed inserts on dynatrace every now and then, possibly as the table is large in size 72 m rows ( 2019 -2023 data )

There is little that adds up here. The "Azure MI Business Critical" tier has nothing that limits fragmentation. Why would you expect the tier to have in it that would have an effect on index fragmentation?

What indicates to you that fragmentation over 50% is an issue and requires a rebuild?

A failing insert should not be affected by a fragmented index, unless of course a rebuild was occurring when the insert was being executed. Are there errors that go with these failures?

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/