March 23, 2009 at 10:43 am
Hi All,
I have two related questions. First I have been working with sys.dm_db_index_physical_stats and I am really surprised by it's relative poor performance compared to DBCC.
I am comparing a query like this
Select * from Sys.dm_db_Index_physical_Stats (@DbId, @ObjectID, Null, Null, 'LIMITED')
to DBCC ShowContig ('tableName') with TableResults, All_Indexes.
DBCC consistently returns in less than 10 seconds while the dm function consistently takes 12+ min.
Is this normal or typcial?
Second
I am confused about something in the TechNet BOL description of Alter Index (http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx). In the section titled “WITH ( LOB_COMPACTION = { ON | OFF } )” there is a comment that “OFF has no effect on a heap”.
From the write up it seems to me that “OFF” has no effect on either a Table or a Heap. I think the statement should say “ON has no effect on a heap”. In other words if the target is a heap then specifying “LOB_Compaction = ON” has no effect.
I am pretty sure this is the behavior I observe but BLOB storage, etc. are still somewhat of a mystery to me.
Also, a little earlier in the article it states “If ALL is specified and the underlying table is a heap, then the rebuild operation has on effect on the table. Any nonclustered indexes associated with the table are rebuilt”. I clearly see this behavior and that is part of the reason I think the other statement is incorrect.
Does anyone know for sure?
Thanks
March 23, 2009 at 12:45 pm
Ray Herring (3/23/2009)
Hi All,I have two related questions. First I have been working with sys.dm_db_index_physical_stats and I am really surprised by it's relative poor performance compared to DBCC.
I am comparing a query like this
Select * from Sys.dm_db_Index_physical_Stats (@DbId, @ObjectID, Null, Null, 'LIMITED')
to DBCC ShowContig ('tableName') with TableResults, All_Indexes.
DBCC consistently returns in less than 10 seconds while the dm function consistently takes 12+ min.
Is this normal or typcial?
That's not normal. How many indexes it it returning? I'm getting sub-second response time.
Second
I am confused about something in the TechNet BOL description of Alter Index (http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx). In the section titled “WITH ( LOB_COMPACTION = { ON | OFF } )” there is a comment that “OFF has no effect on a heap”.
From the write up it seems to me that “OFF” has no effect on either a Table or a Heap. I think the statement should say “ON has no effect on a heap”. In other words if the target is a heap then specifying “LOB_Compaction = ON” has no effect.
I am pretty sure this is the behavior I observe but BLOB storage, etc. are still somewhat of a mystery to me.
Also, a little earlier in the article it states “If ALL is specified and the underlying table is a heap, then the rebuild operation has on effect on the table. Any nonclustered indexes associated with the table are rebuilt”. I clearly see this behavior and that is part of the reason I think the other statement is incorrect.
Does anyone know for sure?
Thanks
I haven't worked with this around heaps, but I think it means that rebuilding heaps, tables without a clustered index, you don't get compression. This makes sense.
"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
March 23, 2009 at 12:59 pm
The way I'm reading the LOB data is that you can alter a non-clustered index on a heap, and if it contains LOB data in the include colums, you'll get compaction in the index, even if you tell it to use OFF for this option. At least, that's what it looks like to me, when I read what you linked to.
I haven't tried it, and I'm not sure how I would go about testing it, so I could easily be wrong.
- 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
March 24, 2009 at 7:10 am
i run the dmv off hours and always with detailed. on a 300 million row table it usually takes around 30 minutes. in a db with 2 tables that are over a billion rows each takes around 2 hours
it will also depend on the speed of the server and the I/O configuration
March 24, 2009 at 10:15 am
Hi Grant,
The DBCC is returning 3 rows for the table, the heap, the Blobs, and one non-clusterer.
The DM returns the same 2 indexes but it is 4 or 5 rows for the various levels
March 24, 2009 at 10:51 am
Ray Herring (3/24/2009)
Hi Grant,The DBCC is returning 3 rows for the table, the heap, the Blobs, and one non-clusterer.
The DM returns the same 2 indexes but it is 4 or 5 rows for the various levels
How big is the table then? I'm just a little surprised by your times.
"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
March 24, 2009 at 12:56 pm
Currently I have about 6300 pages in teh Clustered Index (I changed it from a HEAP).
The Blobs have 1.1Mpages and the non-clustered index is 2100 pages.
Scan density is 99+% and logical Fragmentation is less than 1%
Extents, Extent Switches, Best Count and Actual Count are all ideal 🙂
March 24, 2009 at 1:27 pm
Well, I finally got around to RTFM... It is noted in BOL that this will run slow on large tables. So, you're just getting hammered. You're already doing the right thing with the LIMITED setting. Other than that, it appears to be a bit of a shortcoming in 2005.
"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
March 24, 2009 at 4:49 pm
Hmm, thanks, I missed that comment in BOL. It seems funny that DBCC Showcontig can complete in so little time (literally 1/100) compared to a select from the dm. I tried limiting the columns in the select but that had no affect at all.
I understood that DBCC approaches were 'deprecated' in preference to the functions and views, but if the difference is that signifcant I think I will have to stay with the old horse 🙂
March 24, 2009 at 6:55 pm
MS supposedly changed the algorithm in the dmv to make it more accurate when it gathers the data
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply