Hi There I created the test below as a quick check of the servers committed write capability. This scripts runs acceptably fast on my laptop, (which is about 3 year old 7200rpm hard disk, 2.something GHZ pentium processor. 2gb memory. Windows XP 32 bit.) By comparision we have a 64 bit server that is Windows Server 2003 Enterprise, 8gb of memory, 2 quadcore processors and Raid 1+0 146GB SCSI harddrives.(not ideal config for SQL Server, but should be OK for our purposes). Its running SQL 2005 Standard. However problem is the test query below runs about 70 times faster on my old laptop. Whats going on?? SHouldn't the smoking server be much faster??! I eliminated a problem with McAfee already. I created a simple .net app that just writes strings to a text file. We got about a 25 increase in performance by preventing "access scans" which is read and write scans for the 32 bit McAfee virus scanner. However SQL Server has not seen any change in performance, as a result of the 32 bit McAfee being disabled. Orinally when the Text files were able to be written much faster by disabling McAfee, we thought this would solve the problem for SQL Server, but the SQL Query below still runs about 1-2 orders of magnitude slower than on much more modest machines that are in use. This new server is still getting commissioned but its SQL Server is acting like a dog. Any ideas?? WHen I look at the disk queue length in PerfMon it is sitting about 100%. I am using remote desktop to view the server, but even when turning off remote desktop, the query doesn't speed up. The server isn't doing anything else and perfmon shows this. There is only 1 logical disk (as per the other machines that i have tested this on, with a fraction of the hardware). I checked the IsStall (as per http://www.sql-server-performance.com/tips/gen_tips_p3.aspx) and its only 8ms. Here is the query below that is of concern to me. Its just a dummy query but highlights a significant performance problem on the "to-be" production server. Help! What am I missing here? I'm satisfied with the pure writing to a text file from a .net console app but something is severely wrong with SQL Server committed writes indicating that there is some bad configuration issue at stake within SQL Server. Any ideas? USE [master] GO DBCC DROPCLEANBUFFERS GO USE [master] GO /****** Object: Database [test] Script Date: 10/14/2008 15:33:58 ******/ IF EXISTS (SELECT name FROM sys.databases WHERE name = N'test') DROP DATABASE [test] go create database test go USE TEST GO CREATE TABLE [dbo].[sqlcounter]( [id] [int] primary key NOT NULL ) GO SET NOCOUNT ON declare @Counter int set @counter = 0 --BEGIN TRAN while (@counter

Could be parallalism issue.

Your laptop has single processor where as server has more than one...

Try using MAXDOP = 1  option in your query to use single processor and see if it improves...

Thanks yeah, I tried that already. We rebuilt the server as a 32 bit machine becuase we were having trouble with the 64 bit oracle providers with SSIS. We still have this problem that reads and rights are about 1/100th of the speed of our test environment which has less hardware. Something must be configured incorrectly somewhere. We don't have any control over the windows image. The Profiler is showing things are quiet. Disk reads and writes are comparable to other machines, but committed SQL writes and reads are 1/100th of the expected or even slower if you consider all the extra hardware this machine has (extra CPUs, memory, disk). Rob

You are using Standard edition of SQL Server 2005 (64-bit) where lock pages in memory cannot be set for sql server. My suggestion is to look at Page life expectancy, Free pages, Page faults etc. to find out any kind of paging on that system. What Disk Read/sec and Disk Write/sec shows? You mentioned 100% Average disk queue length whereas this counter shows actual number not the % value. Its Disk Time which shows %. How pagefile.sys looks like and where it is located.

I found the problem over the weekend. Finally. Disk Caching is not enabled in the OS, nor support by the disk driver. The test I ran is pretty much the same as the test described in this link (http://www.bobsgear.com/display/ts/The+effect+of+buffers%2C+flushes%2C+and+transactions%2C+on+insert+operations+with+small+data+inserts+to+SQL+Server+2005) My statement above about read speed was incorrect. I did get some slow read speeds, but the problem was with the writes not the reads. We're not actually doing transactions like this test in the above link, but running a loop test like this that inserts small records obviously has a performance hit. The question I posed is more of an academic question that a real observable production problem. A lot of my SSIS packages import small amounts of data however, but I'm unsure of whether the data volumes per insert are similar enough to the above test. The windows 2003 machine (test server)that has acceptable performance and about half the iron of the production server has disk caching enabled (disk properties). Anyway I've left it with the server host to figure out this hardware stuff and put on a driver that supports disk caching. The very small chance of data loss is acceptable to us, to get much improved write speed. The disk itself may support battery backed up caching, however its thousands of miles away in houston, so it could be some time before I hear back. Anyway, I was very interested in this and hadn't come across it before. By the way we had other problems with the 64 bit Oracle Providers which we were using for an SSIS package so we converted the machine back to 32 bit and it ran. Not fast, but it ran.

Also I suggest http://sqlserver-qa.net/blogs/tools/archive/2008/04/28/sql-server-2005-and-disk-drive-allocation-unit-size-to-64k-any-benefit-or-performance.aspx on the drive allocation, as it helps the performanc.