November 16, 2015 at 3:15 am
Hi,
I have a largish table that is 330GB, and I wanted to check the index fragmentation. It seems very slow to do so though, and when I check the IO going on in resource monitor, it is moving along at about 1MB/sec, which is far below the maximum throughput that my system can achieve, it is capable of over 150MB/sec. When I check the wait times, it is listing PAGEIOLATCH_SH as being the wait type.
Is there any way I can speed up this process? I am already querying the data in limited mode with the following query:
select *
from sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID('Server.dbo.FactTable'),NULL,NULL,'Limited') as IPS
I'm working towards creating an index maintenance scheme where I only rebuild/reorganize on certain thresholds, but given this test, it looks like it is going to take a very long time to get a list of those thresholds for my database, and I'm not sure why it is achieving such a low I/O throughput, in comparison to other read/write tasks I assign to the SQL server... SQL check shows there being around 20 R/W's per second.
November 16, 2015 at 3:40 am
PageIOLatch is an IO subsystem wait. Maybe chat with your storage admin as to why you're seeing IO-related waits?
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
November 16, 2015 at 3:55 am
Well this is a newly created SQL Azure VM based on the DS13 model (8 cores, 25600 IOPS, 256MB/sec network bandwidth), and I currently have a storage pool of 6 disks striped for the transaction log, and another 6 disks striped for the database files. I also am using Buffer Pool Extensions to create an 80GB SSD extension area. Allocation unit size is set to 64KB.
The server is not being used for anything other than my testing right now, and I have been able to get good throughput when I do read/write tasks, other than this one, which is horrible. I'm looking at the activity monitor, SQL check and resource monitor, and nothing is blocking it. I'm not running anything else, other than the monitoring tools! I'd have to make some kind of azure ticket to get it looked at at the storage level, I guess, but because of the fact that other queries are easily surpassing the I/O of this one, I did think this was likely to do with the slowness of the DMV. Are other people getting acceptable throughput (i.e normal query speed throughput) when they query the view?
November 16, 2015 at 4:54 am
I've been experimenting a bit with this, and there might be a diminishing returns effect on large tables, or maybe tables of a certain structure.
I tried to run the query over a table that is 20GB in size, and I can achieve 100mb/sec DB read speeds when I try to get the index fragmentation on this DMV in "detailed" mode. This slows down to around 2MB/sec when I switch to "limited" mode for this table.
When I do the same to my 330GB table, I am restricted to the very slow disk read speeds, regardless of the mode I use.
I'll see if I can test loading a subset of the data from my 330GB to another table, and see if a reduced subset subsequently increases the I/O speed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply