May 23, 2012 at 5:03 am
Hi
I'm looking at one of my databases and trying to get fragmentation info
i run the query
select top 10 * from sys.dm_db_index_physical_stats(21 ,0,NULL,0,'LIMITED')
but the query itself just sits there for an age (i stopped it after 12 minutes)
i ran sp_who2 and that particular spid is reporting back DBCC under the command, with a very very slowly incrementing DiskIO column
is sys.dm_db_index_physical_stats just ruinning a DBCC SHOWCONTIG under the bonnet? - all my other databases seem to run this absolutely fine
MVDBA
May 23, 2012 at 5:30 am
Is it a particularly big database?
Far as I know, sys.dm_db_index_physical_stats is a way to produce more consumable results from the 2008 equivalent of DBCC SHOWCONTIG.
May 23, 2012 at 5:40 am
It appears it does just run DBCC SHOWCONTIG - found this, straight from the horses mouth: http://www.sqlskills.com/BLOGS/PAUL/post/Inside-sysdm_db_index_physical_stats.aspx
DBCC SHOWCONTIG was replaced by sys.dm_db_index_physical_stats. Under the covers though, they both use the same code - and the I/O characteristics haven't changed.
That's for 2005, but this hints that it's still the same in 2008 (as you still can't use APPLY with it): http://www.sqlskills.com/BLOGS/PAUL/post/Indexes-From-Every-Angle-Using-CROSS-APPLY-with-sysdm_db_index_physical_stats.aspx
This works for most of the DMVs, but some of them are written to an older internal implementation that doesn't support CROSS APPLY, and sys.dm_db_index_physical_stats is one of them.
May 23, 2012 at 6:40 am
hmm
but the funny thing is that yesterday the query was working fine.... what can have changed to make the showcontig stall ?
MVDBA
May 23, 2012 at 7:58 am
Massive fragmentation overnight?!
Run it table by table, or index by index to narrow down the location of the problem?
May 23, 2012 at 8:11 am
in that same database there was a checkpoint that seemed to have hung - looks like that was the issue - the checkpoint was doing nothing and had been for 24 hours
i can't kill the checkpoint as it's a system process, and i can't issue any other checkpoints in that DB as spid 10 blocks them.
couldn't stop the SQL service either (it just hung at stopping) so i had to kill the actuall sqlserver.exe process itself (i'm now preparing to repair my data!!!!!)
MVDBA
May 23, 2012 at 9:17 am
Nice...
May 23, 2012 at 9:28 am
lucky it's a test box
MVDBA
May 23, 2012 at 10:42 am
Check your IO throughput. Sounds like the drive is not responding as it should.
Index physical stats doesn't run showcontig, more the other way around. Showcontig is deprecated and just runs index physical stats and formats the data as expected. In detailed mode it has to read ever page of the index.
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
May 23, 2012 at 12:51 pm
GilaMonster (5/23/2012)
Check your IO throughput. Sounds like the drive is not responding as it should.
+1
GilaMonster (5/23/2012)
Index physical stats doesn't run showcontig, more the other way around. Showcontig is deprecated and just runs index physical stats and formats the data as expected. In detailed mode it has to read ever page of the index.
according to Paul Randal's post they do both use the same code base but one doesn't actually call the other. Wasn't DBCC SHOWCONTIG written long before DMVs were possibly even a concept at MS?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 23, 2012 at 2:41 pm
Perry Whittle (5/23/2012)
according to Paul Randal's post they do both use the same code base but one doesn't actually call the other.
Ok, I thought I'd read somewhere that the code had been rewritten for index physical stats and showcontig changed to call that code.
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
May 24, 2012 at 2:45 am
GilaMonster (5/23/2012)
Check your IO throughput. Sounds like the drive is not responding as it should.Index physical stats doesn't run showcontig, more the other way around. Showcontig is deprecated and just runs index physical stats and formats the data as expected. In detailed mode it has to read ever page of the index.
can't check io throughput - it's cloud based... i have no idea in which country that hard drive resides (given that it's spread across about 4 billion amazon hard drives)
MVDBA
May 24, 2012 at 2:47 am
michael vessey (5/24/2012)
GilaMonster (5/23/2012)
Check your IO throughput. Sounds like the drive is not responding as it should.Index physical stats doesn't run showcontig, more the other way around. Showcontig is deprecated and just runs index physical stats and formats the data as expected. In detailed mode it has to read ever page of the index.
can't check io throughput - it's cloud based...
Sure you can, cloud doesn't mean magic IO and it doesn't mean that you can't check performance stats.
It's a virtual machine? Or a hosted SQL instance? If the former, perfmon. If the latter, virtual file stats, IO waiting tasks and a mail chat with the admins.
If it's just a hosted database on a shared instance, then you need to contact the support people and ask them to run some checks.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply