[dba-SQLServer] SSD, Raid 0 and (apparent) bandwidth

jwcolby jwcolby at colbyconsulting.com
Wed Oct 13 21:43:15 CDT 2010


I am pulling data out of HSID (the database from hell) on database _DataHSID and writing it into 
tblHSID in _DataMergePurge.  Basically this is an inner join between a PK table in _DataMergePurge 
and HSID to select the records, then write them into the table in _DataMergePurge.

I have both databases on the SSD, along with their respective log files.  My temp files are on 
another 30g SSD.  The database SSDs are a pair of disks, raid 0.  I don't really have available 
separate SSDs for the log and data files, and I figured (though by no means certain) that the SSD 
would be faster even reading / writing both log and data than having the log on rotating media.

Disk reads / writes are in Meg BYTES / minute (not second).

My read volume is running pretty consistent between 400 and 450 mB/Minute out of _DataHSID and 
around 3 million bytes / minute out of _DataMergePurge.  Response time 1 ms in both cases.  All data 
from Resource Monitor.

CPU is running around 25% average with most of that on the 6 cores dedicated to SQL Server.

Suddenly a burst of writes to the log file at about 1.8 GBytes / min.
1 hard fault / minute every once in awhile, mostly 0.
14.8 Gig memory private to SQL Server's PID
37 minutes in and it hasn't even begun to write to the destination table.
TempDb is up to about 5 gigs.
MergePurge_Log about 1.1G atm.
Another burst write to tempdb at around 1.8G / Min.  Tempdb is a single SSD directly on an SATA port 
on the motherboard.
At about 55 minutes in, data starts to write to the destination table.  Fairly massive writes to 
both the log and data file for mergePurge - anywhere from 500 MBytes / minute up to 1 GByte / minute.

Writing about 1.2 GBytes / minute between the log and the data file.  Interestingly it is not 
*reading* at all (right now), it must be dumping from memory?  About 930 MBytes / Minute to log, 333 
MBytes / min to data.

At 1 hour and 10 minutes it is finished.  4890683 row(s) affected (copied from the database from 
hell into the MP database).

BTW the destination table has an existing clustered index (PK) on the People hash and PKID - two 
fields as the key.

The MP data file is about 4.5 gb with 0 free.
The MP log file is about 6 gigs with 1.6 gigs free.
The tempdb file is about 5 gigs.

That was interesting to watch.  I sure wish I had more memory to see how that affects the system. 
ATM I am running 2 dims (channels) on a CPU socket that can use 4 channels, and "only" 16 gigs total 
available right now.

I am supposed to get 64 gigs total and another CPU with 8 cores which would make it 4 dims of 8 gigs 
each per cpu (all 4 memory channels going).  Who knows what that would do.

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list