Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
Upvote on the idea that another server on the SAN could be affecting your server. Nobody believes me when I say this is possible to the almighty SAN (even thought I proved it with a real test). –  James Risto Oct 29 '09 at 0:02

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.

share|improve this answer
    
These tests are conducted out of hours –  user16176 Aug 10 '09 at 8:46
    
Just because the tests are performed out of ours doesn't mean that things aren't happening. There could be batch processing happening, or database maintenance could be being performed. –  mrdenny Aug 10 '09 at 8:50
    
Nothing else is happening on the server, nothing is showing up in 'Current Activity' at the time. –  user16176 Aug 10 '09 at 8:54
    
I assume that you don't have sysadmin access to the server? By default a non-sysadmin won't be able to see what other processes are running on the system. If nothing is showing up besides your process you don't have the ability to see the other things which are running. –  mrdenny Sep 22 '09 at 1:09

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

share|improve this answer
    
-1. "if it is 'simple' nothing is written to the transactionlog" is factually wrong. Transactions are written to the log still (it is needed for SQL Server to work), just the logspace truncated upon commit, so reused. Tx log writes are inherent part of never having inconsistent db states and simple backup mode does not invalidate db integrity guarantees (due to delayed writes to the database file). –  TomTom Oct 25 '10 at 7:03
    
In Fact, thats true - i oversimplified the mecano... –  Ice Nov 6 '10 at 11:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.