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