SQL Server Performance Forum – Threads Archive
Slow Disk Performance
Hi, We recently purchased a new server to be our dedicated SQL server, however we seem to be bumping into a few problems with it. Insert Operations run very slowly on this server. Select queries run very very fast. But our desktop machines with 512Mb RAM, 2.2GHz processors and 60Gb IDE Hard disks run the Insert Operations faster than the server (anything up to twice as fast). We currently have: 4 72Gb 15k SCSI Drives as RAID 5 – Data
1 72Gb 15k SCSI Drive as RAID 0 – Logs
– All attached to a Compaq 642 RAID Controller (no write cache)
2 3.2GHz Xeon Processors
6Gb RAM Windows 2003 Server Enterprise Edition
SQL Server 2000 Developer Edition (for testing until the server goes live with Enterprise Edition). We have one more option to add with regard to the memory, up to now SQL Server has been using the default 3Gb, but this will be fixed soon. The reason this is still being posted is that I’m not convinced that memory is responsible for the problems we’re having. So a few questions, if anyone knows or can help I would be very grateful. Will adding a Write Cache Enabler to the RAID Controller have any effect at all on the performance of the SQL Server? Any ideas why SQL Server runs so much slower than on desktop machines on Insert Operations? Or any other things to try? Many thanks
One idea is, if it possible, put data on RAID 0.
This would increase performance, specially in write.
About desktop, I’m sure you are talking with single user, not with all users on line.
Before to try with RAID 0, you should test insert query looking execution plan to find if there is any optimization.
i would check the status of the OS disk cache
go to disk manager, properties, policies etc
Thanks for the responses guys. This problem, at least by the descriptions given, does seemed to have popped up on this forum a couple of times, unfortunately no successful solution posted. The server, as it stands, isn’t performing any other tasks, sitting on a desk in the office next to this with only us running speed tests on it, no other users connected. The Desktops are generally more heavily loaded (all of which run AntiVirus scanners) and are performing insert operations in anything up to 7 times the speed of the new server (which is only running SQL Server 2000 SP3a). So far we’ve tried: Data & Logs on RAID 5
Data & Logs on RAID 1+0
Data on RAID 5, Logs on RAID 1+0
Data & Logs on RAID 0
Data & Logs on two seperate RAID 1 sets
Disabled Hyperthreading on the XEON processors While the settings we’ve required have had an impact in the general file system performance SQL Server itself seems to be stuck and refuses to alter its operation speed regardless of the disk set-up. Write Cache can’t be enabled on our RAID Controller as it stands, requires a extra component. We tried enabling Write Cache on the Hard disks themselves, but this had no effect on the performance. Still not sure if enabling write cache on the controller will have any effect on the speed.
When you say its faster on your desktop, do you mean you have a local sql server on your desktop, which is running the EXACT same database? (including indices, etc etc). Maybe the database is heavily fragmented which is causing slow inserts. I dont know if a backup and restore does anything to defragment the pages, but id certainly run DBCC INDEXDEFRAG on your database. Also check your file system fragmentation, although this is probably not the cause since you say its a new server. Keep looking! Id be reluctant to shell out for more hardware on the assumption itll magically fix it.
Each of the desktop machines we’ve been testing it on has been running SQL Server Developer Edition, the machines themselves ranging from 600MHz P3 with 512Mb RAM to P4 2.6 with 2Gb RAM. Every one of them runs through these operations faster than our new server. In each case this is 100000 Inserts run through Query Analyzer, all machines have exactly the same database set-up.
Ok, still worth checking fragmentation of server database though. Perhaps the act of backing up and restoring does something to help the fragmentation, and so your desktop database is not organised exactly the same way on disk as server. An sql profile of the server (examine IO & showplan all), may shed some more light on the problem too. Are all inserts uniformly slow for example, or does it get exponentially slower during those 100000
The Inserts seem to be uniformly slow. Running Performance Monitor shows everything within the normal expected values. The sole exception to this is the SQL Page Writes / Sec. On the Desktop machines (despite them having much greater load on disks) there are roughly 5 – 10 times more writes per second than on the new SQL Server. Really confused about why it’s going so much slower at the moment.
Look into your RAID controller.
Wouldn’t disk performance in general be slow if there was a problem with the RAID Controller? This is certainly not the situation we’re encountering, it’s purely related to SQLs write speed.
<br />Just to let everyone know…<br /><br />We seem to have a solution to this now, each of our Inserts was a new transaction requiring a buffer flush. Using Explicit Transactions seems to have solved the problem.<br /><br />Thanks for any and all answers <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />
"We seem to have a solution to this now, each of our Inserts was a new transaction requiring a buffer flush. Using Explicit Transactions seems to have solved the problem." It’s great that you were able to code around it. Many times you can’t. The key here is that you’ll never write any faster than the transaction logs can handle. If you can’t change your code, then you need to look at ways to improve log write performance. Cache, Diskalignment, Aloocation unit size, etc. come to mind. John
Interesting. We have a simillar problem ourselves http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3313) but can definately say that it only happens when the transaction log is on a standalone (basci) SCSI drive. Putting the transaction log n an IDE drive resolves the issue.