Performance Developer vs. Std

We have a series of SPs, poorly written, but that's not the issue here.
Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
(Dev), dual P3, 2gig ram. Pretty much just DB servers.
But we've even tried the local Dasd on the Prod (Std) box.
The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
even everything on a single RAID drive. Prod at least has db and log
separate, more at times.
We've checked everything, and can't find any differences. Running on Prod
Off hours when it's not even breaking a sweat.
The proc does nothing special, except a cursor for Update (Ooouugghh).
Any ideas what to look at ' We compared everything we can think of, no
differences SQL, Win2000... No errors in the event log ..., Query plans
match even cost estimates.
Could Developer (which is Enterprise, right) provide that much boost vs.
Std, even with nothing, that I know of that would be using EE features.
Note to date everything I've wrritten, even some larger procs (10 to 20
million row processes) seem to run as expected. Slightly faster on Prod.
--
KlK, MCSEHi
As a good benchmark, run the SQL IO stress tools on your Dev and Production
server. It will indicate if your SAN is considerably slower than the local
drives.
Look at the links off http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx
EE does have some performance enhancements over standard (pre-fetch) but the
Disk might be your issue.
Regards
Mike
"KevinK" wrote:
> We have a series of SPs, poorly written, but that's not the issue here.
> Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> But we've even tried the local Dasd on the Prod (Std) box.
> The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> even everything on a single RAID drive. Prod at least has db and log
> separate, more at times.
> We've checked everything, and can't find any differences. Running on Prod
> Off hours when it's not even breaking a sweat.
> The proc does nothing special, except a cursor for Update (Ooouugghh).
> Any ideas what to look at ' We compared everything we can think of, no
> differences SQL, Win2000... No errors in the event log ..., Query plans
> match even cost estimates.
> Could Developer (which is Enterprise, right) provide that much boost vs.
> Std, even with nothing, that I know of that would be using EE features.
> Note to date everything I've wrritten, even some larger procs (10 to 20
> million row processes) seem to run as expected. Slightly faster on Prod.
>
> --
> KlK, MCSE|||Thanks Mike, unfortunately we have tested it using only the local drive on
Prod.
We have a 70gig local raid drive that is normally used for Backups etc.
It didn't help.
We do know the SAN drives are slower than locals, but it isn't that
significant, maybe 20% (I think 12 to 15 was the official number.
But when we run it on the SAN we are using multiple drives, log and data.
I am trying to get them to do a DBHammer
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> As a good benchmark, run the SQL IO stress tools on your Dev and Production
> server. It will indicate if your SAN is considerably slower than the local
> drives.
> Look at the links off http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx
> EE does have some performance enhancements over standard (pre-fetch) but the
> Disk might be your issue.
> Regards
> Mike
> "KevinK" wrote:
> > We have a series of SPs, poorly written, but that's not the issue here.
> >
> > Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> > (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> >
> > But we've even tried the local Dasd on the Prod (Std) box.
> >
> > The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> > even everything on a single RAID drive. Prod at least has db and log
> > separate, more at times.
> >
> > We've checked everything, and can't find any differences. Running on Prod
> > Off hours when it's not even breaking a sweat.
> >
> > The proc does nothing special, except a cursor for Update (Ooouugghh).
> >
> > Any ideas what to look at ' We compared everything we can think of, no
> > differences SQL, Win2000... No errors in the event log ..., Query plans
> > match even cost estimates.
> >
> > Could Developer (which is Enterprise, right) provide that much boost vs.
> > Std, even with nothing, that I know of that would be using EE features.
> >
> > Note to date everything I've wrritten, even some larger procs (10 to 20
> > million row processes) seem to run as expected. Slightly faster on Prod.
> >
> >
> >
> > --
> > KlK, MCSEsql