Automatic Statistics Maintenance - Good or Bad

Question

Automatic Statistics Maintenance - Good or Bad

Viewing 9 posts - 16 through 23 (of 23 total)

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Christopher Stobbs (8/8/2008)
THis pretty exciting stuff 🙂
Thanks for all the help everyone.
Whats cause index fragmentation?

Page splits, rearranging the order of pages, becuase of updates or inserts. Also gaps left by deletes. Over time the indexes just sort of spread... like my gut.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

HI All,

I just ran an index rebuild on a table and it still has a fragmentation of over 90%????

Anyone know why this is?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Grant Fritchey (8/8/2008)
Page splits, rearranging the order of pages, becuase of updates or inserts. Also gaps left by deletes.

Shrinking the database or the data file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Christopher Stobbs (8/8/2008)
HI All,
I just ran an index rebuild on a table and it still has a fragmentation of over 90%????

How big is the table? It's not uncommon for very small tables to have that happen. On smaller tables (< 100 pages) don't worry too much about fragmentation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

GilaMonster (8/8/2008)
Grant Fritchey (8/8/2008)
[
Page splits, rearranging the order of pages, becuase of updates or inserts. Also gaps left by deletes.
Shrinking the database or the data file.

Oh yeah, good one. I always forget about it.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

The table only has 163 rows or so.

How would I tell how many pages it's using?

just want to say thanks again for all the help 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Check sys.dm_db_index_physical_stats, or run DBCC SHOWCONTIG

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

Cool thanks

They all have Avg page of 1

So these I don't have to worry about then is that right 🙂

I must say thats you all, I have learn' more today than in the last week 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 9

163 rows can, depending on the table, fit on one or two pages. That's just not going to be an issue if it goes to three or four. You'll want to focus on bigger fish.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software