Hi all,
My setup is : SQL Server 2000 SP3 on Windows 2000.
I used the profiler to run an sql script with 33,000 commands (mostly
inserts). I ran it on 4 setups and the results were strange. Lower-end
machines ran twice as fast as higher-end machines. I also ran out system
and not the script on all machines and got the same results.
Lower-end ? Motorola Pentium III 500. with 256 MB memory, one slow disk and
.
High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
Any help is greatly appreciated.
AviAssuming each insert is in a separate transaction, a likely cause of the
performance difference is that write caching is enabled on the older
machines but not on the high-end boxes. A synchronous log write is
required for each transaction so you'll need to wait for the i/o to
physically complete.
Write caching improve performance but the controller must guarantee that
data will eventually get written to disk with non-volatile (e.g. battery
backup) memory. Loss of log data may result in a corrupt database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> Hi all,
>
> My setup is : SQL Server 2000 SP3 on Windows 2000.
>
> I used the profiler to run an sql script with 33,000 commands (mostly
> inserts). I ran it on 4 setups and the results were strange.
Lower-end
> machines ran twice as fast as higher-end machines. I also ran out
system
> and not the script on all machines and got the same results.
>
> Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
disk and
> .
> High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
>
> Any help is greatly appreciated.
> Avi
>|||Hi,
Thanks for the reply. It is enabled on both machines..
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> Assuming each insert is in a separate transaction, a likely cause of the
> performance difference is that write caching is enabled on the older
> machines but not on the high-end boxes. A synchronous log write is
> required for each transaction so you'll need to wait for the i/o to
> physically complete.
> Write caching improve performance but the controller must guarantee that
> data will eventually get written to disk with non-volatile (e.g. battery
> backup) memory. Loss of log data may result in a corrupt database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Avi" <rememberoti@.yahoo.com> wrote in message
> news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> >
> >
> > My setup is : SQL Server 2000 SP3 on Windows 2000.
> >
> >
> >
> > I used the profiler to run an sql script with 33,000 commands (mostly
> > inserts). I ran it on 4 setups and the results were strange.
> Lower-end
> > machines ran twice as fast as higher-end machines. I also ran out
> system
> > and not the script on all machines and got the same results.
> >
> >
> >
> > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> disk and
> > .
> >
> > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> >
> >
> >
> > Any help is greatly appreciated.
> >
> > Avi
> >
> >
>|||Different RAID levels?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Avi" <rememberoti@.yahoo.com> wrote in message news:uh6ZtM5rDHA.2400@.tk2msftngp13.phx.gbl...
> Hi,
> Thanks for the reply. It is enabled on both machines..
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> > Assuming each insert is in a separate transaction, a likely cause of the
> > performance difference is that write caching is enabled on the older
> > machines but not on the high-end boxes. A synchronous log write is
> > required for each transaction so you'll need to wait for the i/o to
> > physically complete.
> >
> > Write caching improve performance but the controller must guarantee that
> > data will eventually get written to disk with non-volatile (e.g. battery
> > backup) memory. Loss of log data may result in a corrupt database.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Avi" <rememberoti@.yahoo.com> wrote in message
> > news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > > Hi all,
> > >
> > >
> > >
> > > My setup is : SQL Server 2000 SP3 on Windows 2000.
> > >
> > >
> > >
> > > I used the profiler to run an sql script with 33,000 commands (mostly
> > > inserts). I ran it on 4 setups and the results were strange.
> > Lower-end
> > > machines ran twice as fast as higher-end machines. I also ran out
> > system
> > > and not the script on all machines and got the same results.
> > >
> > >
> > >
> > > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> > disk and
> > > .
> > >
> > > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> > >
> > >
> > >
> > > Any help is greatly appreciated.
> > >
> > > Avi
> > >
> > >
> >
> >
>|||Thanks for the reply!
On both machines the Write Caching is enabled.
Any other ideas?
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> Assuming each insert is in a separate transaction, a likely cause of the
> performance difference is that write caching is enabled on the older
> machines but not on the high-end boxes. A synchronous log write is
> required for each transaction so you'll need to wait for the i/o to
> physically complete.
> Write caching improve performance but the controller must guarantee that
> data will eventually get written to disk with non-volatile (e.g. battery
> backup) memory. Loss of log data may result in a corrupt database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Avi" <rememberoti@.yahoo.com> wrote in message
> news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> >
> >
> > My setup is : SQL Server 2000 SP3 on Windows 2000.
> >
> >
> >
> > I used the profiler to run an sql script with 33,000 commands (mostly
> > inserts). I ran it on 4 setups and the results were strange.
> Lower-end
> > machines ran twice as fast as higher-end machines. I also ran out
> system
> > and not the script on all machines and got the same results.
> >
> >
> >
> > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> disk and
> > .
> >
> > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> >
> >
> >
> > Any help is greatly appreciated.
> >
> > Avi
> >
> >
>|||Thanks for the reply,
The disks are not RAID configured. I even disabled two of them just to make
sure of that. I still get bad response.
Any other ideas are welocme!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u0G$Bo6rDHA.3436@.tk2msftngp13.phx.gbl...
> Different RAID levels?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Avi" <rememberoti@.yahoo.com> wrote in message
news:uh6ZtM5rDHA.2400@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Thanks for the reply. It is enabled on both machines..
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:eMdns82rDHA.2260@.TK2MSFTNGP09.phx.gbl...
> > > Assuming each insert is in a separate transaction, a likely cause of
the
> > > performance difference is that write caching is enabled on the older
> > > machines but not on the high-end boxes. A synchronous log write is
> > > required for each transaction so you'll need to wait for the i/o to
> > > physically complete.
> > >
> > > Write caching improve performance but the controller must guarantee
that
> > > data will eventually get written to disk with non-volatile (e.g.
battery
> > > backup) memory. Loss of log data may result in a corrupt database.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "Avi" <rememberoti@.yahoo.com> wrote in message
> > > news:uAoQT02rDHA.4092@.tk2msftngp13.phx.gbl...
> > > > Hi all,
> > > >
> > > >
> > > >
> > > > My setup is : SQL Server 2000 SP3 on Windows 2000.
> > > >
> > > >
> > > >
> > > > I used the profiler to run an sql script with 33,000 commands
(mostly
> > > > inserts). I ran it on 4 setups and the results were strange.
> > > Lower-end
> > > > machines ran twice as fast as higher-end machines. I also ran out
> > > system
> > > > and not the script on all machines and got the same results.
> > > >
> > > >
> > > >
> > > > Lower-end - Motorola Pentium III 500. with 256 MB memory, one slow
> > > disk and
> > > > .
> > > >
> > > > High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> > > >
> > > >
> > > >
> > > > Any help is greatly appreciated.
> > > >
> > > > Avi
> > > >
> > > >
> > >
> > >
> >
> >
>|||Avi wrote:
> Hi all,
> My setup is : SQL Server 2000 SP3 on Windows 2000.
> I used the profiler to run an sql script with 33,000 commands (mostly
> inserts). I ran it on 4 setups and the results were strange. Lower-end
> machines ran twice as fast as higher-end machines. I also ran out system
> and not the script on all machines and got the same results.
> Lower-end ? Motorola Pentium III 500. with 256 MB memory, one slow disk and
> High-end machines had 1 Xeon processor, 1GB RAM, 3 fast disks.
> Any help is greatly appreciated.
> Avi
Remember that Google is your friend. You are not alone in having this
kind of problem:
http://www.google.com/groups?as_q=new+machine+slower&num=10&as_scoring=r&hl=en&ie=UTF-8&oe=UTF-8&btnG=Google+Search&as_epq=&as_oq=&as_eq=&as_ugroup=microsoft.public.sqlserver.*&as_usubject=&as_uauthors=&as_umsgid=&lr=&as_drrb=q&as_qdr=&as_mind=12&as_minm=5&as_miny=1981&as_maxd=23&as_maxm=11&as_maxy=2003&safe=images
HARDWARE:
Ensure that there are no undetected hardware differences affecting your
benchmarks, e.g., that all memory is available to the processors, that
the hardware and network component interfaces to the systems are
equivalent, that you have exclusive use of the network while testing
(don't let Joe the Programmer download MP3s from your server during
tests). Once you have eliminated possible hardware variations then
reconsider the software element.
FILE I/O:
Test the machines on non-SQL I/O processing, i.e., file I/O. Perhaps the
"fast disks" aren't as fast as claimed. There are some posts here that
might be relevant:
http://www.google.com/groups?as_q=disk+formatting&num=10&as_scoring=r&hl=en&ie=UTF-8&oe=UTF-8&btnG=Google+Search&as_epq=&as_oq=&as_eq=&as_ugroup=microsoft.public.sqlserver.*&as_usubject=&as_uauthors=&as_umsgid=&lr=&as_drrb=q&as_qdr=&as_mind=12&as_minm=5&as_miny=1981&as_maxd=23&as_maxm=11&as_maxy=2003&safe=images
Good Luck,
Michael D. Kersey
mruniqueid wrote: