SQL Server Disk Write Performance

Question

This question is from a developer, so it might be a simpleminded.

We have a dev and prod sqlservers (2000) and I suspect the disk write times on the Prod server are quite slow. I've done the following t-sql tests

Test 1 : 10k inserts, takes 2 secs on Dev, 63 secs on Prod

if object_id('testD') is not null drop table testD
create table testD  (i integer)           

declare @i int
set @i=1

while @i <= 10000
begin
   insert into testD (i) values (@i)
   set @i = @i+1
end

Test 2 : 5k deletes , no time on either server

delete from testD where i >= 5000

Test 3 : Nested Loop, 2 secs on Dev, 5 seecs on Prod

select count(A.i + B.i)
from testD A
,    testD B

These tests are conducted out of hours and, I always get the same results. I'm a developer so my only access to the servers is through SQLServer. What conclusions, if any, can be drawn from these tests, and what sort of questions shuold I be asking the tech support section?

Answer 1

What sort of disk subsystems do each of the servers have? Without knowing much more about the servers and what's happening on that at the time that you ran the test there's really now way for sure to know where the problem is.

At first glace it does sound like there's a problem with the disk subsystem, but that can be misleading.

The CPUs could be busy running a compressed backup. If the disk is on a SAN and using shared storage with a file server, the file server could be going through a nightly defrag operation. The SQL Server could be rebuilding all the indexes in this database, or another database on the server and those operations are taking all the disk resources.

Without more access to the SQL Server you won't be able to figure out what the problem is. Report the issue to the support team and make sure that they actually follow up on it. If your support team is any good they should already be aware of the issue and working on it.

Answer 2

How much else is going on on the production server?

On your dev box you are likely to be the only active user so get all the resource available for your queries but on the production box there could be a number of active queries going on on your database and/or possible other databases on that server, so there is contention for IO bandwidth.

Answer 3

i mention to have a closer look a the disk or disksubsystem an the fragmentation of the mdf and the ldf files.

On the other side it may be that sql-server index-fragmentation is slowing down the performance or the statistics are totaly different in fact of having only few rows on your dev-db versus much higher row-count on the pred-db. try to transfere the statistics from your prod-server to your dev-server to get the same executionplans.

Last but not least have a look at the (how is it called) backup-level: if it is 'full', eveything goes to the transaction-log (*.ldf), if it is 'simple' nothing is written to the transactionlog. So i bet your dev-system is configured with simple-mode and your prod-system drives the full-mode.

Eventually, if you have a Management Studio (Query-Analyzer) on your local pc accessing the servers over the network. it might be you have a better bandwith e.g. 1 GBit to your dev-server and only 100MBit to your prod-server.

Don't look where the light is, the truth lies in the dark...

Peace

Ice