SQL Server Performance Forum – Threads Archive

Hard disk issue

We run an internet application that is constantly updated by users who send data files to the application, which are received by the web server. The records in these files (between 100 and 50.000) are then one by one (as quite a few validity checks have to take place and user feedback is required) inserted into an auxiliary table of the SQL-server. When all records have been processed the main database is updated by first deleting the data for the respective user and then replace it by the data in the auxiliary table. We recently migrated the SQL-server from a machine with IDE-disks to a machine with RAID 1 SCSI-disks, with twice the RAM and a much facter processor. To my disappointment the performance of the first part of the update procedure has severely deteriorated. This is only partially compensated by increased performance on the second part (deleting records from the main database and copying the data from the auxiliary table into it). Tests have clearly demonstrated that it is the disk performance that is the bottle neck. I spoke to the manufacturer of the server and they said that a RAID 1 configuration will always be slower on the very tiny writing operations as each entry has to be written twice. They recommended me to upgrade the configuration to RAID 10. My questions are: 1. When I run a trace in SQL-profiler on the process many of the insert statements do not result in any writes, only once every few hundred records I see a write operation. Nevertheless the hard disk shows constant writing activity. I wonder how this can be explained. 2. How can the SQL-server be held up by the hard disk so much? The disk queue is pretty low, so there should be ample of buffer space. 3. I thought the overhead of mirroring would be handled mainly by the RAID controller. Still it apparently slows down the process considerably. How can this be explained? 4. Is there any way we could prevent that the inserts of the auxiliary table create constant writing activity? F.i. do all the inserts in memory and then write the table at once? 5. Would upgrading to RAID 10 be a good idea?
Jaap Kramer
Utrecht, The Netherlands

---

HI ya, 1. I’d suggest that you use ntfilemon from www.sysinternals.com to check that the file activity is actually SQLServer and not something else. This will tell you exactly what files are being written to. 2. based on your findings, it would seem that it is perhaps not SQL that is using the disk/slowing down the box? 3. The one RAID controller should be fine, but make sure that it has a write-cache enabled and that it has battery backup 4. no you can’t, the writes have to be put into the SQL log, no way to get around that 5. RAID10 is not faster that RAID1 for writing…? RAID10 requires at least 4 drives, I’d suspect that you only have 2? Cheers
Twan

---

How often you perform Transaction log backups?
How about the memory settings on SQL? Are there any other queries affected with this low performance, and check the proper indexes usage for the slow running queries. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Perhaps something of interest is recovery interval setup. Any changes there?

---

RAID controllers frequently disable OS disk caching, The OS cache should be faster than the HW RAID controller cache because it closer to the application that needs it. if you are doing single row-by-row inserts into an aux table, considering using an explicit transaction (BEGIN TRAN insert, insert, …, COMMIT TRAN)
this will reduce the number of individual writes to the transaction log

---

5. RAID10 is not faster that RAID1 for writing…? RAID10 requires at least 4 drives, I’d suspect that you only have 2? This isn’t necessarily true. RAID 10 will give you better write performance many times, especially with a good controller. What type of server/controller is this? You also might consider a redesign of your system. The deletes will be hard on any OLTP system during the middle of the day. Have you considered using a view and having a batch process at night that switches the records. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

And in order to reduce the fragmentation after those delete/inserts its better to execute or schedule intermittent UPDATE stats on frequently used tables, that will help to reduce the fragmentation and increase performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Satya, are you sure update statistics reduces fragmentation? Per BOL:
IMO Update statistics doesn’t affect data/index space allocations. You have to rebuild or defragment indexes or to shrink db to reduce fragmentation.

---

Are auxiliary tables in main db? You can put them in separate stage db and use simple recovery model there. That way you would save some log space. If you save input files long enough you don’t have to have full recovery model there.

---

Thanks for all your thoughts and comments. Here are some outcomes of my further research based on individual comments: Twan: 1. Yes it is definitely the SQL-log that is slowing down thing. ntfilemon did confirm my suspicions 2. I tried to figure out whether the write cach is enabled. The only thing I found so far that Windos 2003 indicates that is enabled. I can not turn it on or off though. Any suggestions where I should look? Satya: No other queries show this slow performance. Running a transaction log back up makes no difference at all for the performance. The inserts that are showing the slow perfomance are preceded by a "TRUNCATE TABLE" so bascially they start with an empty table, so I do not think the fragmentation will be of significant influence. Joechang: My fear is that this model might have a negative impact on both memory and connection pooling (the update will require an exclusice connection for the whole process) but we will try it to see if it improves performance. MMarovic: The set-up you describe is exactly what we use. Jaap Kramer

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">We run an internet application that is constantly updated by users who send data files to the application, which are received by the web server. The records in these files (between 100 and 50.000) are then one by one (as quite a few validity checks have to take place and user feedback is required) inserted into an auxiliary table of the SQL-server. When all records have been processed the main database is updated by first deleting the data for the respective user and then replace it by the data in the auxiliary table.<br /><br />We recently migrated the SQL-server from a machine with IDE-disks to a machine with RAID 1 SCSI-disks, with twice the RAM and a much facter processor. To my disappointment the performance of the first part of the update procedure has severely deteriorated. This is only partially compensated by increased performance on the second part (deleting records from the main database and copying the data from the auxiliary table into it). Tests have clearly demonstrated that it is the disk performance that is the bottle neck. <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">I don’t think I unerstood the process. You said you have to insert rows one at time because of user feedback is necessary for each and that you could have files with up to 50.000 entries. Does it mean that you would have to wait for up to 50.000 user feedbacks? Now how can be that insert is slow compared to user feedback? I am sure I missed something but I can’t figure out what. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Sorry if I have not been clear enough: User feed back in this case only involves keeping the user informed about what is going on (number of records processed and information about failed records). So we do not ask the user to actually reply on anything. The main reason for the record by record processing is the validity checks. We process a great number of different record formats and sources, so a thourough cleansing is needed. In the mean time we tried the transaction model. It sure looks very promissing. It improves the processing of the a single data file enormously by cutting out the log entries. We are now going to to do some stress tests to see what the overall effect would be under production circumstances. Thanks for your help and best wishes for the new year everyone Jaap Kramer

---

]]>