August 14, 2008 at 10:28 am
When we first moved from 2K to 2K5 we did not take the time to rewrite our index maintenance code to utilize the new DMV's in 2K5. Our older code is fairly robust and worked so we let it be (DBCC SHOWCONTIG, DBREINDEX, INDEXDEFRAG) for the time being.
Well the time has come to rewrite the routines to use the DMV's. I must say, I am very impressed. It appears a lot easier to determine in 2K5 what the fragmentation level of an index is. I have two "basic" questions:
1. I am using sys.dm_db_index_physical_stats. How exactly is this data kept current? It is very convenient to have this is handy management view, but I am curious how it is kept current.
2. In our 2K code we made decisions when an index had a ScanDensity 10. We then went on to make more decisions (DBREINDEX or INDEXDEFRAG) based on the size of the table. I am looking for the 2K5 equivalents on ScanDensity and LogicalFrag. I see a lot of references to avg_fragmentation_in_percent in sys.dm_db_index_physical_stats. Is this the only value I really need to worry about now?
August 14, 2008 at 10:38 am
Paul Timmerman (8/14/2008)
1. I am using sys.dm_db_index_physical_stats. How exactly is this data kept current? It is very convenient to have this is handy management view, but I am curious how it is kept current.
None of the DMVs actually 'store' data. They are not tables. They are views that expose certain aspects of sQL's internal structure. So, as far as I know, dm_db_index_physical_stats is kept current because when you query it SQL goes and looks at the indexes actual physical stats.
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
August 14, 2008 at 11:10 am
On the question about scan density, the answer is, "yes". Just worry about the fragmentation data in the new view.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 11:39 am
Understood about it being a VIEW. I am just impressed with the efficiency. Seems to return data for all indexes much quicker than a DBCC SHOWCONTIG.
August 14, 2008 at 4:50 pm
GilaMonster (8/14/2008)
None of the DMVs actually 'store' data. They are not tables. They are views that expose certain aspects of sQL's internal structure. So, as far as I know, dm_db_index_physical_stats is kept current because when you query it SQL goes and looks at the indexes actual physical stats.
As I understand it SQL Server is looking at the actual index pages in sys.dm_db_index_physical_stats. This would mean that it is not using statistics here.
Kalen Delaney has a good blog post about this.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/03/06/fragmentation-internals.aspx
"In LIMITED mode, SQL Server scans only level 1 and reports the logical fragmentation of level 0."
"In DETAILED mode, SQL Server scans every level, and reports on all kinds of stuff."
I have a stored procudure that is using sys.dm_db_index_physical_stats to dynamically rebuild or reorganize indexes.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply